DEV Community

Albert Chang
Albert Chang

Posted on

Mental Model For SQL Joins

SYNTAX

SELECT *  
FROM table1 
JOIN table2
    ON table1.id = table2.id;
Enter fullscreen mode Exit fullscreen mode

Just replace JOIN with LEFT JOIN/ FULL OUTER JOIN, etc if using a different join.

Join / Inner Join

  1. loop through each row in table1
  2. for each row in table1, loop through table2
  3. 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
  4. return the new table

Left / Right Join (table1 LEFT JOIN table2)

  1. loop through each row in table1
  2. for each row in table1, loop through table2
  3. push a new row made up of all the columns in table1 and table2 into the new table if table1.id === table2.id
  4. 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.
  5. return the new table

FULL OUTER JOIN (table1 FULL OUTER JOIN table2)

  1. do a table1 LEFT JOIN table2
  2. do a table1 RIGHT JOIN table2
  3. combine the two tables returned above and remove the duplicate rows.
  4. return the combined table

CROSS JOIN / CARTESIAN JOIN

  1. loop through each row in table1
  2. for each row in table1, loop through table2
  3. push a new row made up of all the columns in table1 and table2 into the new table
  4. return the new table

Note that CROSS JOIN is essentially JOIN without a ON clause.

Top comments (3)

Collapse
 
aarone4 profile image
Aaron Reese

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.

Collapse
 
a89529294 profile image
Albert Chang

Thank you!