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.