PostgreSQL EXCEPT Clause: Eliminate Overlaps in Your Query Results

Introduction

You’ve executed a couple of SELECT queries and now you’re looking to identify records that are exclusive to the first query—things that don’t appear in the second one. You’re in luck! The EXCEPT clause in PostgreSQL is just the tool you need. It helps you find rows that are in the result of the first query but not in the second. Stick around to learn how you can make the best use of EXCEPT.

Basic Syntax and Usage

Using EXCEPT is pretty straightforward. Just write two SELECT queries and place the EXCEPT keyword in between them. Like so:

SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

Remember, the number and type of columns in both queries should be compatible.

Example with tv_series and web_series Tables

Let’s say you want to know which TV series names are not also Web series. Here’s how you’d use EXCEPT:

SELECT name FROM tv_series
EXCEPT
SELECT name FROM web_series;

Common Flags and Usage Patterns

• Column Compatibility: Ensure that both queries select the same number of columns, and those columns are of compatible types.

• Ordered Results: You can combine EXCEPT with ORDER BY to sort your result set.

  SELECT name FROM tv_series WHERE release_year > 2015
  EXCEPT
  SELECT name FROM web_series WHERE release_year > 2015
  ORDER BY name ASC;

Common Errors and Solutions

Error Message:

ERROR:  each EXCEPT query must have the same number of columns

Cause:
The queries used with EXCEPT don’t have the same number of columns.

Solution:
Make sure both SELECT queries have the same number of columns and that those columns have compatible types.

Pro Tips

• Like INTERSECT, the EXCEPT clause also removes duplicates from the final output, similar to what SELECT DISTINCT would do.

• Don’t overuse EXCEPT. If you’re dealing with large datasets, it can be resource-intensive.

That’s it for the EXCEPT clause! A nifty tool to have in your PostgreSQL toolbox.