Joins & Aggregation

What a JOIN Is

A JOIN combines rows from two or more tables using a related column: a column whose values link a row in one table to a row in another.

Think of two lists in an office. One lists employees and their department number. The other lists department numbers and their names. A JOIN reads both lists and matches each employee to their department name.

Sample Tables

Employees

Departments

department_id is the related column. Two rows are special:

• Neha has no department (NULL).
• Finance has no employees.

How each JOIN type treats these two rows is the whole difference between them.

Click to zoom

Anatomy of a JOIN Query

SELECT
  e.name,
  d.department_name
FROM Employees e                          -- left table, alias e
INNER JOIN Departments d                  -- right table, alias d
  ON e.department_id = d.department_id;   -- how rows match

Aliases are optional, but they keep queries short and are required when a column name exists in both tables.

1. INNER JOIN

Returns only rows that match in both tables.

SELECT e.name, d.department_name
FROM Employees e
INNER JOIN Departments d
  ON e.department_id = d.department_id;

Why: Neha's NULL matches no department, and no employee is in Finance (103), so neither appears.

JOIN on its own means INNER JOIN.

2. LEFT JOIN

Returns all rows from the left table, plus matching rows from the right. If there's no match, the right table's columns are NULL.

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id;

Why: every employee appears, even Neha, who has no department.

This answers the practice question: "Show all employees, including those without a department, with their department name when available." "All employees" means the Employees table must keep every row, so it goes on the left of a LEFT JOIN.

3. RIGHT JOIN

Returns all rows from the right table, plus matching rows from the left.

SELECT e.name, d.department_name
FROM Employees e
RIGHT JOIN Departments d
  ON e.department_id = d.department_id;

Why: every department appears, even Finance, which has no employees.

A RIGHT JOIN is a LEFT JOIN with the tables swapped. Most teams write LEFT JOIN only, which keeps queries easier to read:

-- same result as the RIGHT JOIN above
SELECT e.name, d.department_name
FROM Departments d
LEFT JOIN Employees e
  ON e.department_id = d.department_id;

4. FULL OUTER JOIN

Returns all rows from both tables, matching them where possible. Unmatched rows on either side get NULLs.

SELECT e.name, d.department_name
FROM Employees e
FULL OUTER JOIN Departments d
  ON e.department_id = d.department_id;

Why: everyone from both tables appears: Neha without a department, Finance without employees.

SQL Server supports FULL OUTER JOIN. MySQL does not; there you combine a LEFT JOIN and a RIGHT JOIN with UNION.

5. CROSS JOIN

Returns every possible combination of rows from both tables. It has no ON condition. The result is called a Cartesian product.

SELECT e.name, d.department_name
FROM Employees e
CROSS JOIN Departments d;

14 employees × 3 departments = 12 rows
2 
3Aakash–Engineering   Aakash–HR   Aakash–Finance
4Priya–Engineering    Priya–HR    Priya–Finance
5Rahul–Engineering    Rahul–HR    Rahul–Finance
6Neha–Engineering     Neha–HR     Neha–Finance

Why: each employee is paired with every department, ignoring their real department.

Real use: generating combinations, like every size for every T-shirt colour, or every day of a month for every store in a report. Careful: 10,000 × 10,000 rows = 100 million rows.

6. SELF JOIN

A self join joins a table to itself. It's not a new keyword; you use a normal JOIN with two different aliases for the same table. It's useful when a row points to another row in the same table, like an employee and their manager.

Employees (with managers)

manager_id holds another employee's employee_id.

SELECT
  e.name AS employee,
  m.name AS manager
FROM Employees e                  -- e = the employee
LEFT JOIN Employees m             -- m = the same table, as the manager
  ON e.manager_id = m.employee_id;

Why LEFT JOIN: Amit has no manager. An INNER JOIN would drop him from the result.

Common Traps

1. Finding rows with no match. Use a LEFT JOIN and keep only rows where the right side is NULL:

-- employees without a department
SELECT e.name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id
WHERE d.department_id IS NULL;        -- Neha

-- departments without employees
SELECT d.department_name
FROM Departments d
LEFT JOIN Employees e
  ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;          -- Finance

2. A filter in WHERE can turn a LEFT JOIN into an INNER JOIN.

-- Filter in WHERE: runs AFTER the join
-- Neha's department_name is NULL, NULL = 'Engineering' is not true, so Neha is removed
SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';
-- Aakash, Rahul

-- Filter in ON: decides which departments may match, every employee stays
SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id
 AND d.department_name = 'Engineering';
-- Aakash Engineering, Priya NULL, Rahul Engineering, Neha NULL

3. NULL never matches NULL. NULL = NULL is not true in SQL, so rows with NULL in the join column never match anything, not even another NULL. That's why Neha drops out of the INNER JOIN.

4. One row can appear many times. If one left row matches three right rows, the result has three rows for it. Joining employees to a table of their projects returns one row per project, not one per employee.

5. Row order isn't guaranteed. SQL Server can return rows in any order. Add ORDER BY whenever order matters.

Quick Revision

Interview tips:

• LEFT JOIN = LEFT OUTER JOIN, RIGHT JOIN = RIGHT OUTER JOIN, FULL JOIN = FULL OUTER JOIN. The word OUTER is optional.
• JOIN alone = INNER JOIN.
• "Show all X, even if..." almost always means LEFT JOIN with X on the left.
• "Find X that don't have Y" means LEFT JOIN + WHERE y.id IS NULL.

Summary

1JOIN        → combine tables on a related column (ON condition)
2INNER       → matches only
3LEFT        → all left rows, NULLs where no match
4RIGHT       → all right rows (usually rewritten as LEFT)
5FULL OUTER  → all rows from both sides
6CROSS       → every combination, no ON, rows = A × B
7SELF        → same table, two aliases (employee → manager)
8Traps       → WHERE on right table kills LEFT JOIN rows
9            → NULL never matches NULL
10            → one row can repeat for many matches
11            → add ORDER BY for a fixed order

Next, GROUP BY and HAVING show how to summarise joined data, like counting employees per department.