EXCEPT operator

In SQL, Operators are special character, symbol or a keyword that is used to perform some specific operations. For example : comparing data

SQL operators are commonly used with SQL statements to retrieve, filter, and manipulate data.

EXCEPT operator :

EXCEPT operator is used to retrieve the distinct rows from the left table ( left SELECT statement ) that don't exists in the right table ( right SELECT statement ).

The syntax of the EXCEPT operator generally looks like this :

SELECT column_name(s)
FROM table1
EXCEPT
SELECT column_name(s)
FROM table2;
        

• Specify the column(s) name after the 'SELECT' keyword.

• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.

• Specify the table name after the 'FROM' keyword.

• 'table1' and 'table2' refer to the two tables being merged together.

• 'EXCEPT' keyword combines the result set of multiple SELECT statements into a single result and returns distinct rows from left table that don't not exist in right table.

Here's an example of how you might use the EXCEPT operator :

Let's suppose we have two tables :

[a] Employees Table :

→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].

[b] Employees_2 Table :

→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.

1. Please ensure that each column_name(s) and their data types are in the same order across all SELECT statements.

For example, let's see what happens if we run the below statement :

SELECT *
FROM [Employees]
EXCEPT
SELECT *
FROM [Employees_2];
        

An error is thrown indicating that while performing 'INTERSECT' operation, number of columns must be the same in all SELECT statements.

The error occurred because we specified 'Asterisk' (*) symbol that selects all columns from the table where [Employees_2] table contains '[Age]' column which is absent in [Employees] table. Therefore, we need to specify column_name(s) explicitly in all SELECT statements.

2. Please run the following SQL statement :

SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees]
EXCEPT
SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees_2];
        

3. Above statement merges the result sets of both employee tables into a single result set for all selected columns. It returns distinct rows from left table that don't exist in right table. Hence only 4 row are returned.

That's it! You have successfully merged data using 'EXCEPT' operator.

To enhance your understanding of 'SQL operators', be sure to check out our next tutorial.

Frequently Asked Questions :

The "EXCEPT" operator in SQL is used to return distinct rows from the first query that are not present in the result of the second query.

"EXCEPT" and "MINUS" in SQL perform similar operations, returning distinct rows from the first query that are not present in the result of the second query, with "MINUS" being used in some databases like Oracle.

To use "EXCLUDE" in SQL, you typically employ other SQL operators or clauses such as "NOT IN" or "NOT EXISTS" to filter out specific records from query results.

Yes, "EXCEPT" is a set operation in SQL, used to subtract one set of data from another based on certain criteria, returning the distinct values present in the first set but not in the second.