3.1. Understanding Subqueries in SQL

SQL adheres to syntax rules like any programming language. The syntax governing the use of subqueries in WHERE clauses in SQL is simple and depends on the following concepts:

Query Predicates and Predicate Operators

A predicate in SQL is a condition that evaluates to a Boolean value. For example, the predicate in the preceeding example returns true for a row of the transfer_payments table if at least one row exists in the us_census table with the same year as the transfer_payments row. The predicate starts with the first WHERE keyword.

... WHERE transfer_payments.year IN (SELECT year FROM us_census);

A SQL predicate in a subquery must also contain a predicate operator. Predicate operators specify the relationship tested in a predicate query. For example, the predicate operator in the example is the EXISTS keyword.

Aggregated and Correlated Queries

Aggregated queries combine one or more aggregate functions, such as AVG, SUM, and MAX, with the GROUP BY statement to group query results by one or more table columns. In the following example, the AVG aggregate function returns the average salary of all employees in the engineering department grouped by year.

SELECT year, AVG(salary) FROM Employees WHERE department = 'engineering' GROUP BY year;

	Note
	The GROUP BY statement may be either explicit or implicit.

Correlated queries contain a query predicate with the Equals To (=) operator. One side of the operator must reference at least one column from the parent query and the other side must reference at least one column from the subquery. The following query is a revised and correlated version of the query at the beginning of this section. It is correlated query because one side of Equals To predicate operator in the subquery references the state column in the transfer_payments table in the parent query and the other side of the operator references the state column in the us_census table.

SELECT state, net_payments FROM transfer_payments WHERE EXISTS (SELECT year FROM us_census WHERE transfer_payments.state = us_census.state);

In contrast, an uncorrelated query does not reference any columns in the parent query.

Conjuncts and Disjuncts

A conjunct is equivalent to the AND condition, while a disjunct is the equivalent of the OR condition. The following subquery contains a conjunct:

... WHERE transfer_payments.year = "2010" AND us_census.state = "california"

The following subquery contains a disjunct:

... WHERE transfer_payments.year = "2010" OR us_census.state = "california"