So there are lots of explanations and diagrams (mostly using venn diagrams) to explain SQL joins, but ‘hey, here is another one’. I’ve chosen a slightly different way of visualising the output of the join. The reason for this is ‘keep it simple’, if you know the data items you start with and the results, then you can easily see how the join works – well thats the idea anyway…
I’ve tried to show the differing terminology, which is used by different versions of SQL.
The JOIN keyword is used in an SQL statement to combine data from two or more sets of data (i.e. two or more tables), based on a relationship between certain columns in these tables; these columns DO NOT need to be table keys, but there needs to be an identifiable relation between the different sets of data.
Different SQL JOINs
The below shows the types of JOIN you can use, and the differences between them.
- JOIN: Return rows based on the type of join
- LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table (The result is NULL from the right side, if there is no match.)
- RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table (The result is NULL from the left side, if there is no match.)
- FULL JOIN: Return rows when there is a match in one of the tables, so combines the results of both left and right outer joins and fill in NULLs for missing matches on either side.
- CROSS JOIN: Returns a combination (or pairing) of each row of the first table with each row of the second table. This type of join is also known as CARTESIAN JOIN
- EXCEPTION JOIN: Returns rows from first table that do not have a match in the second table.
I’d appreciate your feedback, as this is one of first blog posts;
how does it read? is it pitched right? or too technical?
have you tried the code? does it work for you?
* The SQL provided is quite generic, as I tend to use SNOWFLAKE, T-SQL and DB2-SQL but similar types of joins are available in other forms of SQL