Geek Answers Handbook

Trying to understand "except everything" in sql query

I came across this example and I do not understand what this means.

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

Relationships: Frequent (drinkers, bars), Likes (drinkers, beer)

What does EVERYTHING do in this case? How is the result different from the request below?

(SELECT drinker FROM Frequents)
     EXCEPT
(SELECT drinker FROM Likes);
+4
source share
2 answers

The SQL EXCEPT statement accepts individual rows from a single query and returns rows that are not displayed in the second result set. The EXCEPT ALL statement does not remove duplicates. To eliminate rows and remove duplicates, the EXCEPT statement does not distinguish between NULLs.

, , , . , SQL Server .

+5

INTERSECT ALL , , SO .

.

sqlfiddle.com, postgres 9.3.
, INTERSECT ALL . row_number() over ().

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] , .

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

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

EXCEPT sql , , . , .
, , . . max(0, x.N - y.N).

, EXCEPT ALL, INTERSECT ALL, min(x.N, y.N) .

, , , : github.com/Rdatatable/data.table, , . data.table - C . 10 .

0

More articles:


All Articles