Mental Model For SQL Joins

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

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.