Database Reference
In-Depth Information
This example modifies the previous one such that a correlated value
is passed from the calling query into the subquery. This effectively cre-
ates a link or correlation between calling query and subquery.
SELECT A.NAME FROM ARTIST A WHERE EXISTS
(SELECT GUESTARTIST_ID FROM GUESTAPPEARANCE
WHERE GUESTARTIST_ID = A.ARTIST_ID);
Note:
Queries using subqueries are sometimes called semi-joins when per-
forming the same or a similar function as a join.
BETWEEN validates an expression being between (inclusive of ) two
values, such that the first value should be less than the second.
BETWEEN allows only single-row returning subqueries.
expression BETWEEN expression AND expression
SELECT NAME FROM ARTIST
WHERE ARTIST_ID BETWEEN 1 AND 10;
The next example would produce no result because there is noth-
ing between 1 and 10 when starting the count at 10.
SELECT NAME FROM ARTIST
WHERE ARTIST_ID BETWEEN 10 AND 1;
Correct this by rewriting the statement as follows:
SELECT NAME FROM ARTIST
WHERE ARTIST_ID BETWEEN 1 AND 10;
ANY, SOME, and ALL check set membership and allow subqueries
that return multiple rows. ANY checks for membership of any ele-
ment, and SOME looks for some elements. ANY and SOME are
identical. ALL only returns a result if all elements in both expressions
match; the two sets must be equal in size and content.
expression [ = | != | > | < | >= | <= ]
[ ANY | SOME | ALL ] expression
