SQL

SQL EXCEPT: Everything You Need to Know

intro

Explore the essentials of using the SQL EXCEPT operator to refine data sets by eliminating duplicate entries and ensuring data integrity.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

In this blog, we’re walking you through the SQL EXCEPT operator. Join us! Have you ever found yourself grappling with complex queries, desperately seeking a way to discern the differences between two result sets? This is where the EXCEPT SQL statement comes in!

Introduction to the SQL EXCEPT Operator

In SQL, the EXCEPT operator is a powerful operator to find the difference between two datasets. In particular, it returns the rows from the first SELECT statement that do not exist in the result set of the second SELECT statement. It essentially subtracts the common rows between the two sets, returning only the unique ones from the first set.

The EXCEPT SQL operator acts as an important tool in removing duplicate records, comparing two tables, or performing complex data analysis. Here are some common use cases:

  • Finding missing values: Identify records that are present in one table but not in another. For example, you might have an inventory table and a sales table, and you might want to find the items that are in the inventory but have not been sold yet.
  • Data validation: Compare two datasets and identify any discrepancies or inconsistencies. For example, if you have two tables with similar data, you can use the SQLEXCEPT operator to find the differences between them.
  • Removing duplicates: Eliminate duplicate records from a single table. By comparing a table with itself, you can identify and remove duplicate rows based on specific criteria.
  • Data cleansing: If you're dealing with data that might contain duplicate or inconsistent records, you can use EXCEPT to identify and remove duplicates.
  • Identifying outliers: Find records that deviate from a particular pattern or set of criteria. By comparing two datasets, you can identify outliers or exceptional cases that don't conform to the expected values.

In the next section, you’ll look at the syntax of EXCEPT in SQL.

EXCEPT in SQL: Operator Syntax

Following is the syntax for the SQL EXCEPT operator:

Copy
        
1 SELECT column1, column2, ... 2 FROM table1 3 EXCEPT 4 SELECT column1, column2, ... 5 FROM table2;

Breaking down each line of the syntax:

  1. SELECT column1, column2, ...: Defines the columns that you want to select from the result set.
  2. FROM table1: Indicates the name of the first table from which you want to retrieve data.
  3. EXCEPT: This line is the SQL operator that performs the set comparison. It subtracts the result set of the second SELECT statement from the result set of the first SELECT statement, returning only the unique rows from the first set.
  4. SELECT column1, column2, ...: Defines the columns that you want to select from the second SELECT statement.
  5. FROM table2: Indicates the name of the second table that you want to compare with the first table.

Some Common Uses of EXCEPT in SQL

In this section of the blog, you’ll explore some possible use cases of the SQL EXCEPT operator. Let’s get right into it!

Finding the Difference Between Two Tables

Suppose you have two tables, employees and former_employees, and you want to find the employees who are currently active and not listed in the former_employeestable. You can use theEXCEPT to accomplish that:

Finding the difference between the two tables
Finding the difference between the two tables
Copy
        
1 SELECT employee_id, first_name, last_name 2 FROM employees 3 EXCEPT 4 SELECT employee_id, first_name, last_name 5 FROM former_employees;

Filtering Out Duplicate Records

The EXCEPT clause in SQL can also be used to remove duplicate records from a single table. For example, consider a table orders that contains duplicate entries. You can eliminate the duplicates this way using DbVisualizer:

Filtering out duplicate records with SQL EXCEPT in DbVisualizer
Filtering out duplicate records with SQL EXCEPT in DbVisualizer
Copy
        
1 SELECT order_id, customer_id 2 FROM orders 3 EXCEPT 4 SELECT order_id, customer_id 5 FROM orders 6 GROUP BY order_id, customer_id;

Combining SQL EXCEPT With Other Operators

In SQL, EXCEPT can be combined with other operators to perform more complex queries. For instance, say you have a table products and you want to find the products that are out of stock and not discontinued. You can do that this way:

Combining SQL EXCEPT with other operators
Combining SQL EXCEPT with other operators

Combining SQL EXCEPT with other operators

Copy
        
1 SELECT product_id, product_name 2 FROM products 3 WHERE product_status = 'Out of Stock' 4 EXCEPT 5 SELECT product_id, product_name 6 FROM products 7 WHERE product_status = 'Discontinued';

Using the EXCEPT SQL Operator with NULL Values

When using EXCEPT, NULL values can sometimes lead to unexpected results. To handle this, you can use the IS NULL or IS NOT NULL operators to explicitly handle NULL values in the comparison. For example:

Using EXCEPT with NULL values
Using EXCEPT with NULL values
Copy
        
