Skip to content
Search

SQL:Join

lastmod 2021-12-16created 2021-04-287 edits

A join takes only the information the user needs from information scattered across several tables and shows the result as if it were a virtual table; it combines two tables and represents them as a single column. (JOIN attaches horizontally, UNION attaches vertically)

Summary

It can be summarized as follows.

  • (INNER) JOIN: returns only rows with equal values in Table1 and Table2
  • LEFT (OUTER) JOIN: fetches all results of Table1 on the left of the JOIN, then matches them with Table2; inserts NULL when there is no matching data
  • RIGHT (OUTER) JOIN: fetches all results of Table2 on the right of the JOIN, then matches them with Table1; inserts NULL when there is no matching data
  • FULL (OUTER) JOIN: matches Table1 and Table2, and uses NULL where there is no data
  • CROSS JOIN: JOINs every row of Table1 and Table2. The result equals the product of the row counts of the two tables
  • NATURAL JOIN: outputs the result set of an INNER join between columns with the same name in the two tables.

Sql_-_list_of_join.png

INNER JOIN

INNER JOIN compares the column values of tables that have key values and fetches the values that meet the condition. Simply put, it is a join method that retrieves only mutually related content.

CROSS JOIN

Also called the Cartesian Product; it returns the Cartesian product of the two tables being joined.

OUTER JOIN

An Outer Join outputs all the contents of the table that has data when, among the tables being joined, one side has data and the other does not. In other words, it can be used when you want to output a row even if it does not satisfy the join condition.

  • LEFT OUTER JOIN - fetches all results of the table on the left of the join statement, then matches the data of the right table, and shows NULL when there is no matching data.
  • RIGHT OUTER JOIN - fetches all results of the table on the right of the join statement, then matches the data of the left table, and shows NULL when there is no matching data.
  • FULL OUTER JOIN - the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN. It combines and outputs everything, including rows that do not match the condition on either side.

Example

Left join example: 

SELECT
    u.uid AS uid,
    pm.project_uid AS project_uid,
    gm.group_uid AS group_uid,
    CASE
        WHEN pm.permission_uid IS NOT NULL THEN pm.permission_uid
        ELSE gm.permission_uid
    END AS permission_uid
FROM
    recc_user u
    LEFT JOIN recc_project_member pm ON pm.user_uid=u.uid
    LEFT JOIN recc_project p ON p.uid=pm.project_uid 
    LEFT JOIN recc_group_member gm ON gm.user_uid=u.uid
ORDER BY u.uid;

ON vs WHERE

ON is the JOIN condition; WHERE is the condition for filtering the result after the JOIN.

SELF JOIN

Joining a table with itself.

See also

Favorite site

author Zer0 sitemap