Trying to understand "except all" in sql query

sql intersect set-intersection set-difference set-operations
user234159 picture user234159 · Feb 10, 2015 · Viewed 17.6k times · Source

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);

Answer

Pradeep Kumar Das picture Pradeep Kumar Das · Feb 10, 2015

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.

Related questions

INTERSECT in MySQL

I have two tables, records and data. records has multiple fields (firstname, lastname, etc.). Each of these fields is a foreign key for the data table where the actual value is stored. I need to search on multiple record fields. …

Read More
Force MySQL to use two indexes on a Join

I am trying to force MySQL to use two indexes. I am joining a table and I want to utilize the cross between the two indexes. The specific term is Using intersect and here is a link to MySQL documentation: …

Read More
SQL: Syntax error with intersect?

This is my query: -- Sids of suppliers who supply a green part AND a red part (SELECT Suppliers.sid FROM Suppliers JOIN Catalog ON Catalog.sid = Suppliers.sid JOIN Parts ON Parts.pid = Catalog.pid WHERE Parts.color = "red") …

Read More