3.20

Show that, in SQL, \(<>\) ALL is identical to NOT IN.

---

Let r1 and r2 be two relations having only one attribute say k.

Let the following be called “query 1”.

SELECT k
FROM r1
WHERE k <> ALL (
    SELECT k
    FROM r2
)

Let the following be called “query 2”.

SELECT k
FROM r1
WHERE k NOT IN (
    SELECT k
    FROM r2
)

As you can see query 1 and query 2 are almost the same, except we replaced “<> ALL” in query 1 by “NOT IN” in query 2. If we can show that the two queries give the same result i.e. the same set of tuples, we have shown that \(<>\) ALL is identical to NOT IN.

Take any tuple from the result of query 1, say \(t_1\). Since \(t_1\) is in relation \(r1\) and not equal to any tuple of relation \(r2\), it is not in relation \(r2\). Therefore \(t_1\) is in the result of query 2.

Take any tuple from the result of query 2, say \(t_2\). Since \(t_2\) is in relation \(r1\) and not in relation \(r2\), it is not equal to any tuple of relation \(r2\). Therefore \(t_2\) is in the result of query 1.

Thus the result of the two queries is identical.

Which proves in general case that \(<>\) ALL is identical to NOT IN.