Multiple Table Joins
In this page:
Joining Three Tables
You can connect three or more tables in a single query when your data spans multiple related tables. To do this, simply chain multiple JOIN clauses one after another, each linking to a previously joined table.
Example: Joining Three Tables
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT, product_id INT);
CREATE TABLE products (product_id INT, product_name TEXT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO products VALUES (10, 'Keyboard');
INSERT INTO orders VALUES (101, 1, 10);
SELECT customers.name, products.product_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products ON orders.product_id = products.product_id;Mixing INNER and LEFT JOINs
You are not limited to using only one type of join across a query. You can freely mix INNER JOIN and LEFT JOIN inside the same query, such as requiring a match on one relationship while allowing another to be optional.
Example: Mixing INNER and LEFT JOINs
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
CREATE TABLE loyalty (customer_id INT, tier TEXT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
SELECT customers.name, orders.order_id, loyalty.tier
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN loyalty ON customers.customer_id = loyalty.customer_id;Using Table Aliases
When joining many tables, column names can get confusing, especially if several tables share a column like id or name. Always use short table aliases to keep your multi-join queries clean and readable, and to disambiguate which table each column comes from.
Example: Using Table Aliases
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
SELECT c.name, o.id AS order_id
FROM orders o
JOIN customers c ON o.customer_id = c.id;Joining via Foreign Keys
Multi-table joins usually follow the path of your database relationships as defined by your schema. You typically join parent tables to child tables using foreign keys, walking the relationship graph one hop at a time.
Example: Joining via Foreign Keys
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
CREATE TABLE order_items (order_id INT, product_name TEXT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
INSERT INTO order_items VALUES (101, 'Keyboard');
SELECT customers.name, order_items.product_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id;Filtering Multiple Table Joins
You can add a WHERE clause at the end of your join chain to filter the combined result set. This allows you to filter the combined rows of all the joined tables together, after all the joins have already been resolved.
Example: Filtering Multiple Table Joins
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT, total INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1, 500);
SELECT customers.name, orders.total
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.total > 100;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: