A JOIN combines rows from two (or more) tables based on a related column — usually a foreign key pointing to a primary key.

Instead of storing everything in one giant table, related data lives in separate tables, and joins stitch it back together when you need it.

• INNER JOIN: Return only rows where both tables have matching data.
• LEFT JOIN: Return all rows from the left table, plus matching rows from the right table. If no match, the right table's columns come back as NULL.
• RIGHT JOIN: Return all rows from the right table, plus matching rows from the left table. If no match, the left table's columns come back as NULL.
• CROSS JOIN: Return the Cartesian product of the two tables. Not supported in all SQL databases.
• SELF JOIN: Return rows from a table that match rows from the same table.

We'll use these two tables:

-- customers table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);
 
-- insert data into customers table
INSERT INTO customers (name, email)
VALUES
('Ram', 'ram@gmail.com'),
('Sita', 'sita@gmail.com'),
('Hari', 'hari@gmail.com'),
('Gita', 'gita@gmail.com');

mysql> SELECT * FROM customers;
+----+------+----------------+
| id | name | email          |
+----+------+----------------+
|  1 | Ram  | ram@gmail.com  |
|  2 | Sita | sita@gmail.com |
|  3 | Hari | hari@gmail.com |
|  4 | Gita | gita@gmail.com |
+----+------+----------------+

-- orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product VARCHAR(100),
    price DECIMAL(10,2),
 
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
);
 
-- insert data into orders table
INSERT INTO orders (customer_id, product, price)
VALUES
(1, 'Laptop', 90000),
(1, 'Mouse', 1500),
(2, 'Keyboard', 3000),
(2, 'Monitor', 25000),
(4, 'Phone', 50000);

mysql> SELECT * FROM orders;
+----+-------------+----------+----------+
| id | customer_id | product  | price    |
+----+-------------+----------+----------+
|  6 |           1 | Laptop   | 90000.00 |
|  7 |           1 | Mouse    |  1500.00 |
|  8 |           2 | Keyboard |  3000.00 |
|  9 |           2 | Monitor  | 25000.00 |
| 10 |           4 | Phone    | 50000.00 |
+----+-------------+----------+----------+

Return only rows where both tables have matching data.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

mysql> SELECT * FROM customers;
+----+------+----------------+
| id | name | email          |
+----+------+----------------+
|  1 | Ram  | ram@gmail.com  |
|  2 | Sita | sita@gmail.com |
|  3 | Hari | hari@gmail.com |
|  4 | Gita | gita@gmail.com |
+----+------+----------------+
mysql> SELECT * FROM orders;
+----+-------------+----------+----------+
| id | customer_id | product  | price    |
+----+-------------+----------+----------+
|  6 |           1 | Laptop   | 90000.00 |
|  7 |           1 | Mouse    |  1500.00 |
|  8 |           2 | Keyboard |  3000.00 |
|  9 |           2 | Monitor  | 25000.00 |
| 10 |           4 | Phone    | 50000.00 |
+----+-------------+----------+----------+

Example:

SELECT customers.name, orders.product
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

Output:

mysql> SELECT customers.name, orders.product
-> FROM customers
-> INNER JOIN orders
-> ON customers.id = orders.customer_id;
+------+----------+
| name | product  |
+------+----------+
| Ram  | Laptop   |
| Ram  | Mouse    |
| Sita | Keyboard |
| Sita | Monitor  |
| Gita | Phone    |
+------+----------+

Read it like a sentence: "Get the title from books and the name from authors, where each book's author_id matches an author's id."

INNER JOIN is the most commonly used join — when you just say JOIN with no other keyword, MySQL treats it as INNER JOIN.

Returns all rows from the left table (the one listed first, after FROM), plus matching rows from the right table. If there's no match, the right table's columns come back as NULL.

Return:

• All rows from the LEFT table
• Matching rows from the RIGHT table
• If no match → NULL

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON condition;

mysql> SELECT * FROM customers;
+----+------+----------------+
| id | name | email          |
+----+------+----------------+
|  1 | Ram  | ram@gmail.com  |
|  2 | Sita | sita@gmail.com |
|  3 | Hari | hari@gmail.com |
|  4 | Gita | gita@gmail.com |
+----+------+----------------+
mysql> SELECT * FROM orders;
+----+-------------+----------+----------+
| id | customer_id | product  | price    |
+----+-------------+----------+----------+
|  6 |           1 | Laptop   | 90000.00 |
|  7 |           1 | Mouse    |  1500.00 |
|  8 |           2 | Keyboard |  3000.00 |
|  9 |           2 | Monitor  | 25000.00 |
| 10 |           4 | Phone    | 50000.00 |
+----+-------------+----------+----------+

Example:

SELECT customers.name, orders.product, orders.price
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

Output:

mysql> SELECT customers.name, orders.product, orders.price
-> FROM customers
-> LEFT JOIN orders
-> ON customers.id = orders.customer_id;
+------+----------+----------+
| name | product  | price    |
+------+----------+----------+
| Ram  | Laptop   | 90000.00 |
| Ram  | Mouse    |  1500.00 |
| Sita | Keyboard |  3000.00 |
| Sita | Monitor  | 25000.00 |
| Hari | NULL     |     NULL |
| Gita | Phone    | 50000.00 |
+------+----------+----------+

