SQL Subqueries, Set Operations and Views: short notes (Database Management Systems)

Revision mode

Introduction

A subquery is a SELECT inside another statement. It lets a query compare a value with a computed set ("salary greater than every salary in EE"), test whether related rows exist ("departments with no employee"), or express "for all" conditions without a division operator. Set operations combine whole query results, and views give a stored query a name so it can be used like a table.

These are the constructs where SQL's NULL handling and empty-set conventions produce the most surprising answers: NOT IN against a set containing NULL returns nothing, > ALL against an empty set is true for every row, and UNION silently removes duplicates that UNION ALL keeps. Each rule is worked on concrete tables.

Prerequisites

SQL Joins, Grouping, Aggregation and NULLs: three-valued logic, aggregates and their NULL handling.

Core concepts

Subquery predicates

Let $S$ be the set (bag) of values returned by the subquery and $x$ the outer value.

Predicate	TRUE when	FALSE when	Otherwise
`x IN S`	some $s = x$	$S$ is empty (even if $x$ is NULL), or $x \neq s$ for all $s$ with no NULL involved	UNKNOWN
`x NOT IN S`	$S$ is empty (even if $x$ is NULL), or $x \neq s$ for all $s$ with no NULL in $S$ and $x$ not NULL	some $s = x$	UNKNOWN
`x > ALL S`	$x > s$ for every $s$ ; always TRUE if $S$ is empty	some $s$ with $x \leq s$	UNKNOWN
`x > ANY S` (`SOME`)	some $s$ with $x > s$	$x \leq s$ for all $s$ ; always FALSE if $S$ is empty	UNKNOWN
`EXISTS (Q)`	$Q$ returns at least one row	$Q$ returns no row	never UNKNOWN

Identities: = ANY is IN; <> ALL is NOT IN. But <> ANY is not NOT IN: it is TRUE as soon as $x$ differs from one element.

A scalar subquery returns one column and at most one row and can be used as a value; with zero rows its value is NULL, with two or more rows it is a run-time error.

A correlated subquery refers to a column of the outer query and is (logically) re-evaluated for each outer row.

Set operations

UNION, INTERSECT and EXCEPT work on union-compatible results and remove duplicates. The ALL versions keep multiplicities: if a row occurs $m$ times in the left result and $n$ times in the right, it occurs

Operation	Multiplicity in the result
`UNION ALL`	$m + n$
`INTERSECT ALL`	$\min(m, n)$
`EXCEPT ALL`	$\max(m - n, 0)$

Views

CREATE VIEW V AS SELECT ... stores a query, not its rows (a virtual table); the DBMS may optimize how it evaluates a reference to it. A restricted sufficient simple updatable form is SELECT direct_base_columns FROM one_base_table [WHERE predicate], with no other query clauses, DISTINCT, expressions, aggregates, joins, set operations, WITH, LIMIT or OFFSET; check the DBMS rules as well. UPDATE can assign only assignable direct base columns; a computed expression is not generally assignable. DELETE support and INSERT feasibility are separate questions: INSERT additionally requires all omitted mandatory base columns to have valid defaults or allow NULL. DBMS extensions and triggers may support other views, including some joins; check the dialect. WITH CHECK OPTION requires an inserted or updated row to satisfy the view predicate.

How it works: the example tables

EMP(eid, name, dept, salary) with rows (1, Asha, CS, 70), (2, Ravi, CS, 50), (3, Meena, EE, 60), (4, Kiran, EE, 60), (5, Arun, ME, 40), (6, Anil, NULL, 55), (7, Dev, CS, NULL), and DEPT(dcode, dname) with CS, EE, ME, CE, as in SQL Joins, Grouping, Aggregation and NULLs. For set operations, R(a) holds 1, 1, 2, 3 and S(a) holds 1, 3, 3, 4.

Algorithms and pseudocode

Logical evaluation of a correlated subquery:

Text

