SQL Subquery

SQL allows subquerying, that is, nesting queries inside a query. To add more functionality, there are certain keywords that represent logical quantifiers (in formal logic) and certain set operators.

They can be divided into two categories:

• Predicate keywords: These keywords do not require relational comparators
• Comparator keywords: These keywords require relational comparators

Relational Comparators §

Predicate Keywords §

IN §

• Is a predicate keywords
• Equivalent to $\in$ (is an element of) operator in mathematical sets
• Checks if the given attribute is in the list returned by the subquery
• Returns a boolean, TRUE or FALSE

--Returns TRUE if column_name is in the subquery
[column_name] IN [SELECT [column_name] ...] 

--Used with WHERE to filter output
SELECT
*
FROM [table_name]
WHERE [column_name]
IN [Subquery | Set]

Using joins for faster performance

In most cases, the natural join can be used instead of IN for faster performance IF A SUBQUERY IS GIVEN after IN

NOT IN §

• Similarly, the logical NOT can be used to get NOT IN
• Equivalent to $\notin$
• Returns TRUE if the attribute is not in the subquery, else FALSE

EXISTS §

• Is a predicate keyword
• Boolean operator which returns TRUE if the the subquery returns at least one record, else FALSE
• Can be used to check if the subquery is empty or not
• Useful with HAVING to return attributes that exist in the subquery

SELECT
*
FROM [table_name]
WHERE [column_name]
EXISTS [Subquery | Set]

Comparator Keywords §

Behave similar to the logical quantifiers, in the sense that they apply a given statement to every element in a set (which can be from a subquery).

ANY §

• Equivalent to the $\exists$ existential quantifier (there exists)
• Returns TRUE if the attribute satisfies any of the statements created using the relational comparator and the subquery. Only FALSE if none of the statements are satisfied.

SELECT 
*
FROM [table_name]
WHERE [column_name] 
[= | < | > | >= | <= | !=] ANY [Subqeury | Set]

ALL §

• Equivalent to the $\forall$ universal quantifier (for all)
• Returns TRUE if the attribute satisfies all of the statements created using the relational comparator and the subquery. FALSE if any of the statements are not satisfied.

SELECT
*
FROM [table_name]
WHERE [column_name]
[= | < | > | >= | <= | !=] ALL [Subqeury | Set]