JOINS in MySQL
👉 A JOIN is used to combine rows from two or more tables based on a related column.
Let’s assume we have two tables:
Customers
| id | name | city |
|---|---|---|
| 1 | John | Delhi |
| 2 | Priya | Mumbai |
| 3 | Ahmed | Kolkata |
Orders
| order_id | customer_id | product |
|---|---|---|
| 101 | 1 | Laptop |
| 102 | 2 | Mobile |
| 103 | 1 | Tablet |
| 104 | 4 | Camera |
1. INNER JOIN
➡ Returns only rows with matching values in both tables.
SELECT c.name, o.product
FROM Customers c
INNER JOIN Orders o
ON c.id = o.customer_id;
Result:
| name | product |
|---|---|
| John | Laptop |
| Priya | Mobile |
| John | Tablet |
2. LEFT JOIN (or LEFT OUTER JOIN)
➡ Returns all rows from left table + matching rows from right table.
➡ If no match → NULL.
SELECT c.name, o.product
FROM Customers c
LEFT JOIN Orders o
ON c.id = o.customer_id;
Result:
| name | product |
|---|---|
| John | Laptop |
| Priya | Mobile |
| John | Tablet |
| Ahmed | NULL |
3. RIGHT JOIN (or RIGHT OUTER JOIN)
➡ Returns all rows from right table + matching rows from left table.
➡ If no match → NULL.
SELECT c.name, o.product
FROM Customers c
RIGHT JOIN Orders o
ON c.id = o.customer_id;
Result:
| name | product |
|---|---|
| John | Laptop |
| Priya | Mobile |
| John | Tablet |
| NULL | Camera |
4. FULL JOIN (FULL OUTER JOIN)
➡ Returns all rows from both tables, matching where possible, else NULL.
👉 MySQL does not support FULL JOIN directly → use UNION.
SELECT c.name, o.product
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.product
FROM Customers c
RIGHT JOIN Orders o ON c.id = o.customer_id;
Result:
| name | product |
|---|---|
| John | Laptop |
| Priya | Mobile |
| John | Tablet |
| Ahmed | NULL |
| NULL | Camera |
5. CROSS JOIN
➡ Returns Cartesian product (all combinations).
SELECT c.name, o.product
FROM Customers c
CROSS JOIN Orders o;
Result: (3 customers × 4 orders = 12 rows)
| name | product |
|---|---|
| John | Laptop |
| John | Mobile |
| John | Tablet |
| John | Camera |
| Priya | Laptop |
| ... | ... |
6. SELF JOIN
➡ A table joins with itself.
Example: Find customers from the same city.
SELECT A.name AS Customer1, B.name AS Customer2, A.city
FROM Customers A, Customers B
WHERE A.city = B.city AND A.id <> B.id;
Result:
| Customer1 | Customer2 | city |
|---|---|---|
| John | Ahmed | Delhi |
✅ Summary:
- INNER JOIN → Matching only.
- LEFT JOIN → All left + matches.
- RIGHT JOIN → All right + matches.
- FULL JOIN → All rows from both.
- CROSS JOIN → All combinations.
- SELF JOIN → Table joins with itself.