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:
