A subquery is a query nested inside another SQL query, used to retrieve data that will be used by the outer query to filter or compute results. It executes first and returns values that the main query can use, often appearing in the WHERE, FROM, or SELECT clauses. Subqueries are useful when filtering based on aggregated data, comparing against values from another table, or avoiding joins for simpler logic. Common types include scalar (returning a single value), row (returning one row with multiple columns), table (returning multiple rows and columns), and correlated subqueries (depending on the outer query for their values). For example, finding employees earning more than the average salary uses a scalar subquery: SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); Correlated subqueries, like finding orders greater than a customer’s average, run once per outer row: SELECT o1.order_id, o1.total FROM orders o1 WHERE o1.total > (SELECT AVG(o2.total) FROM orders o2 WHERE o2.customer_id = o1.customer_id). Best practices include keeping subqueries simple, using aliases for clarity, testing subqueries independently, and being mindful of performance, especially with correlated subqueries on large datasets. Different databases may optimize or restrict subqueries differently, so checking documentation is key.

SQL subqueries are one of those tools that might seem intimidating at first, but once you get the hang of them, they become incredibly useful for pulling precise data from your database. They let you query within a query, which opens up a lot of flexibility when dealing with complex data relationships.

What Exactly Is a Subquery?

A subquery is basically a query inside another query. It runs first and returns results that the outer query can use. You’ll often see them in places like the WHERE clause, FROM clause, or even in SELECT statements.

For example, if you want to find all customers who placed an order after a certain date, you could write a subquery that gets those order IDs first, then use them in the outer query to filter customers.

Here’s a basic structure:

SELECT name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date > '2023-01-01'
);

This is a common pattern: use a subquery to get a list of matching IDs, then use that list in the main query.

When Should You Use a Subquery?

You’ll typically reach for a subquery when you need to filter data based on something that isn’t directly available in the current table. This often happens when you need to compare against aggregated data or look up related values from another table.

• You need to filter based on the result of an aggregate function.
• You want to compare against a value from another table that’s not joined directly.
• You're trying to avoid joins because the logic is simpler with a subquery.

One common use case is finding employees whose salary is above the average:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Here, the inner query calculates the average salary, and the outer query filters employees who earn more than that.

Types of Subqueries You Should Know

There are a few types of subqueries you’ll run into, and knowing the difference helps you use them correctly:

• Scalar subqueries: Return a single value. These are often used in comparisons.
• Row subqueries: Return one row with multiple columns. Useful when comparing multiple fields at once.
• Table subqueries: Return multiple rows and columns. Often used in the FROM clause.
• Correlated subqueries: Depend on the outer query for their values. These run once for each row processed by the outer query, so they can be slower.

Let’s say you want to find all orders where the order total is greater than the average for that customer. That’s a correlated subquery:

SELECT o1.order_id, o1.total
FROM orders o1
WHERE o1.total > (
    SELECT AVG(o2.total)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Notice how the inner query uses a value from the outer query (o1.customer_id). That’s what makes it correlated.

Tips for Writing and Optimizing Subqueries

Subqueries can get messy if not handled carefully. Here are a few practical tips:

• Keep them simple. If a subquery starts getting too nested or complex, consider using a JOIN instead.
• Use aliases for clarity, especially when working with multiple layers of queries.
• Test the subquery on its own before plugging it into the main query.
• Watch out for performance. Correlated subqueries can be slow on large datasets.

Also, remember that some databases handle subqueries differently. MySQL, PostgreSQL, and SQL Server might optimize or restrict them in various ways, so always check your DB’s documentation.

That’s the core of working with subqueries — they’re flexible but need to be used thoughtfully. Once you’re comfortable with the basics, they become a go-to tool for more precise and dynamic querying.

The above is the detailed content of Understanding SQL Subqueries: A Comprehensive Guide. For more information, please follow other related articles on the PHP Chinese website!