1 SELECT column1, column2 2 FROM table1 3 WHERE column1 IS NOT NULL 4 EXCEPT 5 SELECT column1, column2 6 FROM table2 7 WHERE column1 IS NOT NULL;

SQL EXCEPT Real-World Examples

Time to explore some examples of EXCEPT in SQL:

Example #1

For inventory management systems, EXCEPT can be employed to find the items that are out of stock or missing from the inventory. Suppose you have an inventory table and a sales table, you can use SQL EXCEPT to identify the items that are in the inventory but have not been sold.

Sales and Inventory Tables
Sales and Inventory Tables

Next, you will build a query to identify the items that are in the inventory but have not been sold:

SQL query for unsold inventory items
SQL query for unsold inventory items
Copy
        
1 SELECT item_id, item_name 2 FROM inventory 3 EXCEPT 4 SELECT item_id, item_name 5 FROM sales;

Executing the query above in DbVisualizer:

The Unsold Inventory Items
The Unsold Inventory Items

From the query output shown in DbVisualizer above, the query retrieves the items from the inventory table that do not have a corresponding entry in the sales table ie. items B and C. The beauty of this is that it helps in locating the items that are not being sold and may require attention.

Example #2

Let’s assume you have a table named orders that stores customer orders. Each order has an order ID and a customer ID. However, due to some data entry errors or system issues, there might be duplicate entries in the table and you want to remove these duplicate records to obtain a clean set of unique orders.

Here’s how you can take care of such a situation: For illustration purposes, first can create an orders table and populate the table:

Copy
        
1 CREATE TABLE orders ( 2 order_id INT, 3 customer_id INT 4 ); 5 6 INSERT INTO orders (order_id, customer_id) 7 VALUES 8 (1,101), 9 (1,101), 10 (2,102), 11 (3,103), 12 (3,103), 13 (4,104);
The ‘Orders’ table in DbVisualizer
The u2018Ordersu2019 table in DbVisualizer

As you can see, there are duplicate entries in the table. This is where SQL EXCEPT comes in to remove these duplicates as shown below:

Copy
        
1 SELECT order_id, customer_id 2 FROM orders 3 EXCEPT 4 SELECT order_id, customer_id 5 FROM ( 6 SELECT order_id, customer_id, COUNT(*) 7 FROM orders 8 GROUP BY order_id, customer_id 9 HAVING COUNT(*) > 1 10 ) AS duplicates;
  • The first part of the EXCEPT operation above selects all records from the orders table.
  • The second part (subquery) selects all records that have duplicates, identified by grouping on order_id and customer_id and using HAVING COUNT(*) > 1 to find groups with more than one entry.
  • The operation then subtracts the duplicate records found in the subquery from the complete set of records, resulting in a list of unique orders.

Executing the query above in DbVisualizer:

Duplicate entries removed
Duplicate entries removed

Duplicate entries removed

Great! The query only returns unique entries.

Best Practices and Tips for Using the EXCEPT SQL Operator

Below are some of the best practices that should be taken into consideration when working with the EXCEPT SQL operator:

  • Ensure that the compared columns have compatible data types.
  • Be cautious when dealing with NULL values to avoid unexpected results.
  • Optimize the performance of yourEXCEPT queries by utilizing indexes and proper query design.
  • Consider using the EXCEPT clause within subqueries or with other complex query constructs to achieve more advanced filtering and analysis.

It’s a wrap!

Conclusion

The SQL EXCEPT operator provides a robust method for refining data sets, particularly in managing duplications and improving the integrity of data within a single table or across related tables. By integrating EXCEPT into data handling strategies, developers and database administrators can achieve a higher level of precision and reliability in data representation.

Leverage the EXCEPT SQL operator to compare and differentiate rows to maintain cleaner, more accurate data sets to ensure that only unique records are preserved.

To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including EXCEPT operations. Download DbVisualizer for free now!

FAQ

What is the SQL EXCEPT Operator?

EXCEPT is a set operation in SQL that returns distinct rows from the first query result that do not exist in the second query result.

How does EXCEPT in SQL work?

In SQL,EXCEPT compares the result sets of two queries and returns only the rows from the first query that are not present in the second query.

Can I use the EXCEPT SQL operator to compare more than two tables?

Yes, you can use the EXCEPT SQL operator to compare multiple tables by chaining additional EXCEPT operations.

Can EXCEPT work with multiple columns?

Yes, EXCEPT can compare multiple columns across two SELECT statements. All specified columns are compared row-wise to determine their uniqueness. Both SELECT statements must return the same number of columns, and the data types must be compatible.

Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL HAVING Clause: The Ultimate Guide

author Antonello Zanini tags SQL 7 min 2024-05-27
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29
title

SQL REPLACE Function: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-04-25
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