Sub-Query in SQL

A subquery is a SELECT statement that is nested within another SELECT, INSERT, UPDATE, or DELETE statement, or within a SELECT clause of a CREATE VIEW statement. Subqueries are often used to retrieve data from multiple tables or to perform calculations within a SELECT statement.

Here is an example of a subquery in a SELECT statement:

CopySELECT * FROM orders WHERE order_date > ( SELECT MAX(order_date) FROM orders WHERE customer_id = 123 ); 

In this example, the subquery retrieves the maximum order date for the customer with ID 123, and the outer query selects all orders with an order date later than that maximum order date. The result of the subquery is used as a condition in the WHERE clause of the outer query.

Subqueries can also be used in other clauses, such as the FROM or GROUP BY clauses, and they can be used with various operators, such as IN, EXISTS, and ANY/SOME.

It is important to note that subqueries should be used with caution, as they can potentially be slower than joins due to the need to execute the subquery for each row being evaluated by the outer query.

Some Examples of Sub-Query in SQL

• Using a subquery with the EXISTS operator:

CopySELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); 

In this example, the subquery selects a dummy value (1) if there are any orders for the current customer in the orders table. The outer query then selects all rows from the customers table where the subquery returns at least one row (indicating that the customer has placed at least one order).

• Using a subquery with the ANY/SOME operator:

CopySELECT * FROM products WHERE price > ANY ( SELECT price FROM products WHERE category = 'books' ); 

In this example, the subquery selects the prices of all products in the 'books' category. The outer query then selects all rows from the products table where the price is greater than any of the prices returned by the subquery. This can be equivalent to using the > operator with a subquery in the WHERE clause.

• Using a correlated subquery:

CopySELECT * FROM orders o WHERE order_date > ( SELECT MAX(order_date) FROM orders WHERE customer_id = o.customer_id ); 

In this example, the subquery references a column from the outer query (o.customer_id). This is called a correlated subquery, because the subquery is correlated to the outer query and is executed once for each row of the outer query. The subquery selects the maximum order date for the current customer in the outer query, and the outer query then selects all orders with an order date later than that maximum order date.

• Using a subquery in the GROUP BY clause:

CopySELECT o.customer_id, COUNT(*) AS num_orders, ( SELECT SUM(quantity * price) FROM order_items i WHERE i.order_id = o.order_id ) AS total_amount FROM orders o GROUP BY o.customer_id; 

In this example, the subquery calculates the total amount for each order by summing the quantities and prices of the items in the order_items table. The outer query selects the customer_id, the number of orders (num_orders), and the total_amount for each customer, grouped by customer_id.

• Using a subquery in the HAVING clause:

CopySELECT o.customer_id, COUNT(*) AS num_orders, ( SELECT SUM(quantity * price) FROM order_items i WHERE i.order_id = o.order_id ) AS total_amount FROM orders o GROUP BY o.customer_id HAVING total_amount > 1000; 

This example is similar to the previous one, but the outer query includes a HAVING clause that filters the results to only include customers who have placed orders with a total amount greater than 1000.

• Using a subquery in an INSERT statement:

CopyINSERT INTO products (product_id, name, price) SELECT product_id, name, price * 1.1 FROM products WHERE category = 'books'; 

In this example, the subquery selects all products in the 'books' category and multiplies their prices by 1.1. The outer INSERT statement then inserts the resulting rows into the products table.

• Using a subquery in an UPDATE statement:

CopyUPDATE products SET price = ( SELECT AVG(price) FROM products WHERE category = 'books' ) WHERE category = 'books'; 

In this example, the subquery calculates the average price of all products in the 'books' category. The outer UPDATE statement then updates the price of all products in the 'books' category to the value returned by the subquery.

• Using a subquery in a DELETE statement:

CopyDELETE FROM products WHERE category = 'books' AND price < ( SELECT AVG(price) FROM products WHERE category = 'books' ); 

In this example, the subquery calculates the average price of all products in the 'books' category. The outer DELETE statement then deletes all products in the 'books' category that have a price lower than the value returned by the subquery.

• Using a subquery in a CREATE VIEW statement:

CopyCREATE VIEW top_customers AS SELECT c.customer_id, c.name, ( SELECT SUM(quantity * price) FROM orders o INNER JOIN order_items i ON o.order_id = i.order_id WHERE o.customer_id = c.customer_id ) AS total_spent FROM customers c WHERE total_spent > 1000; 

In this example, the subquery calculates the total amount spent by each customer by summing the quantities and prices of the items in their orders. The outer query then creates a view that selects the customer_id, name, and total_spent for all customers who have spent more than 1000.