Skip to content

Module 5 - SQL Joins

2026-02-18 21:07

Tags: #ADV_DBMS

Author: Duke Hsu

Module 5 - SQL Joins

Topic / Concept

  • Three types of joins
  • Compare the difference of join and subqueries

Three types of joins

INNER JOIN

  • Return records that have matching values in both tables.

Syntax:

1
2
3
4
SELECT columns 
    FROM table_1
    INNER JOIN table_2
        ON table_1.common_column = table_2.common_column;
flowchart LR subgraph A["STUDENT"] A1[(StudentID)] A2[StudentName] end subgraph B["ENROLLMENT"] B1[(StudentID)] B2[CourseID] end A1 -->|ON StudentID| J{{INNER JOIN}} B1 -->|ON StudentID| J J --> R["JOINED RESULT"]

Example:

1
2
3
4
SELECT user.name, orders.orderID, orders.amount
    FROM users AS U
    INNER JOIN orders AS O
        ON U.userID = O.userID

NATURAL JOIN

  • A NATURAL JOIN is a type of inner join that automatically matches columns between the two tables that have the same name and data type. The natural join implicitly uses these columns to perform the join which can sometimes simplify queries.

Syntax

1
2
3
SELECT columns 
    FROM table_1
        NATURAL JOIN table_2;

Example:

1
2
3
SELECT name, dept_name 
    FROM employees
        NATURAL JOIN departments;

Note

Inner join: requires explicit columns to be specified in the ON clause for the Join condition Natural join: Automatically uses all columns with the same name and data type in both tables to join the tables.

flowchart LR subgraph STUDENT S1[(StudentID)] S2[StudentName] end subgraph ENROLLMENT E1[(StudentID)] E2[CourseID] end S1 --> J{{NATURAL JOIN}} E1 --> J J --> P[π Remove Duplicate StudentID] P --> R["Final Relation"]

LEFT JOIN

  • LEFT JOIN, returns all the rows from the left table, even if there are no matches in the right table , if there are no matches, NULLvalues will be returned in the columns of the right table
  • Right table not match -> NULL

Syntax:

1
2
3
4
SELECT column1,column2, column3....
    FROM left_table
    LEFT JOIN right_table
        ON left_table.column1 = ritgh_table.column1;

Example:

1
2
3
4
SELECT user.name, orders.orderID, orders.amount
    FROM users AS U
    LEFT JOIN orders AS O 
        ON U.userID = O.userID;
flowchart LR subgraph A["users (LEFT TABLE)"] A1[(usersID)] A2[usersName] end subgraph B["orders (RIGHT TABLE)"] B1[(usersID)] B2[orderID] B3[amount] end A1 -->|ON usersID| J{{LEFT JOIN}} B1 -->|Match if Exists| J J --> R["ALL users + MATCHED orders (Unmatched → NULL)"]

RIGHT JOIN

  • RIGHT JOIN returns all rows from the right table , event if there are no matches in the left table , unmatched left-side columns will be NULL
  • Left table not matched -> NULL

Syntax:

1
2
3
4
SELECT user.name, orders.orderID, orders.amount
    FROM users AS U
    RIGHT JOIN orders AS O 
        ON U.userID = O.userID;

Example:

1
2
3
4
SELECT user.name, orders.orderID, orders.amount
    FROM users AS U
    RIGHT JOIN orders AS O 
        ON U.userID = O.userID;
flowchart LR subgraph A["users (LEFT TABLE)"] A1[(usersID)] A2[usersName] end subgraph B["orders (RIGHT TABLE)"] B1[(usersID)] B2[ordersID] B3[amount] end A1 -->|Match if Exists| J{{RIGHT JOIN}} B1 -->|ON usersID| J J --> R["ALL orders + MATCHED users (Unmatched → NULL)"]

CROSS JOIN

  • The CROSS JOIN keyword returns the Cartesian product of two or more tables(combines every row from the first table with every row from the second table)

Note

嵌入式for loop 的概念， 例子：2個3rows的table 通過cross join 會變成1個9rows的table

Syntax :

1
2
3
SELECT column1,column2....
    FROM table1
        CROSS JOIN table2;

Example:

1
2
3
SELECT * 
    FROM meals
        CROSS JOIN drinks;

FULL OUTER JOIN

  • FULL OUTER JOIN , returns all records when there is a match in either left or right table

Syntax:

1
2
3
4
5
SELECT column1,column2,column3....
    FROM table1
        FULL OUTER JOIN table2
    ON table1.column1 = table2.column2
    WHERE condition;

Example:

1
2
3
4
5
SELECT CITY, STATE_PROVINCE
    FROM  locations AS L
        FULL OUTER JOIN countries AS C
    ON L.COUNTRY_ID = C.COUNTRY_ID
    WHERE CITY IS NOT NULL ;

SELF JOIN

  • A self join is a regular join, but the table is joined with itself .

!!note 使用場景： 同一張表，既存在員工， 也存在主管； A single table contains both employees and manager.

Syntax

1
2
3
SELECT column_name(s)
    FROM table1 T1, table1 T2
    WHERE condition;

Example

1
2
3
4
5
6
7
SELECT 
    e.FIRST_NAME AS Employees,
    m.FIRST_NAME AS Manager
    FROM employees  e
    JOIN employees  m

ON e.MANAGER_ID = m.MANAGER_ID;

Gemini canvas

https://gemini.google.com/share/531b591e835c

References

Module 5: SQL Joins.PPTX