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

loop through each row in table1
for each row i…


This content originally appeared on DEV Community and was authored by Albert Chang

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.


This content originally appeared on DEV Community and was authored by Albert Chang


Print Share Comment Cite Upload Translate Updates
APA

Albert Chang | Sciencx (2022-06-03T18:24:55+00:00) Mental Model For SQL Joins. Retrieved from https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/

MLA
" » Mental Model For SQL Joins." Albert Chang | Sciencx - Friday June 3, 2022, https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/
HARVARD
Albert Chang | Sciencx Friday June 3, 2022 » Mental Model For SQL Joins., viewed ,<https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/>
VANCOUVER
Albert Chang | Sciencx - » Mental Model For SQL Joins. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/
CHICAGO
" » Mental Model For SQL Joins." Albert Chang | Sciencx - Accessed . https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/
IEEE
" » Mental Model For SQL Joins." Albert Chang | Sciencx [Online]. Available: https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/. [Accessed: ]
rf:citation
» Mental Model For SQL Joins | Albert Chang | Sciencx | https://www.scien.cx/2022/06/03/mental-model-for-sql-joins/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts