So far, you have worked with one table at a time. But real databases have many tables, and the interesting answers come from combining them.

In the previous article, you learned how to add and modify data. Now you will learn how to pull data from multiple tables at once using JOINs.

In our bookstore database, we have separate tables for books and authors. Why not put everything in one table?

Bad approach — one big table:

| book_title          | author_name         | author_country | price |
|---------------------|---------------------|----------------|-------|
| The Great Gatsby    | F. Scott Fitzgerald | USA            | 12.99 |
| Tender Is the Night | F. Scott Fitzgerald | USA            | 15.99 |
| 1984                | George Orwell       | UK             | 11.99 |
| Animal Farm         | George Orwell       | UK             | 8.99  |

See the problem? “F. Scott Fitzgerald” and “USA” appear twice. “George Orwell” and “UK” also appear twice. If the author changes their name, you need to update every row. This wastes space and causes errors.

Good approach — separate tables linked by IDs:

authors
| id | name                | country |
|----|---------------------|---------|
| 1  | F. Scott Fitzgerald | USA     |
| 2  | Harper Lee          | USA     |
| 3  | George Orwell       | UK      |
| 4  | Jane Austen         | UK      |
| 5  | J.D. Salinger       | USA     |

books
| id | title                   | author_id | price |
|----|-------------------------|-----------|-------|
| 1  | The Great Gatsby        | 1         | 12.99 |
| 2  | To Kill a Mockingbird   | 2         | 14.99 |
| 3  | 1984                    | 3         | 11.99 |
| 4  | Pride and Prejudice     | 4         | 9.99  |
| 5  | The Catcher in the Rye  | 5         | 13.99 |
| 6  | Animal Farm             | 3         | 8.99  |
| 7  | Tender Is the Night     | 1         | 15.99 |

The author_id column in books points to the id column in authors. This connection is the foundation of relational databases.

Primary Keys and Foreign Keys

A primary key is a column that uniquely identifies each row. In our tables, id is the primary key. No two rows can have the same id.

A foreign key is a column that references the primary key of another table. In books, the author_id column is a foreign key. It references authors.id.

-- When creating tables, you can define foreign keys:
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    price REAL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

Foreign keys ensure data integrity. The database can prevent you from adding a book with an author_id that does not exist in the authors table.

Our Updated Sample Database

Let us expand our bookstore database with customers and orders tables:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

INSERT INTO customers (id, name, city) VALUES
(1, 'Alex Johnson', 'New York'),
(2, 'Sam Wilson', 'London'),
(3, 'Jordan Smith', 'New York');

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES
(1, 1, 1, 1, '2026-01-15'),
(2, 1, 3, 2, '2026-01-15'),
(3, 2, 2, 1, '2026-02-01'),
(4, 3, 5, 1, '2026-02-10'),
(5, 1, 6, 1, '2026-03-01'),
(6, 2, 1, 1, '2026-03-05');

Now we have four related tables: authors, books, customers, and orders.

INNER JOIN — Matching Rows from Both Tables

INNER JOIN returns only the rows that have a match in both tables.

SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

| title                    | name                |
|--------------------------|---------------------|
| The Great Gatsby         | F. Scott Fitzgerald |
| To Kill a Mockingbird    | Harper Lee          |
| 1984                     | George Orwell       |
| Pride and Prejudice      | Jane Austen         |
| The Catcher in the Rye   | J.D. Salinger       |
| Animal Farm              | George Orwell       |
| Tender Is the Night      | F. Scott Fitzgerald |

How it works:

  1. SQL looks at each book
  2. For each book, it finds the matching author using books.author_id = authors.id
  3. If there is a match, the row appears in the result
  4. If a book has no matching author (author_id is NULL), it is skipped

Think of it as: “Give me books and their authors, but only where both exist.”

   books                  authors
┌─────────────┐      ┌─────────────┐
│             │      │             │
│    ┌────────┼──────┼────────┐    │
│    │ INNER  │      │  JOIN  │    │
│    │ (match)│      │        │    │
│    └────────┼──────┼────────┘    │
│             │      │             │
└─────────────┘      └─────────────┘
       Only the overlapping part

LEFT JOIN — All Rows from the Left Table

LEFT JOIN returns all rows from the left table, and matching rows from the right table. If there is no match, the right side columns are NULL.

SELECT books.title, authors.name
FROM books
LEFT JOIN authors ON books.author_id = authors.id;

If we had a book with author_id = NULL (like “Brave New World” from the previous article), the result would include that book with NULL for the author name.

| title            | name |
|------------------|------|
| Brave New World  | NULL |

Think of it as: “Give me all books, and add author info where available.”

   books (LEFT)           authors (RIGHT)
┌─────────────────┐      ┌─────────────┐
│                 │      │             │
│  ┌──────────────┼──────┼──────┐      │
│  │ ALL left rows│      │match │      │
│  │ + matching   │      │      │      │
│  └──────────────┼──────┼──────┘      │
│                 │      │             │
└─────────────────┘      └─────────────┘
  All of left + overlap

LEFT JOIN is the most commonly used join after INNER JOIN. Use it when you want all records from the main table, even if related data is missing.

Find books with no author:

SELECT books.title
FROM books
LEFT JOIN authors ON books.author_id = authors.id
WHERE authors.id IS NULL;

This returns only the books that have no matching author. The LEFT JOIN includes them, and the WHERE clause filters for NULL matches.

RIGHT JOIN — All Rows from the Right Table

RIGHT JOIN is the opposite of LEFT JOIN. It returns all rows from the right table, and matching rows from the left table.

SELECT books.title, authors.name
FROM books
RIGHT JOIN authors ON books.author_id = authors.id;

If an author has no books, they still appear in the result with NULL for the book title.

In practice, RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by swapping the table order:

-- These two queries give the same result:
SELECT * FROM books RIGHT JOIN authors ON books.author_id = authors.id;
SELECT * FROM authors LEFT JOIN books ON books.author_id = authors.id;

Most developers use LEFT JOIN and put the “main” table on the left.

Note: SQLite added RIGHT JOIN support in version 3.39.0 (2022). If you use an older version, use LEFT JOIN with swapped table order instead.

FULL JOIN — All Rows from Both Tables

FULL JOIN (or FULL OUTER JOIN) returns all rows from both tables. Where there is a match, the columns are filled in. Where there is no match, the missing columns are NULL.

SELECT books.title, authors.name
FROM books
FULL JOIN authors ON books.author_id = authors.id;

This gives you:

  • Books with matching authors (like INNER JOIN)
  • Books with no author (NULL on the right)
  • Authors with no books (NULL on the left)

FULL JOIN is useful when you want a complete picture. It is not used as often as INNER JOIN or LEFT JOIN.

Note: SQLite added FULL JOIN support in version 3.39.0 (2022). If you use an older version, you can simulate it with a UNION of a LEFT JOIN and a subquery.

CROSS JOIN — Every Combination

CROSS JOIN returns every possible combination of rows from both tables. If you have 5 books and 3 authors, you get 15 rows.

SELECT books.title, authors.name
FROM books
CROSS JOIN authors;

This pairs every book with every author, even when they are not related. It is rarely useful, but it can be helpful for generating test data or creating all possible combinations.

How many rows does a CROSS JOIN produce?

rows in table A x rows in table B

Be careful with large tables. Two tables with 1000 rows each produce 1,000,000 rows in a cross join.

Self-Joins — Joining a Table to Itself

Sometimes you need to compare rows within the same table. You do this with a self-join — joining a table to itself.

Let us add a manager_id column to a new employees table:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alex', NULL),
(2, 'Sam', 1),
(3, 'Jordan', 1),
(4, 'Taylor', 2);

Alex is the boss (no manager). Sam and Jordan report to Alex. Taylor reports to Sam.

Find each employee and their manager:

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

| employee | manager |
|----------|---------|
| Alex     | NULL    |
| Sam      | Alex    |
| Jordan   | Alex    |
| Taylor   | Sam     |

We join employees to itself. The alias e represents the employee, and m represents the manager. LEFT JOIN ensures Alex appears even though they have no manager.

Using Aliases

When you join tables, column names can get long. Aliases make queries shorter and easier to read.

-- Without aliases (verbose)
SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- With aliases (cleaner)
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

Use AS or just put the alias after the table name. Both work:

