Nested Queries in SQL

Nested Queries

Nested queries, also known as subqueries, are a powerful feature in SQL that allow you to embed one query within another. These subqueries can be used to filter results, calculate values, or perform complex operations that would be difficult to achieve with a single query. 

The inner query (subquery) is executed first, and its result is used by the outer query. This allows you to use the output of one query as a condition or value in another query.

Syntax:

SELECT column1, column2, ...
FROM table1
WHERE column_name operator (SELECT column_name FROM table2 WHERE condition);

Example:

SELECT first_name
FROM Customers
WHERE age= (
    -- subquery
    SELECT MAX(age)
    FROM CUSTOMERS
);

Explanation:

• The subquery selects the maximum age from the Customers table.
• The outer query then selects the first_name of the customer whose age matches the maximum age returned by the subquery.

Exists Operator

The EXISTS operator is used to check whether a subquery returns any rows. It returns TRUE if the subquery produces one or more rows and FALSE if no rows are produced. The EXISTS operator is often used in correlated subqueries where the inner query depends on the outer query.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

Example:

SELECT customer_id, first_name
FROM Customers
WHERE EXISTS (
  SELECT order_id
  FROM Orders
  WHERE Orders.customer_id = Customers.customer_id
);

Explanation:

• The subquery checks if there are any orders associated with each customer.
• The outer query returns the customer_id and first_name of customers who have placed at least one order.

Any and All Operators

The ANY and ALL operators are used in combination with subqueries to compare a value with a set of values returned by the subquery.

ANY Operator:

The ANY operator returns TRUE if the comparison is true for at least one value returned by the subquery.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name operator ANY (subquery);

Example:

SELECT *
FROM Teachers
WHERE age = ANY (
  SELECT age
  FROM Students
);

ALL Operator:

The ALL operator returns TRUE if the comparison is true for all values returned by the subquery.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name operator ALL (subquery);

Example:

SELECT * 
FROM Teachers
WHERE age >  ALL (
  SELECT age
  FROM Students
);