SQL Complex Queries

🎭 SQL Complex Queries: The Restaurant Story

Imagine you’re running a magical restaurant where different recipe books need to talk to each other. That’s exactly what SQL complex queries do — they help different tables “meet” and share information!

🤝 INNER JOIN: The Perfect Match

What Is It?

Think of INNER JOIN like a friendship bracelet swap. Only kids who BOTH have bracelets AND want to trade get to swap!

Simple Example:

• Table A has: 🍕 Pizza lovers (Tom, Sara, Mike)
• Table B has: 🎂 Cake lovers (Sara, Mike, Lucy)
• INNER JOIN gives you: Sara and Mike (they love BOTH!)

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

What happens?

• Only customers who placed orders appear
• No lonely customers, no orphan orders
• Perfect matches only! ✨

graph TD
    A["👥 Customers Table"] --> C{INNER JOIN}
    B["📦 Orders Table"] --> C
    C --> D["✅ Only Matching Records"]

Real Life Example

You want to see which students bought lunch today:

SELECT students.name,
       lunch.meal
FROM students
INNER JOIN lunch
  ON students.id = lunch.student_id;

Result: Only students who bought lunch appear!

⬅️➡️ LEFT and RIGHT JOIN: Including the Lonely Ones

LEFT JOIN: Keep Everyone from the Left

Imagine a classroom photo. LEFT JOIN says: “Everyone from Class A gets in the photo, even if they don’t have a buddy from Class B!”

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

What happens?

• ALL customers appear (even those without orders)
• Customers without orders show NULL for product
• Nobody from the left table is left behind! 🎯

RIGHT JOIN: Keep Everyone from the Right

Same idea, but flipped! Everyone from the RIGHT table stays, even without a match.

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

graph LR
    A["LEFT JOIN"] --> B["All from LEFT table"]
    A --> C["Matches from RIGHT"]
    A --> D["NULL if no match"]
    E["RIGHT JOIN"] --> F["All from RIGHT table"]
    E --> G["Matches from LEFT"]
    E --> H["NULL if no match"]

Quick Memory Trick 🧠

• LEFT = “I promise everyone on my LEFT gets included”
• RIGHT = “I promise everyone on my RIGHT gets included”

🎁 Subqueries and Nested Queries: A Box Inside a Box

What Is It?

A subquery is like a Russian nesting doll 🪆 — there’s a small query hiding inside a bigger query!

Simple Example: You want to find students who scored ABOVE the class average. But wait… you need to calculate the average first!

SELECT name, score
FROM students
WHERE score > (
  SELECT AVG(score)
  FROM students
);

What happens?

1. First, the inner query finds the average
2. Then, the outer query uses that number
3. Magic happens! ✨

Types of Subqueries

1. Single Value (Scalar) Subquery:

-- Returns ONE number
SELECT name
FROM products
WHERE price = (
  SELECT MAX(price)
  FROM products
);

2. List Subquery:

-- Returns multiple values
SELECT name
FROM customers
WHERE city IN (
  SELECT city
  FROM stores
);

3. Table Subquery:

-- Returns a whole mini-table
SELECT *
FROM (
  SELECT name, score
  FROM students
  ORDER BY score DESC
  LIMIT 5
) AS top_students;

graph TD
    A["🔍 Outer Query"] --> B["📦 Inner Query"]
    B --> C["Result"]
    C --> A
    A --> D["Final Answer!"]

📝 Common Table Expressions (CTEs): Give Your Query a Nickname

What Is It?

CTEs are like giving your helper query a name tag. Instead of hiding it inside, you put it at the TOP with a friendly name!

Without CTE (Messy):

SELECT * FROM (
  SELECT * FROM (
    SELECT name FROM students
  ) AS inner1
) AS inner2;

With CTE (Clean & Beautiful):

WITH top_students AS (
  SELECT name, score
  FROM students
  WHERE score > 90
)
SELECT * FROM top_students;

Why Use CTEs?

1. Readable — Easy to understand
2. Reusable — Use the same CTE multiple times
3. Organized — Clean code = happy developer!