FROM books AS b    -- explicit
FROM books b       -- implicit (more common)

Pick short, meaningful aliases. b for books, a for authors, c for customers, o for orders.

Joining Multiple Tables

You can join more than two tables in a single query.

Find which customers ordered which books:

SELECT
    c.name AS customer,
    b.title AS book,
    o.quantity,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN books b ON o.book_id = b.id
ORDER BY o.order_date;

| customer      | book                    | quantity | order_date |
|---------------|-------------------------|----------|------------|
| Alex Johnson  | The Great Gatsby        | 1        | 2026-01-15 |
| Alex Johnson  | 1984                    | 2        | 2026-01-15 |
| Sam Wilson    | To Kill a Mockingbird   | 1        | 2026-02-01 |
| Jordan Smith  | The Catcher in the Rye  | 1        | 2026-02-10 |
| Alex Johnson  | Animal Farm             | 1        | 2026-03-01 |
| Sam Wilson    | The Great Gatsby        | 1        | 2026-03-05 |

This query starts with orders, joins customers to get the customer name, and joins books to get the book title. Each JOIN adds another table.

Add the author name too (three joins):

SELECT
    c.name AS customer,
    b.title AS book,
    a.name AS author,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN books b ON o.book_id = b.id
INNER JOIN authors a ON b.author_id = a.id
ORDER BY o.order_date;

| customer      | book                    | author              | order_date |
|---------------|-------------------------|---------------------|------------|
| Alex Johnson  | The Great Gatsby        | F. Scott Fitzgerald | 2026-01-15 |
| Alex Johnson  | 1984                    | George Orwell       | 2026-01-15 |
| Sam Wilson    | To Kill a Mockingbird   | Harper Lee          | 2026-02-01 |
| Jordan Smith  | The Catcher in the Rye  | J.D. Salinger       | 2026-02-10 |
| Alex Johnson  | Animal Farm             | George Orwell       | 2026-03-01 |
| Sam Wilson    | The Great Gatsby        | F. Scott Fitzgerald | 2026-03-05 |

Four tables combined in one query. This is the power of relational databases.

JOIN Summary

Here is a quick reference for all join types:

Join TypeReturns
INNER JOINOnly matching rows from both tables
LEFT JOINAll rows from left table + matching rows from right
RIGHT JOINAll rows from right table + matching rows from left
FULL JOINAll rows from both tables
CROSS JOINEvery combination of rows (cartesian product)
Self-joinA table joined to itself

For most queries, you will use INNER JOIN or LEFT JOIN. The others are for special cases.

Common Mistakes

1. Forgetting the ON clause

-- This is a CROSS JOIN, not an INNER JOIN!
SELECT b.title, a.name
FROM books b
INNER JOIN authors a;
-- Missing: ON b.author_id = a.id

Without ON, most databases will give you a syntax error. Some older databases treat it as a cross join instead. Always include the ON clause.

2. Accidental cross joins with comma syntax

-- Old-style join syntax (avoid this)
SELECT b.title, a.name
FROM books b, authors a
WHERE b.author_id = a.id;

This works, but if you forget the WHERE clause, it becomes a cross join. Use explicit JOIN syntax instead. It is clearer and harder to mess up.

3. Wrong column in the ON clause

-- Wrong: joining on the wrong column
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.id = a.id;
-- Should be: ON b.author_id = a.id

This matches book ID 1 with author ID 1, book ID 2 with author ID 2, and so on. The result looks plausible but is completely wrong. Always check that you are joining on the correct relationship columns.

What You Learned

In this article, you learned:

  • Primary keys uniquely identify rows
  • Foreign keys connect tables to each other
  • INNER JOIN returns matching rows from both tables
  • LEFT JOIN returns all rows from the left table
  • RIGHT JOIN returns all rows from the right table
  • FULL JOIN returns all rows from both tables
  • CROSS JOIN creates every possible combination
  • Self-joins compare rows within the same table
  • Aliases make join queries shorter
  • You can join three or more tables in one query

What’s Next?

In the next article, you will learn about aggregation functions like COUNT, SUM, and AVG, plus GROUP BY, subqueries, and CTEs. These let you summarize and analyze data.