EVAL-CORRELATED(outer query O with WHERE predicate on subquery Q)
  for each row r produced by O's FROM
      bind the outer columns of r inside Q
      S <- result of Q                    // re-evaluated per outer row
      keep r if the predicate on S is TRUE

Cost is $|O| \times \text{cost}(Q)$ as written. Optimisers decorrelate: an EXISTS becomes a semi-join, NOT EXISTS an anti-join, an IN a semi-join preserving the outer bag: inner matches do not multiply outer rows; optional deduplication is only on the matching/subquery side.

Mathematical explanation

Why NOT IN fails with a NULL. x NOT IN (s1, s2, NULL) is x <> s1 AND x <> s2 AND x <> NULL. The last conjunct is UNKNOWN, so the AND is at best UNKNOWN and never TRUE: WHERE keeps no row. NOT EXISTS has no such problem because EXISTS is two-valued.

Why > ALL over an empty set is TRUE. x > ALL S means $\forall s \in S\,(x > s)$ , and a universal statement over an empty set is vacuously true. Dually, > ANY over an empty set is $\exists s \in S\,(x > s)$ , which is false. This is also why x > ALL S is not the same as x > (SELECT MAX(...)): on an empty $S$ , MAX is NULL and the comparison is UNKNOWN; and when $S$ contains NULL, ALL can never be TRUE while MAX ignores the NULL.

Worked examples

Example 1: NOT IN with a NULL. T(v) holds 50 and NULL. SELECT name FROM EMP WHERE salary NOT IN (SELECT v FROM T) returns 0 rows. SELECT name FROM EMP e WHERE NOT EXISTS (SELECT * FROM T WHERE T.v = e.salary) returns 6 rows: everyone except Ravi (Dev is included, because no T.v equals his NULL salary).

Example 2: > ALL against > MAX. Let $S$ be SELECT salary FROM EMP WHERE salary < 60 OR salary IS NULL, that is $\{50, 40, 55, \text{NULL}\}$ .

salary > ALL S: 0 rows (the NULL makes every candidate UNKNOWN at best).
salary > (SELECT MAX(salary) ... same WHERE): MAX is 55, so Asha, Meena and Kiran: 3 rows.

Example 3: the empty set. With $S$ = SELECT salary FROM EMP WHERE dept = 'XX' (no rows): salary > ALL S keeps all 7 rows, including Dev, whose salary is NULL; salary > ANY S keeps 0 rows; salary > (SELECT MAX(salary) FROM EMP WHERE dept = 'XX') keeps 0 rows.

Example 4: <> ANY is not NOT IN. With $S = \{70, 50\}$ (the non-NULL CS salaries): salary <> ANY S keeps all 6 employees with a salary, since each differs from at least one of 70 and 50; salary NOT IN S keeps 4 (Meena, Kiran, Arun, Anil).

Example 5: correlated subquery. Employees paid more than their department's average:

Text

SELECT e.name FROM EMP e
WHERE e.salary > (SELECT AVG(e2.salary) FROM EMP e2 WHERE e2.dept = e.dept);

