Welcome back! 👋
In the last lesson, we learned the basics of JOINs:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• Joining multiple tables

In this lesson, we’ll explore more advanced JOIN techniques used in real-world SQL queries.

By the end of this lesson, you’ll be able to:

1. Use self joins
2. Join multiple tables efficiently
3. Understand and use anti joins (NOT IN, NOT EXISTS)
4. Understand and use semi joins (IN, EXISTS)
5. Solve practical business questions using these joins

Self Join

A self join is when a table is joined to itself.

Imagine a table of employees with a manager_id column:employees ------------------------- employee_id | name | manager_id 1 | Ali | NULL 2 | Sara | 1 3 | John | 1 Copy

We can find employees and their managers using a self join:SELECT e1.name AS employee, e2.name AS manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id; Copy

✅ Here, we joined the table to itself using different aliases (e1 and e2) to differentiate the two roles.

Joining Multiple Tables

We often need data from 3 or more tables.

Example: Customer orders with products (from Lesson 6)

SELECT c.full_name, o.order_id, p.product_name, oi.quantity, oi.price_per_unit FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id INNER JOIN order_items oi ON o.order_id = oi.order_id INNER JOIN products p ON oi.product_id = p.product_id; Copy

Using table aliases (c, o, oi, p) makes queries shorter and easier to read.

Anti Join – Finding Missing Data

An anti join finds rows in one table that do NOT have a match in another table.

Example 1: Products never ordered

SELECT product_name FROM products WHERE product_id NOT IN ( SELECT DISTINCT product_id FROM order_items ); Copy

✅ This returns products that don’t appear in any order.

Example 2: Using NOT EXISTS

SELECT p.product_name FROM products p WHERE NOT EXISTS ( SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id ); Copy

Both methods work; NOT EXISTS is often faster on large datasets.

Semi Join – Checking Existence

A semi join finds rows in one table that have a match in another table.

Example 1: Customers who have placed at least one order

SELECT full_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); Copy

Example 2: Using IN for the same result

SELECT full_name FROM customers WHERE customer_id IN ( SELECT DISTINCT customer_id FROM orders ); Copy

✅ Both queries return customers who have orders.

Real-World Examples

1. Find customers who never placed an order:

SELECT full_name FROM customers WHERE customer_id NOT IN ( SELECT DISTINCT customer_id FROM orders ); Copy

2. Find orders with at least one product costing more than $1000:

SELECT DISTINCT o.order_id FROM orders o INNER JOIN order_items oi ON o.order_id = oi.order_id WHERE oi.price_per_unit > 1000; Copy

3. Find products ordered by multiple customers:

SELECT product_id, COUNT(DISTINCT customer_id) AS num_customers FROM orders o INNER JOIN order_items oi ON o.order_id = oi.order_id GROUP BY product_id HAVING COUNT(DISTINCT customer_id) > 1; Copy

Practice Queries

Try these exercises on your database:

1. Find customers who never bought a Laptop.
2. List orders that include the most expensive product.
3. Find customers who placed orders only in May 2023.
4. Find products ordered by more than 1 customer.

Practicing these queries will help you solve real-life business problems using SQL.

Lesson Summary

• Self join: join a table to itself
• Multiple table joins: combine data from 3+ tables efficiently
• Anti join: find rows without a match (NOT IN, NOT EXISTS)
• Semi join: find rows that have a match (IN, EXISTS)
• These advanced joins help answer complex real-world questions