MINUS (EXCEPT) Operator
Let us use the following tables
for explaining the concept of the set operators;
|
Regno
|
Name
|
Phone
|
|
10BS0123
|
Madhavan
|
9965235412
|
|
10BC0234
|
Jerry
|
8569326541
|
|
11BM0023
|
Malar
|
9658236542
|
|
11BC0003
|
Kumar
|
9653268954
|
Table 2 - S_Cricket (Students registered for Cricket)
|
Regno
|
Name
|
Phone
|
|
10MS0434
|
Noel
|
9856452314
|
|
10MS0012
|
Kumar
|
9653268954
|
|
11MC0070
|
Ramkumar
|
9965235412
|
|
11BC0003
|
Kumar
|
9653268954
|
Table 2 - S_Basketball (Students registered for Basketball)
MINUS is used to filter records that are unique to first expression only. That is, the system will find all the common records of expression 1 and 2, remove the common records, and display only records from the result of expression 1. For
example, consider the query given below, which gives the students details who have registered only for cricket.
(SELECT Name,
Phone FROM S_Cricket) MINUS (SELECT Name, Phone FROM S_Basketball);
----- Query 1
The result of this query will be;
Table 3 shows the result generated from Query 1. Here, except the common record, i.e, except ('11BC0003', 'Kumar', 9653268954), the result will contain all the other records of result of expression 1.
Related Articles
Regno
|
Name
|
Phone
|
10BS0123
|
Madhavan
|
9965235412
|
10BC0234
|
Jerry
|
8569326541
|
11BM0023
|
Malar
|
9658236542
|
Table 3 shows the result generated from Query 1. Here, except the common record, i.e, except ('11BC0003', 'Kumar', 9653268954), the result will contain all the other records of result of expression 1.
Support for SQL Set Operator MINUS (EXCEPT) in Various DBMSs:
MINUS (EXCEPT) operator in MySQL
MySQL
does not support MINUS (EXCEPT). It can be achieved through other ways using
the keywords NOT IN or NOT EXISTS. Query 1 is rewritten using the keyword NOT IN as
follows to achieve exception in MySQL.
SELECT Name,
Phone FROM S_Cricket WHERE (Name, Phone) NOT IN (SELECT Name, Phone FROM S_Basketball);
----- Query 2
MINUS (EXCEPT) operator in Oracle
Oracle supports MINUS operator as
discussed above. The above example (Query 1) can be treated as example for Oracle MINUS
operator.
MINUS (EXCEPT) operator in DB2
DB2 supports EXCEPT operator.
Above examples can be treated as examples for DB2 with MINUS replaced as EXCEPT. An example is shown in Query 3;
(SELECT Name,
Phone FROM S_Cricket) EXCEPT (SELECT Name, Phone FROM S_Basketball);
----- Query 3