In SQL, a subquery is a query nested within another query. It allows you to retrieve data from one or more tables and then use that result as a part of the main query. Subqueries are enclosed within parentheses and can be used in various parts of a SELECT statement, such as the SELECT, FROM, WHERE, or HAVING clauses.

Basic Syntax of a Subquery in SQL SELECT

The basic syntax of a SELECT statement with a subquery is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table);

In this example, the subquery is (SELECT column_name FROM another_table). It retrieves the column_name from the another_table and then uses it in the WHERE clause of the main query.

Examples of SQL SELECT Statements with Subqueries

Example 1: Filtering Records

Let’s assume we have two tables, orders and customers. We want to retrieve all the orders placed by customers who are located in a specific city. We can use a subquery in the WHERE clause to achieve this:

SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'New York');

In this example, the subquery (SELECT customer_id FROM customers WHERE city = 'New York') retrieves the customer_id from the customers table for customers located in New York. The main query then returns the order_id and order_date for those customers.

Example 2: Computing Aggregate Functions

Subqueries can also be used to compute aggregate functions, such as finding the average, maximum, minimum, or sum of a specific column. Here’s an example of calculating the average order total using a subquery:

SELECT AVG(total_amount) AS average_order_total
FROM (
    SELECT SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS subquery;

In this example, the subquery (SELECT SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id) calculates the sum of order_amount for each customer and groups it by customer_id. The main query then calculates the average of the subquery result and aliases it as average_order_total.

Conclusion

Subqueries in SQL SELECT statements are powerful tools for filtering records, performing calculations, and retrieving data from multiple tables. They help make queries more flexible and efficient. By understanding the syntax and examples provided above, you are well-equipped to use subqueries effectively in your own SQL queries.

#SQL #Subquery