Sql Lectures

Subqueries
Subqueries are an identifying feature of SQL. It is called Structured Query Language because a query can nest inside another query.
There are 3 basic types of subqueries in SQL:
  * Predicate Subqueries -- extended logical constructs in the WHERE (and HAVING) clause.
  * Scalar Subqueries -- standalone queries that return a single value; they can be used anywhere a scalar value is used.
  * Table Subqueries -- queries nested in the FROM clause.
All subqueries must be enclosed in parentheses.
Predicate Subqueries
Predicate subqueries are used in the WHERE (and HAVING) clause. Each is a special logical construct. Except for EXISTS, predicate subqueries must retrieve one column (in their select list.)
  * IN Subquery
The IN Subquery tests whether a scalar value matches the single query column value in any subquery result row. It has the following general format:
value-1 [NOT] IN (query-1)
Using NOT is equivalent to:
NOT value-1 IN (query-1)
For example, to list parts that have suppliers:
SELECT *
FROM p
WHERE pno IN (SELECT pno FROM sp)
pno | descr | color |
P1 | Widget | Blue |
P2 | Widget | Red |
The Self Join example in the previous subsection can be expressed with an IN Subquery:
SELECT DISTINCT pno
FROM sp a
WHERE pno IN (SELECT pno FROM sp b WHERE a.sno <> b.sno)
pno |
P1 |
Note that the subquery where clause references a column in the outer query (a.sno). This is known as an outer reference. Subqueries with outer references are sometimes known as correlated subqueries.
  * Quantified Subqueries
A quantified subquery allows several types of tests and can use the full set of comparison operators. It has the following general format:
value-1 {=|>|<|>=|<=|<>} {ANY|ALL|SOME} (query-1)
The comparison operator specifies how to...