SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL
SQL Except
2022-03-17 21:24:31

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

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWHERE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4

Table2: Employee

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VaibhavSharma65000PUNEORACLE1
1002NikhilVani60000NOIDAORACLE1
1003VaibhaviMishra50000JAIPURFMW2
2001RuchikaJain55500CHANDIGARHORACLE1
2002PrachiSharma65500PUNEFMW2
2003BhaveshJain50000MUMBAITESTING4
3001DeepamJauhari55500PUNEJAVA3
3002ANUJAWHERE50500JAIPURFMW2
3003PranotiShende58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4

Table3: Manager

Manageridmanager_namemanager_department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING

Table4: Manager1

Manageridmanager_namemanager_department
1Ishita AgrawalORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Paul OakipTESTING

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:

SQL EXCEPT

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:

SQL EXCEPT

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:

SQL EXCEPT