SQL Joins are incredibly useful, and knowing the difference between the five different types makes harnessing their great power a lot easier… obviously!
Joins are used to join tables together in a query, so that you can use data from both tables. You are able to join to a maximum of 256 tables, which frankly, should be more than enough!
The syntax goes like this:
FROM Person AS P INNER JOIN
Orders AS O ON P.PersonId = O.PersonId
The above example uses the most common of all joins, the INNER JOIN. The word “Inner” can actually be omitted from the query, as it isn’t necessary.
The Inner Join will join the two tables together on the linking columns, and only the results where a match is found in both tables will be returned.
Next, we have the OUTER JOIN’s (Left, Right, and Full). These are just as simple to use as the INNER join, but work a little differently. The Synax is the same as the above INNER join example, just replace “INNER JOIN” with “LEFT OUTER JOIN” or Right / Full, depending on what you want to achieve.
Again, with the OUTER Join’s, the word “Outer” can be omitted.
The way the LEFT JOIN works is by selecting all of the data from the table on the left, and returns all of the matchs from the table on the right. If there isn’t a match, then a NULL will be returned by the right table. Right Join is exactly the same, just reversed (returns everything on the right, and all possible matches on the left).
FULL JOIN will return every row from the left and right tables, matching where possible.
And lastly we have CROSS JOIN. This join will match every row from the left table, to every row in the right table. This means that if the left table has 100 rows, and the right table also has 100 rows, our result set would be a grand total of 10,000 rows! Can I think of an example where you would use this? No.
Hopefully the below simple diagram will help re-enforce what I’ve said above.