LEFT, RIGHT, FULL, CROSS আর সেলফ জয়েন

SELECT c.customer_id, c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (1, 4, 8)
ORDER BY c.customer_id, o.order_id;

+-------------+--------------+----------+-----------+
| customer_id | name         | order_id | status    |
+-------------+--------------+----------+-----------+
|           1 | Nadia Rahman |        1 | delivered |
|           1 | Nadia Rahman |        3 | delivered |
|           1 | Nadia Rahman |       10 | delivered |
|           4 | Rafiq Islam  |        5 | cancelled |
|           8 | Karim Uddin  | NULL     | NULL      |
+-------------+--------------+----------+-----------+

SELECT c.customer_id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

+-------------+-------------+-------------------+
| customer_id | name        | email             |
+-------------+-------------+-------------------+
|           8 | Karim Uddin | karim@example.com |
+-------------+-------------+-------------------+

SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL
ORDER BY p.product_id;

+------------+-----------------------------+
| product_id | name                        |
+------------+-----------------------------+
|         10 | Noise-Cancelling Headphones |
|         11 | Gift Card                   |
+------------+-----------------------------+

SELECT c.name, COUNT(*) AS count_star, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name;

+-----------------+------------+--------+
| name            | count_star | orders |
+-----------------+------------+--------+
| Farhana Akter   |          3 |      3 |
| Nadia Rahman    |          3 |      3 |
| Tanvir Ahmed    |          3 |      3 |
| Imran Hossain   |          2 |      2 |
| Mitu Das        |          1 |      1 |
| Rafiq Islam     |          1 |      1 |
| Sadia Chowdhury |          1 |      1 |
| Karim Uddin     |          1 |      0 |
+-----------------+------------+--------+

SELECT c.name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

+-----------------+------------------+
| name            | delivered_orders |
+-----------------+------------------+
| Nadia Rahman    |                3 |
| Tanvir Ahmed    |                2 |
| Farhana Akter   |                3 |
| Sadia Chowdhury |                1 |
| Imran Hossain   |                1 |
| Mitu Das        |                1 |
+-----------------+------------------+

SELECT c.name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

+-----------------+------------------+
| name            | delivered_orders |
+-----------------+------------------+
| Nadia Rahman    |                3 |
| Tanvir Ahmed    |                2 |
| Farhana Akter   |                3 |
| Rafiq Islam     |                0 |
| Sadia Chowdhury |                1 |
| Imran Hossain   |                1 |
| Mitu Das        |                1 |
| Karim Uddin     |                0 |
+-----------------+------------------+

SELECT c.name AS category, p.name AS product
FROM products p
RIGHT JOIN categories c ON p.category_id = c.category_id
WHERE c.category_id IN (3, 5)
ORDER BY c.category_id, p.product_id;

+-----------+-------------------------+
| category  | product                 |
+-----------+-------------------------+
| Courses   | SQL Masterclass         |
| Courses   | AI Engineering Bootcamp |
| Furniture | NULL                    |
+-----------+-------------------------+

SELECT p.name AS product, c.name AS category
FROM products p
FULL OUTER JOIN categories c ON p.category_id = c.category_id
ORDER BY category, product;

+-----------------------------+-------------+
| product                     | category    |
+-----------------------------+-------------+
| Gift Card                   | NULL        |
| Laptop Stand                | Accessories |
| USB-C Hub                   | Accessories |
| Hands-On Machine Learning   | Books       |
| Python Crash Course         | Books       |
| AI Engineering Bootcamp     | Courses     |
| SQL Masterclass             | Courses     |
| 27-inch Monitor             | Electronics |
| Mechanical Keyboard         | Electronics |
| Noise-Cancelling Headphones | Electronics |
| Wireless Mouse              | Electronics |
| NULL                        | Furniture   |
+-----------------------------+-------------+

-- MySQL
SELECT p.name AS product, c.name AS category
FROM products p
FULL OUTER JOIN categories c ON p.category_id = c.category_id;

Error: ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN categories c ON p.category_id = c.category_id' at line 4

-- MySQL
SELECT p.name AS product, c.name AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
UNION ALL
SELECT p.name AS product, c.name AS category
FROM products p
RIGHT JOIN categories c ON p.category_id = c.category_id
WHERE p.product_id IS NULL
ORDER BY category, product;

