sqlintersectset-intersectionset-differenceset-operations

Trying to understand "except all" in sql query

I came across this example and I don't understand what it means. 

(SELECT drinker FROM Frequents)
     EXCEPT ALL
(SELECT drinker FROM Likes);

relations: Frequents(drinker, bar), Likes(drinker, beer)

What does the ALL do in this case? How is the result different from the query below?

(SELECT drinker FROM Frequents)
     EXCEPT
(SELECT drinker FROM Likes);
Solution

  • The SQL EXCEPT operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. The EXCEPT ALL operator does not remove duplicates. For purposes of row elimination and duplicate removal, the EXCEPT operator does not distinguish between NULLs.

    EXCEPT ALL which returns all records from the first table which are not present in the second table, leaving the duplicates as is. Unfortunately, SQL Server does not support this operator.