Advanced Subqueries (Correlated, EXISTS)

Difficulty: Advanced
Generated on: 2025-07-10 02:30:09
Category: SQL Cheatsheet for Database Development

---

Advanced Subqueries: Correlated & EXISTS - SQL Cheatsheet

1. Quick Overview

Correlated Subquery: A subquery that refers to a column from a table in the outer query. It’s executed once for each row returned by the outer query. This makes them slower than simple subqueries, but they are essential for certain tasks, like row-by-row comparisons.

EXISTS Subquery: A subquery that checks for the existence of rows that satisfy a certain condition. It returns TRUE if the subquery returns at least one row, and FALSE otherwise. EXISTS is often more performant than using IN or aggregate functions within a subquery, especially with large datasets. It stops processing when a match is found.

When to Use:

• Correlated: When you need to compare data within a subquery to data from the outer query row-by-row. Examples include finding employees earning more than their department average or finding customers who have placed orders on multiple days.
• EXISTS: When you only need to know if any rows meet a condition. Examples include finding customers who have placed orders or finding products that are not in a specific category.

2. Syntax

Correlated Subquery:

SELECT column1, column2
FROM outer_table
WHERE column3 > (SELECT AVG(column3)
                 FROM inner_table
                 WHERE inner_table.foreign_key = outer_table.primary_key); -- Correlation!

EXISTS Subquery:

SELECT column1, column2
FROM outer_table
WHERE EXISTS (SELECT 1 -- or any column, it's ignored
              FROM inner_table
              WHERE inner_table.foreign_key = outer_table.primary_key
                AND some_condition);

3. Common Use Cases

• Correlated Subquery:
  • Finding customers who have placed more orders than the average customer.
  • Finding employees who earn more than the average salary in their department.
  • Identifying products that are priced higher than the average price of products in the same category.
  • Calculating a running total.
• EXISTS Subquery:
  • Finding customers who have placed any orders.
  • Finding departments that have any employees.
  • Finding products that are not in a specific promotion.
  • Checking for data integrity (e.g., ensuring that all foreign keys have corresponding primary keys).

4. Best Practices

• Correlated Subquery Performance:
  • Minimize Outer Query Rows: Reduce the number of rows processed by the outer query as much as possible through filtering.
  • Indexes: Ensure that the correlated column (e.g., inner_table.foreign_key and outer_table.primary_key in the examples above) is properly indexed.
  • Rewriting: Consider rewriting the query using JOINs if performance is critical and the logic allows. Sometimes, a JOIN and aggregation can be more efficient.
• EXISTS Subquery Performance:
  • Indexes: Index the columns used in the WHERE clause of the EXISTS subquery.
  • SELECT 1: Use SELECT 1 (or any constant value) in the EXISTS subquery. The database only needs to find any row that satisfies the condition, not the actual data in the row.
  • Avoid unnecessary DISTINCT: The purpose of EXISTS is to check for the existence of rows, so DISTINCT is generally not needed and can hinder performance.
  • Use NOT EXISTS instead of NOT IN with NULLs: NOT IN can behave unexpectedly when the subquery returns NULL values. NOT EXISTS is generally safer and more predictable.
• General Subquery Best Practices:
  • Clarity: Write subqueries that are easy to understand. Use proper indentation and comments.
  • Testing: Test your subqueries thoroughly with various data sets to ensure they produce the correct results.
  • Explain Plans: Use your database’s “explain plan” feature to analyze the query execution plan and identify potential performance bottlenecks.

5. Examples

Sample Data:

Customers Table:

CustomerID	CustomerName	City
1	John Doe	New York
2	Jane Smith	Los Angeles
3	David Lee	Chicago
4	Emily Wong	New York

Orders Table:

OrderID	CustomerID	OrderDate	Amount
101	1	2023-01-15	100
102	2	2023-02-20	150
103	1	2023-03-10	200
104	3	2023-04-05	50
105	2	2023-05-12	120

Example 1: Correlated Subquery - Customers with orders greater than their average order amount

SELECT c.CustomerID, c.CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    GROUP BY o.CustomerID
    HAVING SUM(o.Amount) > (SELECT AVG(Amount) FROM Orders)  -- compare customer total amount to all orders avg
);

-- Expected Output:
-- | CustomerID | CustomerName |
-- | :--------- | :------------- |
-- | 1          | John Doe      |
-- | 2          | Jane Smith    |

Example 2: EXISTS Subquery - Customers who have placed orders

SELECT c.CustomerID, c.CustomerName
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- Expected Output:
-- | CustomerID | CustomerName |
-- | :--------- | :------------- |
-- | 1          | John Doe      |
-- | 2          | Jane Smith    |
-- | 3          | David Lee     |

