JOIN

Table Relationships

In a relational database, data is often stored in multiple tables to avoid duplication. JOIN is used to combine rows from two or more tables based on related columns.

Example Tables

EMPLOYEE table:

DEPARTMENT table:

INNER JOIN

Returns rows that have a match in both tables.

SELECT K.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEE K
INNER JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT;

Result:

Dana does not appear because her ID_DEPT is NULL. FINANCE does not appear because it has no employees.

LEFT JOIN

Returns all rows from the left table and the matching rows from the right table.

SELECT K.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEE K
LEFT JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT;

Result:

RIGHT JOIN

Returns all rows from the right table and the matching rows from the left table.

SELECT K.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEE K
RIGHT JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT;

Result:

FULL OUTER JOIN

Returns all rows from both tables. NULL is displayed for rows without a match.

SELECT K.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEE K
FULL OUTER JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT;

Result:

CROSS JOIN

Produces a Cartesian product — every row from the left table is paired with every row from the right table.

SELECT K.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEE K
CROSS JOIN DEPARTMENT D;

Result: 4 employees × 3 departments = 12 rows.

SELF JOIN

Joins a table with itself, useful for hierarchical data.

-- Example: Display employee names alongside supervisor names
SELECT E.NAME AS EMPLOYEE, S.NAME AS SUPERVISOR
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.ID_SUPERVISOR = S.ID;

Visual Diagram of JOIN Types

INNER JOIN:        LEFT JOIN:         RIGHT JOIN:        FULL OUTER JOIN:
  A ∩ B              A ∪ (A ∩ B)        (A ∩ B) ∪ B        A ∪ B

 [A][B]            [A][B]            [A][B]            [A][B]
  ##                 ###                ###               ####
  ##                  #                  ##               ###

  [A]          [B]           [A]
   ∩      or    ∩      or    ∩      or    ∪      =  AB
  [B]          [A]          [B]

The ON Condition vs WHERE

-- ON: Determines how the tables are joined
-- WHERE: Filters the results after the join

SELECT K.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEE K
LEFT JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT
WHERE K.STATUS = 'Active';

Usage Tips

1. Use table aliases to keep queries short and readable
2. Always choose the right type of JOIN for your needs
3. INNER JOIN is the most commonly used
4. Watch performance — JOIN on large tables requires proper indexes

After combining data from multiple tables, we need to group it for analysis. Learn GROUP BY in the next chapter.