Anyone who is familiar with SQL to at least some extent will know that you can access data from multiple tables by using joins on certain columns. However there are a lot of joins out there – inner, left outer, full outer, cross. And sometimes some of these can get a little confusing – for instance where to use a left outer join vs a full outer or an inner join. And then what’s this left join. Or Natural join. Or Self Join.
For anyone who has ever been in this dilemma I recommend first reading Jeff Atwood’s post which explains the basic joins beautifully with the help of Venn diagrams.
Did you read it? No?! Go back and read it now. And then come back.
If you are done, then you must agree that he does such a wonderful job, that I don’t have to explain once again! However just to reiterate for the sake of repetitive retention,
You use –
- an inner join when you need matching records only from both tables that you are joining
- a left outer join when you need to get matching records from both the tables AND all the other records as well from the left table
- a full outer join when you need to get all the records from both the tables
- Cross join – well thats basically just to get all combinations of all records in both tables, there is no ‘joining condition’ as such. Very rarely used in real-life applications. For two tables with m and n records each, the cross join will return m*n records.
There is also a right outer join – similar to left outer join, but all the records of the table to the right are returned instead of the left one. However this is rarely used, since it can always be replaced by a left outer join by just changing the sequence of tables. Also it is sometimes difficult to evaluate the result of a right outer join (compared to a left outer join) when it is part of a larger multi-join query.
Note: Left outer join and Right outer join are also called Left and Right joins respectively. Outer is just a better way of communicating the intent of the join.
There are other joins like self-joins or natural join, however it is always best to think of these as special cases of inner joins and even write the SQL as such. (Natural join for instance automatically determines which column the join is based on, but it is a better practice to mention it explicitly anyways).