What is a MySQL Join?
A JOIN clause(खंड) in MySQL is used to retrieve(पुनः प्राप्त करना) data from multiple tables by linking them through a common column (often a primary key in one table and a foreign key in another). The result is a combined dataset that includes columns from all joined tables.
For example:
- You have a customers table with customer details.
- You have an orders table with order details.
- You want to see which customers placed which orders. A JOIN helps you combine these tables.
Types of MySQL Joins
There are several types of joins, each serving a different purpose. Let’s go through them one by one:
- INNER JOIN (or Simple Join)
- LEFT JOIN (or Left Outer Join)
- RIGHT JOIN (or Right Outer Join)
- FULL JOIN (or Full Outer Join)
- CROSS JOIN (or Cartesian Join)
1. INNER JOIN
- What it does: Returns only the rows where there is a match in both tables.
- Use case: When you only want data that exists in both tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;Example: Let’s say we have two tables:
- customers: Stores customer info.
- orders: Stores order info.
customers table:
| customer_id | customer_name | city |
|---|---|---|
| 1 | Alice | New York |
| 2 | Bob | Chicago |
| 3 | Charlie | Miami |
orders table:
| order_id | customer_id | order_amount |
|---|---|---|
| 101 | 1 | 500 |
| 102 | 2 | 300 |
| 103 | 4 | 700 |
Query:
SELECT customers.customer_name, orders.order_id, orders.order_amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;Result:
| customer_name | order_id | order_amount |
|---|---|---|
| Alice | 101 | 500 |
| Bob | 102 | 300 |
Explanation:
- Only Alice and Bob appear because they have matching customer_id values in the orders table.
- Charlie (no orders) and order 103 (no matching customer) are excluded.
2. LEFT JOIN (Left Outer Join)
- What it does: Returns all rows from the left table and the matching rows from the right table. If there’s no match, NULL is returned for the right table’s columns.
- Use case: When you want all records from the left table, even if there’s no corresponding data in the right table.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;Example (using the same tables):
SELECT customers.customer_name, orders.order_id, orders.order_amount
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;Result:
| customer_name | order_id | order_amount |
|---|---|---|
| Alice | 101 | 500 |
| Bob | 102 | 300 |
| Charlie | NULL | NULL |
Explanation:
- All customers are included (Alice, Bob, Charlie).
- Charlie has no orders, so order_id and order_amount are NULL.
- Order 103 is excluded because its customer_id (4) doesn’t exist in the customers table.
3. RIGHT JOIN (Right Outer Join)
- What it does: Returns all rows from the right table and the matching rows from the left table. If there’s no match, NULL is returned for the left table’s columns.
- Use case: When you want all records from the right table, even if there’s no corresponding data in the left table.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT customers.customer_name, orders.order_id, orders.order_amount
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;Result:
| customer_name | order_id | order_amount |
|---|---|---|
| Alice | 101 | 500 |
| Bob | 102 | 300 |
| NULL | 103 | 700 |
Explanation:
- All orders are included (101, 102, 103).
- Order 103 has no matching customer (customer_id 4 doesn’t exist), so customer_name is NULL.
- Charlie is excluded because he has no orders.
4. FULL JOIN (Full Outer Join)
- What it does: Returns all rows from both tables, with NULL in places where there’s no match.
- Use case: When you want all records from both tables, regardless of matches.
- Note: MySQL doesn’t directly support FULL JOIN, but you can simulate it using a combination of LEFT JOIN and RIGHT JOIN with UNION.
Simulated Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT customers.customer_name, orders.order_id, orders.order_amount
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
UNION
SELECT customers.customer_name, orders.order_id, orders.order_amount
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;Result:
| customer_name | order_id | order_amount |
|---|---|---|
| Alice | 101 | 500 |
| Bob | 102 | 300 |
| Charlie | NULL | NULL |
| NULL | 103 | 700 |
Explanation:
- All customers and all orders are included.
- Charlie has no orders, so his order_id and order_amount are NULL.
- Order 103 has no matching customer, so customer_name is NULL.
5. CROSS JOIN
- What it does: Returns the Cartesian product of both tables, meaning every row from the first table is paired with every row from the second table.
- Use case: Rarely used, but handy for generating all possible combinations (e.g., for testing or specific scenarios).
Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;Example:
SELECT customers.customer_name, orders.order_id
FROM customers
CROSS JOIN orders;Result:
| customer_name | order_id |
|---|---|
| Alice | 101 |
| Alice | 102 |
| Alice | 103 |
| Bob | 101 |
| Bob | 102 |
| Bob | 103 |
| Charlie | 101 |
| Charlie | 102 |
| Charlie | 103 |
Explanation:
- There are 3 customers and 3 orders, so the result has 3 × 3 = 9 rows.
- No ON condition is needed because it pairs every row from customers with every row from orders.
Key Points for Beginners
- Common Column: Joins rely on a shared column (e.g., customer_id in both tables). This is often a primary key in one table and a foreign key in another.
- Choosing the Right Join:
- Use INNER JOIN for matched data only.
- Use LEFT JOIN to include all rows from the left table.
- Use RIGHT JOIN to include all rows from the right table.
- Use FULL JOIN (simulated) for all rows from both tables.
- Use CROSS JOIN for all possible combinations.
- Performance: Joins can be slow on large tables, so ensure the common column is indexed (e.g., primary or foreign keys are usually indexed).
- Table Aliases: To make queries shorter, you can use aliases
SELECT c.customer_name, o.order_id
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;Practical Example: Setting Up and Running Joins
Let’s create the tables, insert data, and run a few joins to solidify your understanding.
Step 1: Create Tables
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
city VARCHAR(50)
);CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_amount INT
);Step 2: Insert Data
INSERT INTO customers (customer_id, customer_name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Chicago'),
(3, 'Charlie', 'Miami');INSERT INTO orders (order_id, customer_id, order_amount) VALUES
(101, 1, 500),
(102, 2, 300),
(103, 4, 700);Step 3: Run Joins
INNER JOIN:
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;Output: Alice and Bob’s orders.
LEFT JOIN:
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;Output: All customers, with Charlie’s order details as NULL
RIGHT JOIN
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;Output: All orders, with order 103’s customer as NULL
1. Setting Up a More Complex Example
To make things practical, let’s use a slightly larger dataset with three tables to simulate a real-world scenario, like an e-commerce database:
- customers: Customer information.
- orders: Orders placed by customers.
- products: Products in the orders.
Create Tables:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
city VARCHAR(50)
);CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
order_amount INT
);CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price INT
);Insert Data:
INSERT INTO customers (customer_id, customer_name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Chicago'),
(3, 'Charlie', 'Miami'),
(4, 'Diana', 'Seattle');INSERT INTO orders (order_id, customer_id, product_id, order_amount) VALUES
(101, 1, 1, 500),
(102, 2, 2, 300),
(103, 4, 3, 700),
(104, 1, 2, 300);INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 500),
(2, 'Phone', 300),
(3, 'Tablet', 700),
(4, 'Headphones', 100);Table Summary:
- customers: 4 customers (Alice, Bob, Charlie, Diana).
- orders: 4 orders (2 by Alice, 1 by Bob, 1 by Diana).
- products: 4 products (Laptop, Phone, Tablet, Headphones).
- Charlie has no orders, and Headphones have no orders.
2. Advanced Join Examples
Let’s explore queries that combine these tables, including multiple joins and different join types.
Example 1: INNER JOIN with Three Tables
Goal: List all orders with customer names and product names
SELECT
c.customer_name,
o.order_id,
p.product_name,
o.order_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;Result:
| customer_name | order_id | product_name | order_amount |
|---|---|---|---|
| Alice | 101 | Laptop | 500 |
| Alice | 104 | Phone | 300 |
| Bob | 102 | Phone | 300 |
| Diana | 103 | Tablet | 700 |
Explanation:
- The query joins customers to orders (via customer_id) and orders to products (via product_id).
- Only rows with matches in all three tables appear.
- Charlie (no orders) and Headphones (no orders) are excluded.
Example 2: LEFT JOIN to Include All Customers
Goal: List all customers, including those without orders.
SELECT
c.customer_name,
o.order_id,
o.order_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;Result:
| customer_name | order_id | order_amount |
|---|---|---|
| Alice | 101 | 500 |
| Alice | 104 | 300 |
| Bob | 102 | 300 |
| Charlie | NULL | NULL |
| Diana | 103 | 700 |
Explanation:
- All customers are included.
- Charlie has no orders, so his order_id and order_amount are NULL.
Example 3: RIGHT JOIN to Include All Products
Goal: List all products, including those not ordered.
SELECT
p.product_name,
o.order_id,
c.customer_name
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
LEFT JOIN customers c ON o.customer_id = c.customer_id;Result:
| product_name | order_id | customer_name |
|---|---|---|
| Laptop | 101 | Alice |
| Phone | 102 | Bob |
| Phone | 104 | Alice |
| Tablet | 103 | Diana |
| Headphones | NULL | NULL |
Explanation:
- All products are included.
- Headphones have no orders, so order_id and customer_name are NULL.
- We used LEFT JOIN (instead of RIGHT JOIN) because MySQL’s RIGHT JOIN is less common, but you could rewrite it as:
SELECT p.product_name, o.order_id, c.customer_name
FROM orders o
RIGHT JOIN products p ON o.product_id = p.product_id
LEFT JOIN customers c ON o.customer_id = c.customer_id;Example 4: Simulating FULL JOIN
Goal: List all customers and all products, showing all possible matches.
SELECT
c.customer_name,
o.order_id,
p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN products p ON o.product_id = p.product_id
UNION
SELECT
c.customer_name,
o.order_id,
p.product_name
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
LEFT JOIN customers c ON o.customer_id = c.customer_id;Result:
| customer_name | order_id | product_name |
|---|---|---|
| Alice | 101 | Laptop |
| Alice | 104 | Phone |
| Bob | 102 | Phone |
| Charlie | NULL | NULL |
| Diana | 103 | Tablet |
| NULL | NULL | Headphones |
Explanation:
- Combines all customers and products.
- Charlie (no orders) and Headphones (no orders) are included with NULL values.
3. Non-Equality Join Conditions
Joins don’t always use = (equality). You can use other operators like <, >, or !=.
Example: Find customers whose order amounts are greater than the price of the product they ordered.
SELECT
c.customer_name,
o.order_amount,
p.product_name,
p.price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id
WHERE o.order_amount > p.price;Result:
- If any order_amount exceeds the price, it’ll show here. In our data, order_amount equals price, so this might return no rows unless we modify the data.
Use Case: Non-equality joins are useful for range-based comparisons, like finding orders placed within a certain price range.
4. Joining Multiple Tables
You can chain multiple joins to combine more tables. For example:
SELECT
c.customer_name,
c.city,
o.order_id,
p.product_name,
p.price
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN products p ON o.product_id = p.product_id;Result:
| customer_name | city | order_id | product_name | price |
|---|---|---|---|---|
| Alice | New York | 101 | Laptop | 500 |
| Alice | New York | 104 | Phone | 300 |
| Bob | Chicago | 102 | Phone | 300 |
| Charlie | Miami | NULL | NULL | NULL |
| Diana | Seattle | 103 | Tablet | 700 |
Explanation:
- Combines all three tables.
- LEFT JOIN ensures all customers are included, even Charlie (no orders).
Tip: When joining multiple tables:
- Use aliases (e.g., c, o, p) to keep queries readable.
- Be clear about join order, as it affects performance and results.
