All 7 Subqueries questions
More in Subqueries
What is the difference between a correlated and a non correlated subquery? What is the difference between using EXISTS and IN when checking for the presence of related data with a subquery? How do you use a subquery within the SELECT list to calculate a related aggregate value alongside each row of the outer query? What are common mistakes developers make when writing subqueries that can lead to incorrect results or poor performance? How would you rewrite a slow correlated subquery as an equivalent, potentially faster JOIN based query? How does the SQL Server query optimizer typically handle and potentially transform a subquery internally when generating an execution plan?
Advertisement
SQL Server › Subqueries

What is a subquery in SQL Server, and how is it typically used within a larger query?

Beginner

Answer

A subquery is a query nested inside another query, often used within a WHERE clause to filter results based on the outcome of that inner query, within a SELECT list to calculate a related value, or within a FROM clause to treat its result as if it were a temporary table for the outer query to work with.
SELECT Name FROM Customers
WHERE CustomerId IN (SELECT CustomerId FROM Orders WHERE Amount > 1000);
Real-world example A marketing report finds all customers who have placed at least one order over a thousand dollars, using a subquery to first identify the relevant customer ids before selecting their names in the outer query.

Common follow-ups: What is the difference between a subquery in the WHERE clause versus the FROM clause?;Can a subquery return more than one column?

Joins;Common Table Expressions (CTEs)
All 7 Subqueries questions