CODEWITHSUNDEEP
sqlexcept
sbenderli
sbenderli

Reputation: 3794

Difference between "SELECT *" and "Select Table.Attribute" when using "EXCEPT"

I am trying to find Professors who have taught all courses. When I execute the following, I get the correct answer.

select P1.pid
from Professors P1
where NOT EXISTS(select C2.cid
                from Teaches T2, Courses C2
                where T2.cid = C2.cid
                EXCEPT
                select C3.cid
                from Teaches T3, Courses C3
                where T3.cid = C3.cid AND T3.pid = P1.pid)

However, when I execute this, I get a null set:

select P1.pid
from Professors P1
where NOT EXISTS(select *
                from Teaches T2, Courses C2
                where T2.cid = C2.cid
                EXCEPT
                select *
                from Teaches T3, Courses C3
                where T3.cid = C3.cid AND T3.pid = P1.pid)

I didn't expect these two queries to be different. Why does the second one return nothing?

Upvotes: 2

Views: 217

Answers (2)

zerkms
zerkms

Reputation: 254944

Because EXCEPT compares rows (and returns distinct ones from the first operand).

Obviously in the second case the result of nested query is different, so you get nothing

Upvotes: 1

gbn
gbn

Reputation: 432271

Because the entire subquery gives different results.

... EXCEPT SELECT * ... will be different to ... EXCEPT SELECT C3.cid .... That is, the combinations of Teaches T3, Courses C3 is different in the EXCEPT to a list of values for C3.cid.

Upvotes: 1

Related Questions