A high level overview of SQL Joins
A SQL JOIN clause is a way to combine rows from two or more tables based on a common column between them.
Let’s say we have two tables, an artists and songs table and we wanted to query both tables to return information about both the artists and songs. This is where JOIN statements come in.
INNER JOIN: The INNER JOIN keyword selects all rows from both tables and acts as a natural filter. Going back to our artists and songs table example, let’s say that we want to get a list of all artists with “Rock” as a genre of their song. The INNER Join will select all the rows from both tables as long as there is a match between the specified columns of each table.
Using a Venn diagram as an example, the inner join will return all of the overlapping data between Table A and Table B.
LEFT JOIN: LEFT JOIN returns all rows from the left, or first table regardless of whether or not they met the join condition. Additionally, this query will also return the matched data from the right or second table.
Now imagine another scenario where you bought concert tickets for all your friends and wanted to keep track of who has paid you back and which of your friends still owe you. In the Venn diagram below, we can think about “Friends” as Table A and “Payments” as Table B. The LEFT JOIN will return the overlap of who has paid as well as the rest of Table B.
If the data from the Table A doesn’t meet our JOIN condition, the resulting output will be NULL or empty.
RIGHT JOIN: The RIGHT JOIN query is the reverse of LEFT JOIN. The result will return all data from the right, or second table and the matched data from the left or first table.
OUTER JOIN: The OUTER JOIN query will combine all the results of both a LEFT and RIGHT JOIN. This JOIN returns all of the data from all the tables including the overlapping data.
Reviewing SQL Joins has been a fun exercise for this blog post. In my next post, I hope to go over some quick exercises around syntax and query calls that would solidify my understanding.