+-----------------------------+-------------+
| product                     | category    |
+-----------------------------+-------------+
| Gift Card                   | NULL        |
| Laptop Stand                | Accessories |
| USB-C Hub                   | Accessories |
| Hands-On Machine Learning   | Books       |
| Python Crash Course         | Books       |
| AI Engineering Bootcamp     | Courses     |
| SQL Masterclass             | Courses     |
| 27-inch Monitor             | Electronics |
| Mechanical Keyboard         | Electronics |
| Noise-Cancelling Headphones | Electronics |
| Wireless Mouse              | Electronics |
| NULL                        | Furniture   |
+-----------------------------+-------------+

CREATE TABLE months (month TEXT PRIMARY KEY);
INSERT INTO months (month) VALUES ('2026-01'), ('2026-02'), ('2026-03');
SELECT m.month, c.name AS category
FROM months m
CROSS JOIN categories c
ORDER BY m.month, c.category_id
LIMIT 6;
SELECT COUNT(*) AS grid_rows FROM months CROSS JOIN categories;

+---------+-------------+
| month   | category    |
+---------+-------------+
| 2026-01 | Books       |
| 2026-01 | Electronics |
| 2026-01 | Courses     |
| 2026-01 | Accessories |
| 2026-01 | Furniture   |
| 2026-02 | Books       |
+---------+-------------+
+-----------+
| grid_rows |
+-----------+
|        15 |
+-----------+

SELECT m.month, c.name AS category, COUNT(DISTINCT o.order_id) AS orders
FROM months m
CROSS JOIN categories c
LEFT JOIN products p ON p.category_id = c.category_id
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o ON o.order_id = oi.order_id AND strftime('%Y-%m', o.order_date) = m.month
WHERE c.category_id <= 3
GROUP BY m.month, c.category_id, c.name
ORDER BY m.month, c.category_id;

+---------+-------------+--------+
| month   | category    | orders |
+---------+-------------+--------+
| 2026-01 | Books       |      1 |
| 2026-01 | Electronics |      2 |
| 2026-01 | Courses     |      0 |
| 2026-02 | Books       |      1 |
| 2026-02 | Electronics |      1 |
| 2026-02 | Courses     |      1 |
| 2026-03 | Books       |      1 |
| 2026-03 | Electronics |      1 |
| 2026-03 | Courses     |      1 |
+---------+-------------+--------+

SELECT e.name AS employee, e.department, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

+-----------------+-------------+-----------------+
| employee        | department  | manager         |
+-----------------+-------------+-----------------+
| Ayesha Siddiqua | Management  | NULL            |
| Hasan Mahmud    | Engineering | Ayesha Siddiqua |
| Nusrat Jahan    | Engineering | Hasan Mahmud    |
| Zahid Hasan     | Engineering | Hasan Mahmud    |
| Lamia Karim     | Data        | Ayesha Siddiqua |
| Shuvo Roy       | Data        | Lamia Karim     |
| Priya Sen       | Data        | Lamia Karim     |
+-----------------+-------------+-----------------+

SELECT m.name AS manager, COUNT(e.employee_id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY direct_reports DESC, m.name;

+-----------------+----------------+
| manager         | direct_reports |
+-----------------+----------------+
| Ayesha Siddiqua |              2 |
| Hasan Mahmud    |              2 |
| Lamia Karim     |              2 |
| Nusrat Jahan    |              0 |
| Priya Sen       |              0 |
| Shuvo Roy       |              0 |
| Zahid Hasan     |              0 |
+-----------------+----------------+

import pandas as pd
from sqlhelp import con

customers = pd.read_sql("SELECT customer_id, name FROM customers", con)
orders = pd.read_sql("SELECT order_id, customer_id FROM orders", con)

both = customers.merge(orders, on="customer_id", how="left", indicator=True)
print(both["_merge"].value_counts())
print(both.loc[both["_merge"] == "left_only", ["customer_id", "name"]])

_merge
both          14
left_only      1
right_only     0
Name: count, dtype: int64
    customer_id         name
14            8  Karim Uddin

-- 1
SELECT c.name AS category, COUNT(p.product_id) AS products
FROM categories c
LEFT JOIN products p ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY c.category_id;

-- 2
SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY units_sold, p.product_id
LIMIT 4;

-- 3
SELECT o.order_id, c.name, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN payments pay ON pay.order_id = o.order_id
WHERE pay.payment_id IS NULL
ORDER BY o.order_id;