Example 3: NOT EXISTS Subquery - Customers who have not placed orders

SELECT c.CustomerID, c.CustomerName
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- Expected Output:
-- | CustomerID | CustomerName |
-- | :--------- | :------------- |
-- | 4          | Emily Wong    |

Example 4: Correlated Subquery - Customers who have placed an order on the same day as another customer

SELECT c.CustomerID, c.CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o1
    WHERE o1.CustomerID != c.CustomerID -- Ensure it's a *different* customer
      AND EXISTS (
          SELECT 1
          FROM Orders o2
          WHERE o2.CustomerID = c.CustomerID
            AND o2.OrderDate = o1.OrderDate  -- Same date
      )
);

-- This is a more complex example, and the expected output depends on more data in the Orders table.
-- It demonstrates how correlated subqueries can be nested.  Requires more data to show a concrete result.

Example 5: Correlated Subquery with aggregation - Find products whose price is higher than the average price of products in the same category.

Let’s assume we have Products and Categories tables.

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(255)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    CategoryID INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

INSERT INTO Categories (CategoryID, CategoryName) VALUES
(1, 'Electronics'),
(2, 'Clothing');

INSERT INTO Products (ProductID, ProductName, CategoryID, Price) VALUES
(101, 'Laptop', 1, 1200.00),
(102, 'Smartphone', 1, 800.00),
(103, 'T-Shirt', 2, 25.00),
(104, 'Jeans', 2, 75.00);

SELECT p.ProductID, p.ProductName, p.Price
FROM Products p
WHERE p.Price > (
    SELECT AVG(Price)
    FROM Products p2
    WHERE p2.CategoryID = p.CategoryID -- Correlated by CategoryID
);

-- Expected Output:
-- | ProductID | ProductName | Price   |
-- | :-------- | :---------- | :------ |
-- | 101       | Laptop      | 1200.00 |
-- | 104       | Jeans       | 75.00   |

6. Common Pitfalls

• Performance: Correlated subqueries can be slow, especially on large tables. Profile your queries and consider alternative approaches like JOINs.
• NULL Values: Be careful when using NOT IN with subqueries that might return NULL values. Use NOT EXISTS instead for more predictable behavior.
• Scope: Understand the scope of variables within nested subqueries. Ensure that you are referencing the correct tables and columns.
• Ambiguous Column Names: If a column name exists in both the outer and inner queries, qualify the column name with the table alias (e.g., outer_table.column_name).
• Incorrect Correlation: Double-check that the correlation condition (e.g., inner_table.foreign_key = outer_table.primary_key) is correct and accurately links the tables. A wrong correlation will produce incorrect results.

Troubleshooting:

• Slow Queries: Use your database’s “explain plan” feature to identify bottlenecks. Add indexes, rewrite the query, or consider using temporary tables.
• Incorrect Results: Simplify the query and test each part separately. Print out intermediate results to understand what’s happening.
• Error Messages: Carefully read the error messages. They often provide valuable clues about the problem.

7. Database Variations

While the core concepts of correlated and EXISTS subqueries are the same across most SQL databases, there might be slight syntax or performance differences:

• MySQL: MySQL’s query optimizer can sometimes struggle with complex correlated subqueries. Consider rewriting them as JOINs or using derived tables.
• PostgreSQL: PostgreSQL generally handles correlated subqueries well. Its optimizer is quite sophisticated.
• SQL Server: SQL Server’s optimizer is generally good, but indexing is crucial for performance. Consider using WITH (NOLOCK) hint on tables if you are experiencing locking issues (understand the implications before using this).
• Oracle: Oracle’s optimizer is powerful, but it’s important to understand the execution plan. Use AUTOTRACE to analyze query performance. Also, Oracle may require table aliases even when they seem unnecessary to avoid “ORA-00918: column ambiguously defined” errors.

Specific Database Notes:

Feature	MySQL	PostgreSQL	SQL Server	Oracle
Optimizer	Can be less efficient with correlated subqueries	Generally very efficient	Generally efficient	Powerful optimizer
EXPLAIN Tool	EXPLAIN	EXPLAIN	SET SHOWPLAN_ALL ON then execute query	EXPLAIN PLAN FOR then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Locking Hints	N/A	N/A	WITH (NOLOCK)	N/A
CONNECT BY Syntax	N/A (Use recursive CTEs)	Recursive CTEs are preferred	Recursive CTEs are preferred	CONNECT BY for hierarchical queries

This cheat sheet provides a solid foundation for understanding and using advanced subqueries in SQL. Remember to always test your queries thoroughly and optimize them for performance. Good luck!