Some databases also allow use of the EXISTS (Free web hosting music)
Some databases also allow use of the EXISTS keyword. The EXISTS keyword returns a Boolean True result if the result is positive (it exists), or False otherwise. Where the IN operator includes expressions on both sides, the EXISTS operator has an expression only on the right side of the comparison. The next query finds all authors where the author exists as a foreign key AUTHOR_ID value in the PUBLISHER table: SELECT * FROM AUTHOR WHERE EXISTS (SELECT AUTHOR_ID FROM PUBLICATION); AUTHOR_ID NAME ———- ——————– 1 Orson Scott Card 2 James Blish 3 Isaac Azimov 4 Larry Niven 5 Jerry Pournelle 6 William Shakespeare 7 Kurt Vonnegut It is often also possible to pass a cross checking or correlation value into a subquery, such as in the following case using EXISTS. The query is a slightly more complex variation on the previous one where the AUTHOR_ID value, for each record found in the AUTHOR table, is passed to the subquery, and used by the subquery, to match with a PUBLISHER record: A correlation between a calling query and a subquery is a link where variables in calling query and subquery are expected to contain the same values. The correlation link is usually a primary key to foreign key link but it doesn t have to be. SELECT * FROM AUTHOR WHERE EXISTS (SELECT AUTHOR_ID FROM PUBLICATION WHERE AUTHOR_ID = AUTHOR.AUTHOR_ID); AUTHOR_ID NAME ———- ——————– 2 James Blish 3 Isaac Azimov 4 Larry Niven 6 William Shakespeare 7 Kurt Vonnegut Sometimes a correlation can be established between the calling query and subquery, using the IN operator as well as the EXISTS operator, although this is not as common. The next query is almost identical to the previous query, except that it uses the IN operator: SELECT * FROM AUTHOR WHERE AUTHOR_ID IN (SELECT AUTHOR_ID FROM PUBLICATION WHERE AUTHOR_ID = AUTHOR.AUTHOR_ID); AUTHOR_ID NAME ———- ——————– 2 James Blish 3 Isaac Azimov 4 Larry Niven 6 William Shakespeare 7 Kurt Vonnegut 142 Chapter 5
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.