CODEWITHSUNDEEP
sql
Lukas po
Lukas po

Reputation: 34

It is EXISTS function somehow confusing?

I have 2 tables

This query:

SELECT * 
FROM t1 
WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.col >= 2)

returns both rows from t1, and this is ok, because EXISTS returns at least one row, so where clause becomes TRUE.

Now the second query

SELECT * 
FROM t1 
WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.col = t2.col)

This returns only 1 row. Why? in this case EXISTS also returns at least one row, so why not all rows returned from "t1" table?

How does the EXISTS() function really work ?

Upvotes: 0

Views: 83

Answers (2)

Aman
Aman

Reputation: 163

EXISTS returns true if it finds one or more records. Since you have column t1 with values 1 and 2 and t2 with value 1.

This 

SELECT * 
FROM t1 
WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.col = t2.col)

now means 

SELECT * 
FROM t1 
WHERE EXISTS (SELECT 1 FROM t2 WHERE 1 = 1)

So you get only 1 row and not 2.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

In your first query, there is no reference to the outer table inside your exists(), and since that query evaluates to true your query becomes 

select * from t1 where 1 = 1 ;

Your second query does have a reference to the outer table inside your exists() and is evaluated for each values of t1.col. So for the row where t1.col = 1 your exists() returns true, when t1.col = 2 then your exists() returns false. So your result set only includes the row where t1.col = 1.

Upvotes: 2

Related Questions