I have the following schema:
create table Took(
sID integer,
oID integer,
grade integer);
INSERT INTO Took VALUES
(1, 1, 90),
(1, 2, 100),
(2, 1, 90),
(2, 3, 80),
(2, 4, 85)
;
I have the following query as well:
(select sid from took) except all (select sid from took where grade < 90);
and this produces the output 1,1,2 which is expected since the grade values are >= 90 in this case. However, when I remove the all clause to
select sid from took) except (select sid from took where grade < 90);
The output is just 1. I know the all determines whether duplicates exist so in this case I expect the output to be 1,2 and not just 1. So whats going on?
From PSQL documentation, when EXCEPT is used alone:
The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one.
But, when ALL is used along with EXCEPT:
The result of EXCEPT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear max(m-n,0) times in the result set.
In your example, the result of left SELECT query will be the sid of all rows.
test=# select sid from took;
sid
-----
1
1
2
2
2
(5 rows)
And, the result of right SELECT query will be last two rows having grade<90.
test=# select sid from took where grade < 90;
sid
-----
2
2
(2 rows)
Now, running query with only EXCEPT:
test=# (select sid from took) except (select sid from took where grade < 90);
sid
-----
1
(1 row)
What happened here is,
This is the explanation for the result you got.
Now, running query with EXCEPT ALL:
test=# (select sid from took) except all (select sid from took where grade < 90);
sid
-----
1
1
2
(3 rows)
In this case, we just remove the result of right SELECT query form the result of left SELECT query.
Hope, this helps.