SQL Correlated Subqueries Tutorial 🎯

Welcome to the SQL Correlated Subqueries Tutorial! 🎉

In this tutorial, we'll dive deep into one of the most powerful SQL features: Correlated Subqueries. You'll learn why and how they work, and how to use them in your projects. Let's get started!

Understanding Correlated Subqueries 📝

A correlated subquery is a subquery that refers to a value from the outer query. In other words, it's a subquery that depends on the outer query's current row.

Let's visualize this with a simple example:

sql

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = 'IT');

In this example, we're using a correlated subquery to find all employees who earn more than the average salary of employees in the 'IT' department.

Correlated Subquery Syntax 💡

A correlated subquery has the following syntax:

sql

SELECT column_list
FROM table_name
WHERE column_name IN (subquery)

Correlated Subquery Example 🎯

Let's create a table and perform a correlated subquery example:

sql

-- Create a sample table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO orders (id, customer_id, order_date, total_amount)
VALUES (1, 101, '2022-01-01', 1000),
       (2, 101, '2022-02-01', 1500),
       (3, 102, '2022-01-02', 2000),
       (4, 103, '2022-02-02', 1800),
       (5, 101, '2022-03-01', 2000);

-- Find the customers who spent more than average on March
SELECT customer_id, AVG(total_amount) as avg_spend
FROM orders
WHERE order_date = '2022-03-01'

-- Find customers who spent more than their average spend in March
SELECT customer_id
FROM orders
WHERE total_amount > (SELECT avg_spend FROM (
    SELECT customer_id, AVG(total_amount) as avg_spend
    FROM orders
    WHERE order_date = '2022-03-01'
) as subquery);

In the example above, we first find the average spend for March (SELECT customer_id, AVG(total_amount) as avg_spend FROM orders WHERE order_date = '2022-03-01'), and then find the customers who spent more than their average spend in March (SELECT customer_id FROM orders WHERE total_amount > (SELECT avg_spend FROM ...)).

Quiz 📝

By the end of this tutorial, you'll be comfortable using correlated subqueries in your SQL queries. Happy learning! 🌟

Remember, practice makes perfect! Take advantage of the CodeYourCraft SQL practice area to reinforce your skills.