Create Simple Join using SQL

To use the WHERE clause to create a simple join in SQL, you essentially combine rows from two or more tables based on a related column that they share. This method, often referred to as an implicit join, involves specifying the join condition in the WHERE clause. Here is a step-by-step guide to achieving this:

1. Identify the Common Column: First, determine the common column that exists in both tables. This column will be used to match rows between the tables. For example, if you have two tables, `Table1` and `Table2`, and both have a common column `common_column`, this will be your key for the join.
2. Select the Columns: Decide on the columns you want to retrieve from each table. For instance, if `Table1` has columns `a` and `b`, and `Table2` has columns `c` and `d`, you need to specify these in your SELECT statement.
3. Construct the Query:
   • Use the `SELECT` statement to specify the columns you want to retrieve.
   • Use the `FROM` clause to specify the tables involved in the join.
   • Use the `WHERE` clause to define the join condition by equating the common column from both tables.

Here is an example SQL query that demonstrates this approach:

SELECT Table1.a, Table1.b, Table2.c, Table2.d
FROM Table1, Table2
WHERE Table1.common_column = Table2.common_column;

In this query:

• `Table1.a`, `Table1.b`, `Table2.c`, and `Table2.d` are the columns selected from the respective tables.
• `Table1` and `Table2` are specified in the `FROM` clause, indicating the tables from which data is retrieved.
• The `WHERE` clause contains the critical join condition: `Table1.common_column = Table2.common_column`. This condition matches rows from both tables where the values in `common_column` are equal.

While this method of joining tables using the WHERE clause is still valid and often used for its simplicity, especially in straightforward queries, it is important to note that explicit JOIN syntax (using `INNER JOIN`, `LEFT JOIN`, etc.) is generally preferred in modern SQL. This is due to its enhanced readability and clarity, particularly in complex queries with multiple join conditions. Nonetheless, in simpler scenarios, using the WHERE clause for joining tables remains an effective and efficient approach.

Here is the SQL statement again, for reference:

SELECT pub_name FROM publishers, titles 
WHERE publishers.pub_id=titles.pub_ID 
AND title_id='BU1032' 

An interesting change comes if you ask to return a column that is found in both tables. In other words, if your SELECT statement calls for the return of the pub_id column, you have to make one change. Because the column is found in both tables, you have to indicate which table you want to retrieve the value from. This may seem a bit confusing, but it will make more sense if you consider the following statement:

SELECT titles.pub_id, pub_name FROM publishers, titles 
WHERE publishers.pub_id=titles.pub_ID 
AND title_id='BU1032' 

By adding the name of the table and a period in front of the column name, you indicate where the information should be obtained. If you forget this, by the way, the SQL engine will likely give you a message that indicates that the column name is ambiguous. Simply add the table name and period and you will be off and running. You will also notice that you can still use a typical WHERE type clause to test for a value. Combining these will, in this case, return a single publisher's name, the one associated with the book with the title ID indicated.

Click on the Exercise link below to make a simple join of your own.
Simple Join - Exercise
In the next lesson, we will begin an examination of INNER and OUTER joins.