387 views
1 votes
1 votes
Consider the following ORACLE relations:

One (x, y) = {<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4, 8>}

Two (x, y) = {<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>, <4, 8>, <9, 9>, <1, 6>}

Consider the following two SQL queries SQ1 and SQ2 :

SQ1: SELECT * FROM One) EXCEPT (SELECT * FROM Two);

SQ2: SELECT * FROM One EXCEPT ALL (SELECT * FROM Two);

For each of the SQL queries, what is the cardinality (number of rows) of the result obtained when applied to the instances above ?

1 Answer

0 votes
0 votes

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. 

Ref: https://stackoverflow.com/questions/28432720/trying-to-understand-except-all-in-sql-query

1. <2,5>
2. <2,5>, <1,6> (two <1,6> removed as it matched with values in two)

Related questions

1 votes
1 votes
1 answer
1
Na462 asked Jan 19, 2019
1,151 views
1 votes
1 votes
1 answer
2
Shubhanshu asked Dec 24, 2018
1,260 views
According to me it should be – “Retrieve the names of all students with a lower rank, than all students with age < 18 ”
0 votes
0 votes
1 answer
3
Na462 asked Jun 29, 2018
812 views