SQL Joins Overview – Combining Tables

What Are Joins?

A join combines rows from two or more tables based on a related column — typically a foreign key relationship. Instead of storing department names in the employees table (creating duplication), you store department IDs and join to a departments table when you need the name.

SQL

-- Without a join — you see IDs, not names
SELECT employee_id, name, dept_id
FROM employees;
-- employee_id | name    | dept_id
--           1 | Alice   |       2
--           2 | Bob     |       1

-- With INNER JOIN — departments table is brought in
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- name  | dept_name
-- Alice | Marketing
-- Bob   | Engineering

All SQL Join Types

Join Type	What It Returns	Typical Use Case
INNER JOIN	Only rows with matching values in both tables	Get employees with their department name (must have a department)
LEFT JOIN	All rows from the left table; NULL for unmatched right columns	List all employees, showing department name if assigned
RIGHT JOIN	All rows from the right table; NULL for unmatched left columns	List all departments, even those with no employees
FULL OUTER JOIN	All rows from both tables; NULLs on both sides for unmatched rows	Find mismatches between two datasets
SELF JOIN	A table joined to itself using different aliases	Employee–manager hierarchy in the same table
CROSS JOIN	Every combination of rows (Cartesian product)	Generate all size/colour product combinations

Sample Tables Used in This Section

All join examples in this section use two tables: employees and departments. Here is their structure and sample data:

SQL

CREATE TABLE departments (
  id        INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(100),
  dept_id    INT,          -- FK to departments.id (nullable = no department)
  manager_id INT,          -- FK to employees.id (self-referencing)
  salary     DECIMAL(10,2)
);

-- Sample data
INSERT INTO departments VALUES
  (1, 'Engineering'),
  (2, 'Marketing'),
  (3, 'HR'),
  (4, 'Finance');          -- No employees in Finance

INSERT INTO employees VALUES
  (1, 'Alice',   2, NULL, 85000),   -- Marketing, no manager
  (2, 'Bob',     1,    1, 72000),   -- Engineering, reports to Alice
  (3, 'Carol',   1,    1, 68000),   -- Engineering, reports to Alice
  (4, 'Dave',    3,    1, 55000),   -- HR, reports to Alice
  (5, 'Eve',  NULL, NULL, 45000);   -- No department assigned

ℹ️

Notice the two edge cases

Eve (id=5) has no department (dept_id is NULL). Finance (id=4) has no employees. These edge cases will appear in different results depending on which join type you use — pay attention to them as you work through each lesson.

Venn Diagrams

Venn diagrams help visualise which rows each join type returns. In the diagrams below, circle A = left table, circle B = right table, and the shaded area = rows returned.

Text – ASCII Venn Diagrams

INNER JOIN             LEFT JOIN
   A    B                  A    B
 (   |##|   )            (###|##|   )
     ↑                       ↑
  overlap only           all of A + overlap

RIGHT JOIN             FULL OUTER JOIN
   A    B                  A    B
 (   |##|###)            (###|##|###)
           ↑             ↑         ↑
  overlap + all B     everything from both

Choosing the Right Join

Question	Use
"Show me employees AND their department (must have one)"	INNER JOIN
"Show all employees — department if they have one, otherwise NULL"	LEFT JOIN
"Show all departments — employees if any exist, otherwise NULL"	RIGHT JOIN or LEFT JOIN (swap table order)
"Show everything from both tables, matched where possible"	FULL OUTER JOIN
"Find employees with no department assigned"	LEFT JOIN + WHERE dept.id IS NULL
"Show employee with their manager's name (same table)"	SELF JOIN
"Generate all possible combinations of two lists"	CROSS JOIN

💡

Prefer LEFT JOIN over RIGHT JOIN

RIGHT JOIN and LEFT JOIN are semantically identical — you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order. Most SQL developers use exclusively LEFT JOIN for consistency and readability. Mixing LEFT and RIGHT JOINs in the same query is confusing and should be avoided.

Summary

📋 Summary

Joins combine rows from two or more tables based on a matching column condition.
INNER JOIN — Only matched rows. Most common join type.
LEFT JOIN — All left rows + matched right rows (NULLs for unmatched right).
RIGHT JOIN — All right rows + matched left rows (equivalent to swapped LEFT JOIN).
FULL OUTER JOIN — All rows from both tables; NULLs where no match.
SELF JOIN — A table joined to itself; requires aliases to distinguish instances.
CROSS JOIN — Cartesian product. Every row × every row. No join condition.
Use table aliases (e, d) to keep multi-table queries readable.

FAQ

How many tables can I join in a single query?+

There is no hard ANSI limit on the number of tables you can join. In practice, most databases handle dozens of joins. However, each join multiplies query complexity, and very large joins can become slow. If you find yourself joining 10+ tables, consider whether a subquery, CTE, or view might make the query more manageable and easier to optimise.

What is the difference between ON and USING in a JOIN?+

ON e.dept_id = d.id — explicit: name the column from each table. Use when column names differ between tables. USING (dept_id) — shorthand when both tables have a column with the same name. USING also deduplicates the join column in SELECT * (it appears once). ON is more flexible and more commonly used.

Is JOIN without INNER, LEFT, or RIGHT an INNER JOIN?+

Yes — JOIN alone defaults to INNER JOIN in all major databases. Many developers write JOIN as a shorthand since INNER is the most common join type. For LEFT and RIGHT joins, the keyword is always required. Being explicit with INNER JOIN is slightly more readable when teaching or documenting.

What is the old comma-style join syntax?+

Older SQL (pre-ANSI 1992) used comma-separated tables in FROM with the join condition in WHERE: FROM employees e, departments d WHERE e.dept_id = d.id. This is a CROSS JOIN filtered by WHERE — functionally identical to INNER JOIN but harder to read and easier to write accidental Cartesian products. Always use explicit JOIN syntax; avoid the comma style in new code.