Subqueries that return columns
When a subquery returns a column, can be used to make comparisons across operators ANY, SOME, and ALL IN:
Code:
SELECT s1 FROM t1 WHERE s1> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
The first query does "select s1 from t1 values that are greater than at least one of t2 values on s1. The second one selects the values of s1 that is equal to at least one of t2 values on s1. "IN" stands for "= ANY". "SOME" is equivalent instead in all respects to "ANY".
Code:
SELECT s1 FROM t1 WHERE s1> ALL (SELECT s1 FROM t2);
The meaning here is "select s1 from t1 values that are greater than all the values of s1 on t2. The clause "NOT IN" is equivalent to <> ALL".
Subqueries that return rows
When a subquery returns a single row, it can be used to make comparisons across manufacturers lines:
Code:
SELECT Column1, column2 FROM t1
WHERE (Column1, column2) IN
(SELECT Column1, column2 FROM t2);
This query extracts the rows of t1 in which the values of Column1 and Column2 are repeated in a row in t2. The expression "(Column1, column2)" is, in fact, a row constructor, which could be expressed as "ROW (Column1, column2).
Bookmarks