INNER JOIN
In this page:
What is an INNER JOIN?
An INNER JOIN matches rows from two tables based on a related column, like matching orders to their customers. It only includes rows where the join condition is met in both tables, so unmatched rows from either side are dropped entirely.
Example: What is an INNER JOIN?
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT orders.order_id, customers.name
FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;Explicit vs Implicit Join Syntax
Explicit joins use the INNER JOIN keyword along with an ON clause spelling out the match condition. Implicit joins use a comma and a WHERE clause instead. Explicit joins are preferred because the join logic is separated clearly from any additional filtering.
Example: Explicit vs Implicit Join Syntax
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
-- Explicit
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
-- Implicit
SELECT * FROM orders, customers WHERE orders.customer_id = customers.customer_id;INNER JOIN with Filtering
You can add a WHERE clause to filter the joined rows after the join itself has matched them. This narrows down your matches based on specific criteria, such as only orders placed in the last 30 days.
Example: INNER JOIN with Filtering
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT, order_date TEXT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1, '2024-01-01'), (102, 1, '2024-06-01');
SELECT orders.order_id, customers.name
FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date > '2024-03-01';Joining Multiple Tables
You can join three or more tables together in a single query, such as connecting orders to customers and to products. To do this, chain multiple INNER JOIN clauses in your query, each with its own ON condition.
Example: Joining Multiple 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
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id;INNER JOIN with Aggregations
Combining joins with GROUP BY lets you calculate group statistics across related tables, such as total spend per customer. You can find sum totals or averages per category once the join has connected the relevant rows together.
Example: INNER JOIN with Aggregations
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, 50), (102, 1, 30);
SELECT customers.name, SUM(orders.total) AS total_spend
FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customers.name;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: