In SQL, the EXISTS keyword returns a boolean value of true if the subquery it takes as an argument returns any records. If the subquery produces nothing, then EXISTS returns a value of false. The important thing to remember is that you aren't getting a set of records back — just a boolean value.
A simple example is where you would want to retrieve a list of all nodes by noders from Louisville. You might write a query like this:
SELECT nodeid, node_title FROM nodes
WHERE EXISTS (SELECT * FROM noders
WHERE hometown = 'Louisville');
But you can do more with EXISTS, including using it with correlated subqueries, joins, and NOT. The only limits to the complexity of queries using EXISTS is are you imagination and your query engine. Here is an example of a much more complicated query with nested subqueries:
SELECT noder_id, join_date FROM noders a
(SELECT * FROM gods b
WHERE a.noder_id = b.god_name AND
1000 <= (SELECT COUNT(*)
FROM nodes WHERE
nodes.author = b.god_name);
What this query does, even if makes no sense in the real world, is select all noders that are gods (i.e., in the gods table, and that have more than 1,000 nodes. Certainly there are more succinct and elegant ways to extract the same set of records, but I hope this illustrates the power and complexity of EXISTS.
Thanks to Understanding SQL by Martin Gruber for keeping me straight on this.