Showing posts with label Set Operators. Show all posts
Showing posts with label Set Operators. Show all posts

EXCEPT or MINUS Set operator in SQL





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;



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

Related Articles

INTERSECT Set Operator


SQL Set Operator INTERSECT and INTERSECT ALL / Supported Set Operators in various DBMSs / INTERSECT and INTERSECT ALL Set Operators with Examples


Let us use the following tables for explaining the concept of the INTERSECT set operator;
Regno
Name
Phone
10BS0123
Madhavan
9965235412
10BC0234
Jerry
8569326541
11BM0023
Malar
9658236542
11BC0003
Kumar
9653268954
Table 1 - UG_Student
Regno
Name
Phone
10MS0434
Noel
9856452314
10MS0012
Kumar
9653268954
11MC0070
Ramkumar
9965235412
Table 2 - PG_Student
INTERSECT operator is used to join two or more tables vertically, and displays only the records which are common for all the tables. That is, the INTERSECT operator filters the records which can be commonly found in all the tables according to the given expressions. For example, consider the query given below;
(SELECT Name, Phone FROM UG_Student) INTERSECT (SELECT Name, Phone FROM PG_Student);
----- Query 1
The result of this query will be;



Name
Phone
Kumar
9653268954
Table 3 - Result of  "(SELECT Name, Phone FROM UG_Student) INTERSECT (SELECT Name, Phone FROM PG_Student);"



The result shows the only common record according to the given expressions 1 (SELECT Name, Phone FROM UG_Student) and 2 (SELECT Name, Phone FROM PG_Student) of Query 1.



The INTERSECT operator can be used in two ways;
1. INTERSECT [Distinct] – Here Distinct keyword need not be specified.
2. INTERSECT ALL – Removes the duplicate tuples (records) from the result set. For intersect all, we will get the result as Table 4.


Name
Phone
Kumar
9653268954
Kumar
9653268954

Table 4 - Result of  "(SELECT Name, Phone FROM UG_Student) INTERSECT ALL (SELECT Name, Phone FROM PG_Student);"



Support for SQL Set Operator INTERSECT in Various DBMSs:

INTERSECT operator in MySQL

MySQL does not support INTERSECT. It can be achieved through other ways using the keywords IN or EXISTS. Query 1 is rewritten using the keyword IN as follows to achieve intersection in MySQL.

SELECT Name, Phone FROM UG_Student WHERE (Name, Phone) IN (SELECT Name, Phone FROM PG_Student);

 

INTERSECT operator in Oracle

Oracle supports INTERSECT operator as discussed above. The above example (Query 1) can be treated as example for Oracle INTERSECT operator.

INTERSECT operator in DB2

DB2 supports INTERSECT operator. Above examples can be treated as examples for DB2 too.

Related Articles


Wikipedia

Search results

Followers