Multiple CTEs Example

WITH
  high_scores AS (
    SELECT * FROM students
    WHERE score > 90
  ),
  low_scores AS (
    SELECT * FROM students
    WHERE score < 60
  )
SELECT 'High' AS type, name
FROM high_scores
UNION ALL
SELECT 'Low' AS type, name
FROM low_scores;

graph TD
    A["WITH clause"] --> B["CTE 1: high_scores"]
    A --> C["CTE 2: low_scores"]
    B --> D["Main Query"]
    C --> D
    D --> E["Final Result"]

🔗 UNION Operations: Stacking Results Together

What Is It?

UNION is like stacking LEGO blocks. You take results from two queries and put them on top of each other!

UNION vs UNION ALL

UNION (No Duplicates):

SELECT name FROM students_A
UNION
SELECT name FROM students_B;

• Removes duplicate names
• Like a guest list with no repeats!

UNION ALL (Keep Everything):

SELECT name FROM students_A
UNION ALL
SELECT name FROM students_B;

• Keeps ALL rows, even duplicates
• Faster because it doesn’t check for duplicates!

Rules for UNION 📋

1. Both queries must have same number of columns
2. Columns should have matching types
3. Column names come from the first query

-- ✅ This works
SELECT name, age FROM teachers
UNION
SELECT name, age FROM students;

-- ❌ This breaks
SELECT name FROM teachers
UNION
SELECT name, age FROM students;

graph TD
    A["Query 1 Results"] --> C["UNION"]
    B["Query 2 Results"] --> C
    C --> D["Combined Stack!"]

🎬 SQL Execution Order: The Secret Recipe

The BIG Secret! 🤫

SQL doesn’t run in the order you WRITE it. It has its own secret order!

You Write This:

SELECT name, COUNT(*)
FROM orders
WHERE status = 'shipped'
GROUP BY name
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC
LIMIT 10;

SQL Reads It Like This:

1. FROM    → "Which table am I looking at?"
2. WHERE   → "Filter rows first"
3. GROUP BY → "Group the survivors"
4. HAVING  → "Filter the groups"
5. SELECT  → "Pick the columns"
6. ORDER BY → "Sort the results"
7. LIMIT   → "How many to show?"

graph TD
    A["1️⃣ FROM - Pick Table"] --> B["2️⃣ WHERE - Filter Rows"]
    B --> C["3️⃣ GROUP BY - Make Groups"]
    C --> D["4️⃣ HAVING - Filter Groups"]
    D --> E["5️⃣ SELECT - Pick Columns"]
    E --> F["6️⃣ ORDER BY - Sort"]
    F --> G["7️⃣ LIMIT - Cut Off"]

Why Does This Matter? 🤔

Example 1: Column Aliases

-- ❌ This FAILS
SELECT name AS customer_name
FROM orders
WHERE customer_name = 'Tom';
-- WHERE runs BEFORE SELECT!

-- ✅ This WORKS
SELECT name AS customer_name
FROM orders
WHERE name = 'Tom';

Example 2: Aggregate Functions

-- ❌ This FAILS
SELECT category, COUNT(*) AS total
FROM products
WHERE total > 5;
-- Can't use 'total' in WHERE!

-- ✅ This WORKS
SELECT category, COUNT(*) AS total
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

Memory Trick 🧠

“From Where Groups Have Selected, Ordered, Limited”

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY
• LIMIT

🎯 Putting It All Together

Here’s a real-world query using EVERYTHING we learned:

WITH monthly_sales AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY customer_id
  HAVING SUM(amount) > 100
)
SELECT
  c.name,
  ms.total
FROM customers c
INNER JOIN monthly_sales ms
  ON c.id = ms.customer_id
ORDER BY ms.total DESC
LIMIT 10;

What This Does:

1. CTE calculates each customer’s 2024 spending
2. Filters to customers who spent over $100
3. JOINs with customer names
4. Shows top 10 spenders!

🌟 Key Takeaways

You did it! 🎉 You now understand how SQL’s most powerful queries work. These are the tools that data analysts use every single day to unlock insights from data!