SQL TL;DR: Joins Deep Dive (DQL)

SQL JOINs Deep Dive

/tldr: Combining related data across multiple tables.

Data Query Language (DQL) Relational Database Core Multi-Table Operations

THE CORE IDEA: WHY WE JOIN

Relational databases normalize data, meaning information is split across multiple tables (e.g., a Customer table and an Orders table). JOIN statements link these tables back together using a common column (the relationship or key).

The Result of a JOIN is a Virtual Table (temporarily combining rows).

Table A (Customers) + Table B (Orders) → Joined Table (Customer details + Order details)

Understanding Join Types

The **type** of JOIN determines which rows—those that match, or those that don't match—are included in the final result set.

1. INNER JOIN (The Default)

**Result:** Only rows that have matching values in **both** tables are returned. Non-matching rows are excluded.

SELECT A.name, B.item
FROM Customers A
INNER JOIN Orders B ON A.customer_id = B.customer_id;

2. LEFT (OUTER) JOIN

**Result:** All rows from the **LEFT** table (A) are returned. Matching rows from the RIGHT table (B) are included. If no match exists in B, the columns from B are NULL.

SELECT *
FROM Employees E
LEFT JOIN Departments D ON E.dept_id = D.id;

3. RIGHT (OUTER) JOIN

**Result:** All rows from the **RIGHT** table (B) are returned. Matching rows from the LEFT table (A) are included. If no match exists in A, the columns from A are NULL. (Less common, usually rewritten as a LEFT JOIN).

SELECT *
FROM Products P
RIGHT JOIN Categories C ON P.cat_id = C.id;

4. FULL (OUTER) JOIN

**Result:** All rows from **both** tables are returned. Where a match exists, they are joined. Where no match exists, the missing side's columns are NULL.

SELECT *
FROM Students S
FULL OUTER JOIN Courses C ON S.course_id = C.id;

Key Difference: ON vs. WHERE

The ON Clause

Defines the **relationship** between the tables. It is executed BEFORE the join occurs. For LEFT, RIGHT, and FULL joins, placing a filter here can change the nature of the join.

The WHERE Clause

Applies the **final filter** to the result set *after* the join has already occurred. This filters out rows that don't meet the criteria, regardless of the join type.

Practical Multi-Join Example


-- Goal: Get the name of every employee, their department name, 
-- and the title of any projects they are currently working on.

SELECT
    E.employee_name,
    D.department_name,
    P.project_title
FROM
    employees E                                      -- Table E (Our anchor - starts here)
LEFT JOIN
    departments D ON E.department_id = D.id          -- Always show the employee, even if department is unknown
INNER JOIN
    employee_projects EP ON E.id = EP.employee_id    -- Only show employees who are linked to a project
INNER JOIN
    projects P ON EP.project_id = P.id               -- Link the project ID to get the project title
WHERE
    P.is_active = TRUE;                              -- Final filter: only include active projects

Use **aliases** (E, D, P) to reference columns when multiple tables have the same column name.

Start connecting your data.