CS average is 60 (Dev's NULL skipped): Asha qualifies. EE average 60: neither. ME average 40: no. Anil's dept is NULL, the inner WHERE matches no row, the average is NULL and the comparison is UNKNOWN. Result: Asha only.

Example 6: second-highest distinct salary. SELECT MAX(salary) FROM EMP WHERE salary < (SELECT MAX(salary) FROM EMP) returns 60.

Example 7: IN does not multiply rows, a join does. SELECT dname FROM DEPT WHERE dcode IN (SELECT dept FROM EMP) returns 3 rows (Computer, Electrical, Mechanical). SELECT dname FROM DEPT JOIN EMP ON dcode = dept returns 6 rows, one per matching employee.

Example 8: set operations. On R = {1, 1, 2, 3} and S = {1, 3, 3, 4}:

Query	Result
`R UNION S`	1, 2, 3, 4 (4 rows)
`R UNION ALL S`	8 rows
`R INTERSECT S`	1, 3
`R EXCEPT S`	2
`R INTERSECT ALL S`	1, 3 ( $\min(2, 1) = 1$ copy of 1, $\min(1, 2) = 1$ copy of 3)
`R EXCEPT ALL S`	1, 2 ( $2 - 1 = 1$ copy of 1, one 2, no 3)

Example 9: departments with no employee. SELECT dcode FROM DEPT d WHERE NOT EXISTS (SELECT * FROM EMP e WHERE e.dept = d.dcode) returns CE. The NOT IN form WHERE dcode NOT IN (SELECT dept FROM EMP) returns nothing, because Anil's dept is NULL.

Example 10: division three ways. On Enrolled(sid, cid) and Req(cid) = $\{c_1, c_3\}$ of Tuple Relational Calculus, "students enrolled in every required course":

Text

-- (a) double NOT EXISTS
SELECT DISTINCT e.sid FROM Enrolled e
WHERE NOT EXISTS (SELECT * FROM Req q
                  WHERE NOT EXISTS (SELECT * FROM Enrolled e2
                                    WHERE e2.sid = e.sid AND e2.cid = q.cid));
-- (b) "required minus taken is empty"
SELECT DISTINCT e.sid FROM Enrolled e
WHERE NOT EXISTS (SELECT cid FROM Req
                  EXCEPT SELECT e2.cid FROM Enrolled e2 WHERE e2.sid = e.sid);
-- (c) counting
SELECT sid FROM Enrolled WHERE cid IN (SELECT cid FROM Req)
GROUP BY sid HAVING COUNT(DISTINCT cid) = (SELECT COUNT(*) FROM Req);

All three return $s_1, s_2, s_4$ .
Drop the WHERE cid IN (...) filter from (c) and it counts all of a student's courses, not the required ones: it returns $s_2$ and $s_3$ , each with exactly two courses, and $s_3$ has not taken $c_1$ .
With Req empty, (a) and (b) return all four students (nothing is required, so nothing is missing), but (c) returns no row: the IN filter removes every enrolment before grouping, so no group exists to compare with 0. The forms are equivalent only when the divisor is nonempty.

Example 11: >= ALL over group averages. "Departments with the highest average salary":

Text

SELECT dept FROM EMP GROUP BY dept
HAVING AVG(salary) >= ALL (SELECT AVG(salary) FROM EMP GROUP BY dept);

The groups are CS (60, Dev's NULL skipped), EE (60), ME (40) and the NULL-department group of Anil (55). CS and EE are at least every average, so the answer is CS, EE. Now add (8, Lila, CE, NULL): the CE group's average is NULL, so every comparison with it is UNKNOWN, no group gets TRUE, and the query returns nothing. Writing >= (SELECT MAX(a) FROM (SELECT AVG(salary) AS a FROM EMP GROUP BY dept)) still returns CS and EE, because MAX skips the NULL. This is Example 2's trap one level up: a NULL can enter the subquery result through an aggregate, even when no stored value in the compared column looks suspicious.

Example 12: top two salaries by a correlated count, and the NULL row.

Text

SELECT name FROM EMP e
WHERE 2 > (SELECT COUNT(DISTINCT e2.salary) FROM EMP e2 WHERE e2.salary > e.salary);

For each employee the subquery counts the distinct salaries above theirs: Asha 0, Meena 1, Kiran 1, Anil 2, Ravi 3, Arun 4, so Asha, Meena and Kiran qualify. But Dev's salary is NULL, e2.salary > NULL is never TRUE, the count is 0, and $2 > 0$ is TRUE: Dev is returned too. Add AND e.salary IS NOT NULL to get the intended three rows. The pattern " $N >$ number of larger values" returns rows ranked $1$ to $N$ by distinct value, ties included.

SQLite differences. SQLite does not accept ANY, SOME or ALL before a subquery; rewrite x > ALL (Q) as NOT EXISTS (SELECT * FROM (Q) WHERE NOT (x > col)) only after deciding how NULLs should behave, or with MIN and MAX when the set has no NULLs and is not empty. SQLite has UNION ALL but not INTERSECT ALL or EXCEPT ALL. SQLite views are read-only unless an INSTEAD OF trigger is defined.

Important properties and observations

NOT IN and NULL: one NULL in the subquery result makes NOT IN return no row; prefer NOT EXISTS.
Empty subquery: ALL is TRUE, ANY is FALSE, IN is FALSE, NOT IN is TRUE, a scalar subquery is NULL.
EXISTS ignores the SELECT list; SELECT *, SELECT 1 and SELECT NULL inside it behave the same.
IN versus join: IN never duplicates outer rows; a join produces one row per match.
Set operations remove duplicates by default; the ALL forms follow $m + n$ , $\min(m, n)$ , $\max(m - n, 0)$ .
A view is a stored query, recomputed on use; the restricted direct-column single-table SELECT form specified above is a sufficient simple updatable case, subject to the restrictions above; assignability, insertion feasibility and DBMS extensions are separate.

Related material. A materialised view stores its result and must be refreshed when base tables change; it trades storage and maintenance for query speed.

Common misconceptions.

"x > ALL S equals x > MAX(S)." Not when $S$ is empty or contains NULL.
"<> ANY means not in the set." It means different from at least one element.
"UNION keeps all rows." Only UNION ALL does.

GATE examination perspective

NOT IN with NULL. The classic empty-result trap; compare with NOT EXISTS.
ALL and ANY on edge cases. Empty subqueries and NULL elements.
Correlated subqueries. Evaluate the inner query once per outer row; NULL outer values match nothing under ordinary equality correlation; evaluate null-safe or IS NULL predicates as written.
Duplicates. UNION versus UNION ALL, IN versus JOIN, and multiplicities of INTERSECT ALL and EXCEPT ALL.
"For all" in SQL. Double NOT EXISTS, the SQL form of division (see Relational Algebra).

Summary

IN, NOT IN, ANY, ALL, EXISTS and scalar subqueries compare a value with a computed set.
= ANY is IN, <> ALL is NOT IN, <> ANY is neither.
A NULL in the subquery result makes NOT IN and > ALL never TRUE.
Over an empty set, ALL is TRUE and ANY is FALSE; MAX and MIN are NULL.
EXISTS is two-valued; NOT EXISTS is the NULL-safe way to write anti-joins.
UNION, INTERSECT, EXCEPT remove duplicates; ALL versions use $m + n$ , $\min(m, n)$ , $\max(m - n, 0)$ .
Correlated subqueries are logically re-evaluated per outer row.
Views are stored queries; the restricted direct-column single-table SELECT form specified above is a sufficient simple updatable case; computed columns are not generally assignable; INSERT needs valid omitted-column values; WITH CHECK OPTION guards the view condition.

Sources

A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts, 7th ed., McGraw-Hill, 2019 (2020 copyright), 3.5 Set Operations, 3.8 Nested Subqueries, 4.2 Views.
R. Elmasri, S. B. Navathe, Fundamentals of Database Systems, 7th ed., Pearson, 7.1 More Complex SQL Retrieval Queries; 7.3 Views (Virtual Tables) in SQL.
SQLite documentation, consulted 2026-11-11: "SQL Language Expressions" (https://www.sqlite.org/lang_expr.html), "The SELECT statement" (https://www.sqlite.org/lang_select.html), "CREATE VIEW" (https://www.sqlite.org/lang_createview.html).
PostgreSQL 18 documentation, CREATE VIEW: Updatable Views (DBMS-specific illustration of assignable columns and triggers), (consulted 2026-11-11).

Introduction

Prerequisites

Core concepts

Subquery predicates

Set operations

Views

How it works: the example tables

Algorithms and pseudocode

Mathematical explanation

Worked examples

Important properties and observations

GATE examination perspective

Summary

Related topics

Sources

Next steps