Trying to understand "except all" in sql query

Question

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

South

Look at this explainextended.com/2010/02/10/sql-server-except-all – Skate

ALL means (ANSI SQL) "leave duplicates intact" – Skate

Answer 1

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.

Millham

Tried some queries on rextester.com. I didnt find EXCEPT ALL supported on any of MySQL, Oracle, MS SQL Server or postgres. In which database engine it exists? (Only SQL Server supports EXCEPT) – Capuano

Answer 2

The except operator returns the first table minus any overlap with the second table.

Set A = (10,11,12,10,10)

Set B = (10,10)

A except B --> (11,12)

A except all B --> (10,11,12)

except removes all occurrences of duplicate data from set A, whereas except all only removes one occurrence of duplicate data from set A for every occurrence in set B.

Cummings

Is it supposed to be (11, 12, 10)? Or is it that EXCEPT ALL removes where the rows are in the same order of (10, 10)? – Lantern

Answer 3

I've recently implemented INTERSECT ALL and EXCEPT ALL and now found there aren't many resources on that on SO.

---

Consider the example of the following data.

You can reproduce example on sqlfiddle.com, use postgres 9.3.
Be aware that most of popular databases does not support INTERSECT ALL and EXCEPT ALL. Workarounds using row_number() over () are of course possible.

create table x (V1 numeric);
create table y (V1 numeric);
insert into x values (1),(2),(2),(2),(3),(4),(4);
insert into y values (2),(3),(4),(4),(4),(5);

EXCEPT [ALL] matches on all columns from both tables, column types and order must match.

select * from x except select * from y;
| v1
----
|  1

select * from x except all select * from y;
| v1
----
| 1
| 2
| 2

In EXCEPT sql process distinct datasets, so any duplicates are automatically removed leaving only single copy of each row. This results to exclude a row based on match just one in row in second dataset.
On the other hand EXCEPT ALL process datasets taking duplicate rows quantities into account. This results to return the exact difference in duplicate rows between tables. Precisely max(0, x.N - y.N).

Another tricky operator, pretty similar to EXCEPT ALL is INTERSECT ALL, this one returns min(x.N, y.N) duplicates of each matching row.

As the project I've commited the feature is open-source I'm glad to put a link to here: github.com/Rdatatable/data.table. May be useful if you would look for the tools to benchmark against with. data.table is in-memory and mostly C implemented data processing. It is open-source around 10 years already.

Autonomic

Tried some queries on rextester.com. I didnt find EXCEPT ALL supported on any of MySQL, Oracle, MS SQL Server or postgres. In which database engine it exists? (Only SQL Server supports EXCEPT) – Capuano

@anir according to documentation except all is in postgres for more than 10 years already. – Autonomic

explanation plus data to reproduce is brilliant. surprised you haven't received more upvotes. – Interstice