SYNTAX
SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id;
Just replace JOIN with LEFT JOIN/ FULL OUTER JOIN, etc if using a different join.
Join / Inner Join
- loop through each row in table1
- for each row in table1, loop through table2
- push a new row made up of all the columns in table1 and table2 into the new table if and only if table1.id === table2.id
- return the new table
Left / Right Join (table1 LEFT JOIN table2)
- loop through each row in table1
- for each row in table1, loop through table2
- push a new row made up of all the columns in table1 and table2 into the new table if table1.id === table2.id
- If table2 has been looped through and no row has been added, add a new row with all the columns in table1 and null for all the columns in table2 to the new table.
- return the new table
FULL OUTER JOIN (table1 FULL OUTER JOIN table2)
- do a table1 LEFT JOIN table2
- do a table1 RIGHT JOIN table2
- combine the two tables returned above and remove the duplicate rows.
- return the combined table
CROSS JOIN / CARTESIAN JOIN
- loop through each row in table1
- for each row in table1, loop through table2
- push a new row made up of all the columns in table1 and table2 into the new table
- return the new table
Note that CROSS JOIN is essentially JOIN without a ON clause.
Top comments (3)
In 30 years of writing SQL code I have never had a commercial reason to write a RIGHT JOIN. I have sometimes done it when analysing a data set to avoid rearranging the tables but if I saw one in production it would be a massive red flag that you don't understand your data.
This is a good breakdown of explaining iteratively the set logic that's happening. A great "explain something in a different way" post!
Thank you!