SQL Standard

SQL Except

The EXCEPT clause in SQL allows you to combine two select statements and return the rows that are not available in the second statement.

Let us illustrate how to use this clause.

Syntax

We can express the operator syntax as shown below:

SELECT COLUMN(s)
FROM TABLE_NAME
[WHERE condition]
EXCEPT
SELECT COLUMN(s)
FROM TABLE_NAME
[WHERE condition]

Keep in mind that the where clause is optional.

Example

Suppose we have two tables as shown below:

Table name: users

Table name: users2

We can perform a minus operation on the tables as shown:

SELECT full_name, salary, active
FROM users
EXCEPT
SELECT full_name, salary, active
FROM users2;

NOTE: The above should return the records that are in the first select query as shown:

NOTE: Some databases do not support the except operator. For Example, the Oracle database uses MINUS as an alternative to the EXCEPT clause.

Conclusion

This article discussed the fundamentals of working with SQL Except statements. Check our tutorial on SQL Minus operator to learn more.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list