This is a simple tutorial aimed towards those who, like me, completely misunderstood how to, and why to, use joins in SQL. It is not meant to be an advanced reference but a tutorial for beginners who know some SQL but can't understand joins.
Huh?
Let's say that you have a database with tables which are related by foreign keys. For example, a Customers table, a Products table and an Orders table. Each row in the Orders table is linked to a row in the Customers table (the customer who made the order) and to a row in the Products table (the product which was ordered).
Now let's say that you want to query the database to view all the rows in the Orders table but with the customer's name and product's name instead of their primary key. If you learned SQL the same way I did, you'd probably do something like this:
SELECT orders.date, customers.name, products.name
FROM orders, customers, products
WHERE orders.customer = customers.id AND orders.product = products.id;
This is called an implicit join. In fact you are doing an unconditioned join between all 3 tables and then just filtering out the joined rows which are not made of related table rows. What does this mean?
If our 3 tables contain the following data:
Orders:
| Id | Date | Customer | Product |
|---|
| 1 | 01/01/01 | 1 | 2 |
| 2 | 2/02/02 | 3 | 2 |
| 3 | 03/03/03 | 3 | 3 |
Customers:
| Id | Name |
|---|
| 1 | John |
| 2 | Michael |
| 3 | Terry |
Products:
| Id | Name |
|---|
| 1 | Cheese |
| 2 | Parrot |
| 3 | Spam |
Then the query will cause this to happen:
| Orders.Date | Customers.Name | Products.Name |
|---|
| 01/01/01 | John | Cheese |
| 01/01/01 | John | Parrot |
| 01/01/01 | John | Spam |
| 01/01/01 | Michael | Cheese |
| 01/01/01 | Michael | Parrot |
| 01/01/01 | Michael | Spam |
| 01/01/01 | Terry | Cheese |
| 01/01/01 | Terry | Parrot |
| 01/01/01 | Terry | Spam |
| 02/02/02 | John | Cheese |
| 02/02/02 | John | Parrot |
| 02/02/02 | John | Spam |
| 02/02/02 | Michael | Cheese |
| 02/02/02 | Michael | Parrot |
| 02/02/02 | Michael | Spam |
| 02/02/02 | Terry | Cheese |
| 02/02/02 | Terry | Parrot |
| 02/02/02 | Terry | Spam |
| 03/03/03 | John | Cheese |
| 03/03/03 | John | Parrot |
| 03/03/03 | John | Spam |
| 03/03/03 | Michael | Cheese |
| 03/03/03 | Michael | Parrot |
| 03/03/03 | Michael | Spam |
| 03/03/03 | Terry | Cheese |
| 03/03/03 | Terry | Parrot |
| 03/03/03 | Terry | Spam |
And after generating all that it will then select the rows which make sense relation wise, according to the WHERE statement.
| Orders.Date | Customers.Name | Products.Name |
|---|
| 01/01/01 | John | Parrot |
| 02/02/02 | Terry | Parrot |
| 03/03/03 | Terry | Spam |
The big table is called a Cartesian product, which means that all possible combinations of rows between the tables are created, yielding a number of rows equal to the multiplication of the number of rows in each table (3 * 3 * 3 = 27 in this case). As you can tell, this will get really huge really quickly as the table get bigger.
Enter explicit joins.
Joins
In SQL, the FROM statement doesn't mean "a list of tables used in the SELECT statement". In the FROM statement you mention just one table. This table could either be one of the tables you created in the database or a "custom" table, such as one which is a joined version of several tables. The JOIN statement in SQL is not a keyword on par with the FROM statement, as I used to think due to the way it is indented in most tutorials I've seen. It is in fact a binary operator between 2 tables, just like "+" and "=" as binary operators.
The JOIN operator takes 2 tables and returns a new table, which is a joined version of the 2 tables. You can then join another table to that new table and keep on adding more tables to the "composite" table. There are several joins available in standard SQL which will be described later, but if we should use the INNER JOIN type in an example, this is how joins are used in general:
SELECT orders.date, customers.name, products.name
FROM (orders INNER JOIN customers ON orders.customer = customers.id) INNER JOIN products ON orders.product = products.id;
As you can see, the join is used between tables and can even be bracketed in order to state which tables should be joined up first. The ON statement is used to immediately join up the rows which are related, avoiding the cartesian product table in the first place. This will therefore be processed much more efficiently as the third table will only be joined up after the first two tables have been joined correctly, rather than joining everything together without a condition.
In the implicit join example, the tables where joined up with an inner join without the ON condition, hence losing all the advantage of joins. Now that you know how joins work, let's see what are the differences between the 4 join types described in w3schools.com.
Join types
The main difference between the join types is how they handle rows which don't match the ON condition.
INNER JOIN:
Strictly follows the ON condition between tables such that any rows in one table which cannot be matched up with another row in the other table will be left out. This is the kind of join we are used to.
SELECT orders.date, customers.name, products.name
FROM (orders INNER JOIN customers ON orders.customer = customers.id) INNER JOIN products ON orders.product = products.id;
results in
| Orders.Date | Customers.Name | Products.Name |
|---|
| 01/01/01 | John | Parrot |
| 02/02/02 | Terry | Parrot |
| 03/03/03 | Terry | Spam |
As you can see, not all customers or all products where mentioned. Only the ones which were mentioned in the ORDERS table and hence were related in some way were returned.
OUTER JOIN:
This is where things get a bit hairy and hence require that you make an effort to understand. The outer join makes sure that all rows in both tables are returned, even if there is no matching row in the other table. What happens to those tables which have no matching rows? They are joined to NULL values. As long as they are returned, it doesn't matter what they're joined to right?
You can also use LEFT JOIN and RIGHT JOIN to say whether you want to return all the rows of the table on the left of the join operator only or all the rows of the table on the right of the join operator only.
Now in order to use these joins to create a complex joined table, you have to be sure that a join will not return columns with NULL values which will be used in an ON condition of another join.
SELECT orders.date, customers.name
FROM orders RIGHT JOIN customers ON orders.customer = customers.id;
results in
| Orders.Date | Customers.Name |
|---|
| 01/01/01 | John |
| NULL | Michael |
| 02/02/02 | Terry |
| 03/03/03 | Terry |
SELECT orders.date, products.name
FROM orders RIGHT JOIN products ON orders.product = products.id;
results in
| Orders.Date | Customers.Name |
|---|
| NULL | cheese |
| 02/02/02 | parrot |
| 01/01/01 | parrot |
| 03/03/03 | spam |
SELECT orders.date, products.name, customers.name
FROM (orders LEFT JOIN customers ON orders.customer = customers.id) RIGHT JOIN products ON orders.product = products.id;
results in
| Orders.Date | Customers.Name | Products.Name |
|---|
| NULL | NULL | cheese |
| 02/02/02 | terry | parrot |
| 01/01/01 | john | parrot |
| 03/03/03 | terry | spam |
As you can see, depending on how the outer joins are used, we can make a particular table return all its rows, even if it isn't related to any other table.
Final note
I'd like to leave a few notes and links before concluding.
First of all, what I said about the implicit join not being efficient next to an explicit one is not necessarily true since the SQL optimizer may fix it before executing it.
Secondly, if you can avoid using brackets in the the joins it would be better as that will allow the SQL optimizer to make adjustments to the query without it having to make sure to preserve the precedence which you unneccessarily imposed.
In a future post, I will describe a trick which I used to efficiently view multiple one-to-many related tables which I also had trouble with in SQL until recently. But for now I will leave you with these links:
http://www.w3schools.com/sql/sql_join.asp
http://onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
