In this lesson, you will learn how to improve query performance by minimizing the amount of data your subqueries pass to the main query.
What is Subquery Cardinality?
"Cardinality" in SQL simply refers to the number of rows a query returns. When you use a subquery—a query nested inside another—the database engine often has to build a temporary result set in memory. If your subquery returns 10,000 rows when it only needs to return 10, the database performs unnecessary work, slowing down your entire operation. Reducing cardinality means filtering that inner data as strictly as possible before the "outer" query ever sees it.
The Efficiency Gap
Imagine you want to find the names of employees who work in departments with high sales. A common (but slow) approach looks like this:
SELECT employee_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM sales_records -- This might return millions of rows!
);
If sales_records contains every single transaction from the last decade, the database engine must process that entire table just to get a list of IDs. To reduce the cardinality, we should filter the subquery to only the relevant subset:
SELECT employee_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM sales_records
WHERE sale_date > '2023-01-01' -- Drastically reduces rows processed
);
By adding a WHERE clause or a DISTINCT keyword inside the subquery, you limit the number of rows the database has to "hold" in memory. When the cardinality of the inner result is low, the database can use much faster join strategies (like a nested loop or a tiny hash join) rather than performing heavy scans of your entire dataset.
Whenever you write a subquery, ask yourself: "Can I filter these results further before the main query starts?" Every row you remove from the inner result is one less row the database engine has to manage, leading to snappy, efficient performance.
Key Takeaway: Always apply the strictest possible filters inside your subqueries to reduce the number of rows returned, ensuring your database engine performs the least amount of work necessary to retrieve your data.