MySQL Joins Overview
In this page:
What is a JOIN?
A JOIN combines rows from two or more tables into a single result set, matching rows together based on a related column that exists in both tables, such as a customer_id that links a customers table to an orders table.
Example: What is a JOIN?
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'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1, 50), (102, 2, 75);
SELECT orders.order_id, customers.name
FROM orders JOIN customers ON orders.customer_id = customers.customer_id;Why Combine Multiple Tables?
Splitting related data into separate tables avoids repeating the same information over and over -- a customer's name is stored once in the customers table, not duplicated on every one of their orders -- and JOIN is what reconnects that data when a query needs to see it together.
Example: Why Combine Multiple Tables?
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), (102, 1), (103, 1);
SELECT orders.order_id, customers.name
FROM orders JOIN customers ON orders.customer_id = customers.customer_id;Overview of Join Types
MySQL supports several join types, including INNER JOIN, which returns only matching rows, LEFT and RIGHT JOIN, which keep all rows from one side even without a match, and CROSS JOIN and SELF JOIN for other special cases, each covered in its own topic.
Example: Overview of Join Types
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), (102, 2);
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;
SELECT * FROM orders CROSS JOIN customers;The ON Clause
The ON clause that follows a JOIN specifies the exact condition used to match rows between the two tables, most commonly comparing a foreign key in one table to the primary key it references in the other, and can include extra conditions beyond a simple equality check.
Example: The ON Clause
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), (102, 1, 20);
SELECT orders.order_id, customers.name
FROM orders JOIN customers
ON orders.customer_id = customers.customer_id AND orders.total > 100;Choosing the Right Join
Choosing the right join type depends on what should happen to rows without a match: INNER JOIN is right when only matched rows are useful, while LEFT or RIGHT JOIN are right when you also need to see rows that have no corresponding match on the other side.
Example: Choosing the Right 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 customers.name FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
SELECT customers.name FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: