Search code examples
sqlsubquerycorrelated-subquery

Interpretation of the SQL operator EXISTS in subquery?

I'm taking an online course about database. Now I understand the following query

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

which is something like

SELECT column1 FROM t1 WHERE TRUE;

but coming to the subquery with EXISTS,I am falling into confusion.

(College has (name, state) as its key.) the following query seeks the name of colleges that has other colleges in the same state. 

SELECT name,state 
FROM College C1
WHERE EXISTS (SELECT * FROM College C2 
              WHERE C1.state = C2.state and C1.name != C2.name);

The subquery will return a relation, right? since it inside a parenthesis, it is computed first.

And the returned relation actually exists due to my artifact-designed table. So the EXISTS operator is always return TRUE, the result is a full table with selected columns.

Edits: sorry for not good demostration. here is my relation

Standford CA
Berkley CA
MIT MA

the true result is 

Standford CA
Berkley CA

but I think it should be

Standford CA
Berkley CA
MIT MA

Apparently there's bug in my line of reasoning. Could you tell me where it is and what need to be corrected.

Solution

  • There is no need for a correlated subquery or anything like that in this case, I don't think. You can just join college to college where the states are the same and the colleges are not:

    SELECT c1.name,c1.state 
FROM College C1,
College C2 
WHERE C1.state = C2.state and C1.name != C2.name

    SQL Fiddle

    If you want to do some reading on correlated sub-queries, here's a good resource:

    Clicky!