The opposite of LEFT JOIN — returns all rows from the right table, plus matching rows from the left. Non-matches on the left come back as NULL.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON condition;

mysql> SELECT * FROM customers;
+----+------+----------------+
| id | name | email          |
+----+------+----------------+
|  1 | Ram  | ram@gmail.com  |
|  2 | Sita | sita@gmail.com |
|  3 | Hari | hari@gmail.com |
|  4 | Gita | gita@gmail.com |
+----+------+----------------+
mysql> SELECT * FROM orders;
+----+-------------+----------+----------+
| id | customer_id | product  | price    |
+----+-------------+----------+----------+
|  6 |           1 | Laptop   | 90000.00 |
|  7 |           1 | Mouse    |  1500.00 |
|  8 |           2 | Keyboard |  3000.00 |
|  9 |           2 | Monitor  | 25000.00 |
| 10 |           4 | Phone    | 50000.00 |
+----+-------------+----------+----------+

Example:

SELECT customers.name, orders.product, orders.price
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

mysql> SELECT customers.name, orders.product, orders.price
-> FROM customers
-> RIGHT JOIN orders
-> ON customers.id = orders.customer_id;
+------+----------+----------+
| name | product  | price    |
+------+----------+----------+
| Ram  | Laptop   | 90000.00 |
| Ram  | Mouse    |  1500.00 |
| Sita | Keyboard |  3000.00 |
| Sita | Monitor  | 25000.00 |
| Gita | Phone    | 50000.00 |
+------+----------+----------+

In practice, most people just rewrite a RIGHT JOIN as a LEFT JOIN with the tables swapped — it's easier to read consistently.

A CROSS JOIN in SQL returns the Cartesian product of two tables. This means every row from the first table is combined with every row from the second table.

Number of Rows:

If:

• Table A has m rows
• Table B has n rows

Then the result of a CROSS JOIN has m x n rows.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

SELECT customers.name, orders.product, orders.price
FROM customers
CROSS JOIN orders;

Output:

mysql> SELECT customers.name, orders.product, orders.price
-> FROM customers
-> CROSS JOIN orders;
+------+----------+----------+
| name | product  | price    |
+------+----------+----------+
| Gita | Laptop   | 90000.00 |
| Hari | Laptop   | 90000.00 |
| Sita | Laptop   | 90000.00 |
| Ram  | Laptop   | 90000.00 |
| Gita | Mouse    |  1500.00 |
| Hari | Mouse    |  1500.00 |
| Sita | Mouse    |  1500.00 |
| Ram  | Mouse    |  1500.00 |
| Gita | Keyboard |  3000.00 |
| Hari | Keyboard |  3000.00 |
| Sita | Keyboard |  3000.00 |
| Ram  | Keyboard |  3000.00 |
| Gita | Monitor  | 25000.00 |
| Hari | Monitor  | 25000.00 |
| Sita | Monitor  | 25000.00 |
| Ram  | Monitor  | 25000.00 |
| Gita | Phone    | 50000.00 |
| Hari | Phone    | 50000.00 |
| Sita | Phone    | 50000.00 |
| Ram  | Phone    | 50000.00 |
+------+----------+----------+

When to Use:

• Generate all possible combinations of two sets of data.
• Create test data.
• Produce combinations such as all products with all sizes or all students with all subjects.

A SELF JOIN is a join where a table is joined with itself.

It is commonly used to compare rows within the same table or to represent hierarchical relationships (e.g., employees and their managers).

Syntax:

SELECT a.column_name, b.column_name
FROM TableName a
JOIN TableName b
ON a.common_column = b.common_column;

Employees:
| EmpID | Name    | ManagerID |
| ----- | ------- | --------- |
|     1 | Alice   |      NULL |
|     2 | Bob     |         1 |
|     3 | Charlie |         1 |
|     4 | David   |         2 |

Example:

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
INNER JOIN Employees m
ON e.ManagerID = m.EmpID;

Output:

| Employee | Manager |
| -------- | ------- |
| Bob      | Alice   |
| Charlie  | Alice   |
| David    | Bob     |

Common Uses:

• Employee → Manager relationships
• Parent → Child relationships
• Comparing rows within the same table

Example:

Find Ram's orders:

SELECT customers.name, orders.product
FROM customers
JOIN orders
ON customers.id = orders.customer_id
WHERE customers.name = 'Ram';

Output:

| name  | product |
| ----- | ------- |
| Ram   | Laptop  |
| Ram   | Mouse   |

Using aliases can make your SQL queries more concise and easier to read, especially when working with complex queries involving multiple tables.

Instead of:

customers.name and orders.product

Use aliases:

SELECT c.name, o.product
FROM customers c
JOIN orders o
ON c.id = o.customer_id;

-- Inner Join
SELECT customers.name, orders.product
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;
 
-- Left Join
SELECT customers.name, orders.product, orders.price
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;
 
-- Right Join
SELECT customers.name, orders.product, orders.price
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;
 
-- Cross Join
SELECT customers.name, orders.product, orders.price
FROM customers
CROSS JOIN orders;
 
-- Self Join
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
INNER JOIN Employees m
ON e.ManagerID = m.EmpID;

FROM books
INNER JOIN authors ON books.author_id = authors.id