In the realm of SQL, the EXCEPT operator is a powerful tool used to compare and filter results from two queries. It helps to retrieve unique records from the first query while excluding those that appear in the result of the second query. This article will delve into the EXCEPT operator, its syntax, practical usage, and alternatives in databases that do not support it, such as MySQL.
What is the SQL EXCEPT Operator?
The EXCEPT operator in SQL is used to compare the result sets of two queries. It retrieves all distinct records from the first query (the left operand) that are not present in the result set of the second query (the right operand). Essentially, it provides a way to find records that are unique to the left query.
For example, consider two tables A and B. If we want to find records in A that do not appear in B, we would use:
SELECT column1, column2, ...
FROM A
EXCEPT
SELECT column1, column2, ...
FROM B;
The result set will include records from A that are not present in B.
Syntax of the EXCEPT Operator
Here’s the standard syntax for using the EXCEPT operator in SQL:
SELECT column1, column2, ...
FROM table1
[WHERE conditions]
EXCEPT
SELECT column1, column2, ...
FROM table2
[WHERE conditions];
- SELECT column1, column2, …: Specifies the columns to retrieve.
- FROM table1: Indicates the first table or query.
- EXCEPT: The operator used to filter out records present in the second query.
- SELECT column1, column2, …: Specifies the columns to retrieve from the second query.
- FROM table2: Indicates the second table or query.
The number and order of columns in both SELECT statements must match for the EXCEPT operator to work correctly.
Example Use Case
Let’s illustrate the EXCEPT operator with a practical example. Consider two tables: STUDENTS and STUDENTS_HOBBY.
- Creating the Tables:
CREATE TABLE STUDENTS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SUBJECT VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
HOBBY VARCHAR(20) NOT NULL,
PRIMARY KEY(ID)
);
CREATE TABLE STUDENTS_HOBBY (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
HOBBY VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY(ID)
);
- Inserting Values:
INSERT INTO STUDENTS VALUES
(1, 'Naina', 'Maths', 24, 'Cricket'),
(2, 'Varun', 'Physics', 26, 'Football'),
(3, 'Dev', 'Maths', 23, 'Cricket'),
(4, 'Priya', 'Physics', 25, 'Cricket'),
(5, 'Aditya', 'Chemistry', 21, 'Cricket'),
(6, 'Kalyan', 'Maths', 30, 'Football'),
(7, 'Aditya', 'Chemistry', 21, 'Cricket'),
(8, 'Kalyan', 'Chemistry', 32, 'Cricket');
INSERT INTO STUDENTS_HOBBY VALUES
(1, 'Vijay', 'Cricket', 18),
(2, 'Varun', 'Football', 26),
(3, 'Surya', 'Cricket', 19),
(4, 'Karthik', 'Cricket', 25),
(5, 'Sunny', 'Football', 26),
(6, 'Dev', 'Cricket', 23);
- Performing the EXCEPT Operation:
SELECT NAME, HOBBY, AGE FROM STUDENTS
EXCEPT
SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY;
Output:
NAME HOBBY AGE
Aditya Cricket 21
Kalyan Cricket 32
Kalyan Football 30
Naina Cricket 24
Priya Cricket 25
In this result, records from STUDENTS that are not present in STUDENTS_HOBBY are displayed.
Alternatives in MySQL
MySQL does not support the EXCEPT operator. Instead, you can achieve similar results using LEFT JOIN along with DISTINCT to filter out records. Here’s how you can replicate the EXCEPT functionality in MySQL:
SELECT DISTINCT s.NAME, s.HOBBY, s.AGE
FROM STUDENTS s
LEFT JOIN STUDENTS_HOBBY sh ON s.NAME = sh.NAME AND s.HOBBY = sh.HOBBY AND s.AGE = sh.AGE
WHERE sh.NAME IS NULL;
Advanced Usage of EXCEPT
- Using EXCEPT with BETWEEN Operator:
To exclude records based on a range, you can use EXCEPT with the BETWEEN operator:
SELECT NAME, HOBBY, AGE
FROM STUDENTS
WHERE AGE BETWEEN 20 AND 30
EXCEPT
SELECT NAME, HOBBY, AGE
FROM STUDENTS_HOBBY
WHERE AGE BETWEEN 20 AND 30;
Output:
NAME HOBBY AGE
Aditya Cricket 21
Kalyan Football 30
Naina Cricket 24
Priya Cricket 25
- Using EXCEPT with IN Operator:
To exclude records based on a list of values:
SELECT NAME, HOBBY, AGE
FROM STUDENTS
WHERE HOBBY IN ('Cricket')
EXCEPT
SELECT NAME, HOBBY, AGE
FROM STUDENTS_HOBBY
WHERE HOBBY IN ('Cricket');
Output:
NAME HOBBY AGE
Aditya Cricket 21
Kalyan Cricket 32
Naina Cricket 24
Priya Cricket 25
- Using EXCEPT with LIKE Operator:
To filter records based on a pattern:
SELECT ID, NAME, HOBBY, AGE
FROM STUDENTS
WHERE HOBBY LIKE 'F%'
EXCEPT
SELECT ID, NAME, HOBBY, AGE
FROM STUDENTS_HOBBY
WHERE HOBBY LIKE 'F%';
Output:
ID NAME HOBBY AGE
6 Kalyan Football 30
Conclusion
The EXCEPT operator is a valuable SQL tool for excluding records from one query based on the results of another. While it’s not available in all SQL databases, understanding its functionality and alternatives ensures you can efficiently handle similar requirements across different SQL environments. Whether you’re using SQL Server, PostgreSQL, or a database without native EXCEPT support like MySQL, these techniques can help you achieve your data filtering goals.
