SQL Except

In SQL, we probably use the JOIN clause to receive the combined result from one or more than one table. But sometimes, we want a result that contains data from one table, and the record should not be available in the other table. In that case, SQL has the concept name SQL Except.

To purify the data from more than one table, we used SQL Except. The SQL Except is the same as the minus operator we do in mathematics. SQL Except first merges the two or more than two SELECT statements in query and returns the data from the first SELECT statement. We aren't available in another SELECT statement result.

SQL EXCEPT Rules

We should understand all the rules and regulations before using the EXCEPT query in SQL:

• The number and order of columns in the given table must be the same in the entire SELECT query.
• The column's data type must be the same or compatible.

The syntax for SQL EXCEPT

SELECT * FROM table1 EXCEPT SELECT * FROM table2;

Table1 and Table2 will be the name of tables.

Example:           

Assume we have two tables with the same number of columns and the order of columns.

• Table 1: T1, Number of Columns: 3, Data: A, B, C, D
• Table 2: T2, Number of Columns: 3, Data: B, D, F, G

Whenever we execute the EXCEPT query on these two tables, we will get A and C because these two data are not present in table T2, B and D are common in both tables, which discard.

Let's understand SQL EXCEPT concept with examples. Consider the following tables along with the given records.

Table1: Emp

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE	1
1003	NIKHIL	VANI	50000	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING	4
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WHERE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4

Table2: Employee

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	Vaibhav	Sharma	65000	PUNE	ORACLE	1
1002	Nikhil	Vani	60000	NOIDA	ORACLE	1
1003	Vaibhavi	Mishra	50000	JAIPUR	FMW	2
2001	Ruchika	Jain	55500	CHANDIGARH	ORACLE	1
2002	Prachi	Sharma	65500	PUNE	FMW	2
2003	Bhavesh	Jain	50000	MUMBAI	TESTING	4
3001	Deepam	Jauhari	55500	PUNE	JAVA	3
3002	ANUJA	WHERE	50500	JAIPUR	FMW	2
3003	Pranoti	Shende	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4

Table3: Manager

Managerid	manager_name	manager_department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING

Table4: Manager1

Managerid	manager_name	manager_department
1	Ishita Agrawal	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Paul Oakip	TESTING

Example 1: Suppose we want to join the above two tables Emp and Employee in our SELECT query using EXCEPT operator.

SELECT EMPLOYEEID, CONCAT(FIRST_NAME, LAST_NAME) AS NAME, CITY, DEPARTMENT MANAGER1.MANAGERID, MANAGER1.MANAGER_NAME FROM EMPLOYEE INNER JOIN MANAGER ON EMP.MANAGERID = MANAGER.MANAGERID EXCEPT SELECT EMPLOYEEID, CONCAT(FIRST_NAME, LAST_NAME) AS NAME, CITY, DEPARTMENT, MANAGER1.MANAGERID, MANAGER1.MANAGER_NAME FROM EMPLOYEE INNER JOIN MANAGER1 ON EMPLOYEE.MANAGERID = MANAGER1.MANAGERID;

We are using the INNER JOIN clause between Emp and Employee table where we display Employee Id, Name, City, Department, Manager Id, and Manager Name using EXCEPT operator. The above query will display only those unique values between both tables.

The above query gives the following output:

If we observe the tables data, there are two common data between both tables Emp table and Employee table, i.e., Employee id 3002 and 4001. Employee id 4001 details display except 3002. Because Employee id 3002 Manager name is the same in both tables Manager and Manager1 but Employee id 4001 Manager names are different in both tables, employee id 4002 details are displayed.

Example 2: Suppose we want to join the above two tables Emp and Employee in our SELECT query using EXCEPT operator and sort the result set by their salary in descending order. We will use the ORDER BY clause to sort the result set in the SQL query.

SELECT EMPLOYEEID, CONCAT(FIRST_NAME, LAST_NAME) AS NAME, CITY, SALARY, MANAGER1.MANAGERID, MANAGER1.MANAGER_NAME FROM EMPLOYEE INNER JOIN MANAGER ON EMP.MANAGERID = MANAGER.MANAGERID EXCEPT SELECT EMPLOYEEID, CONCAT(FIRST_NAME, LAST_NAME) AS NAME, CITY, SALARY, MANAGER1.MANAGERID, MANAGER1.MANAGER_NAME FROM EMPLOYEE INNER JOIN MANAGER1 ON EMPLOYEE.MANAGERID = MANAGER1.MANAGERID ORDER BY SALARY;

The above query shows the following output:

Example 3: Suppose we want to join the above two tables Emp and Employee in our SELECT query using EXCEPT operator where employee salary greater than 55000 from Emp table and employee city include ‘Pune’, ‘Mumbai’, ‘Jaipur’ from Employee table.

SELECT * FROM EMP WHERE SALARY > 55000 EXCEPT SELECT * FROM EMPLOYEE WHERE CITY IN ('Pune', 'Mumbai', 'Jaipur');   

The above query first SELECT statement fetches all the details of those employees whose salary is greater than 55000 from the Emp table. The second SELECT statement fetches all the details of those employees whose cities include Pune, Mumbai, Jaipur from the Employee table. Then, EXCEPT operator will be executed between the Emp table and Employee table.

This gives the following output: