Yogesh Chauhan's Blog

INTERSECT and EXCEPT in Postgres

in Postgres on July 4, 2020

INTERSECT and EXCEPT, both of them returns distinct rows by comparing the results of two queries.

EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

The basic rules to use EXCEPT and INTERSECT are:

The number and the order of the columns must be the same in all queries.

The data types must be compatible.

INTERSECT

Syntax:


SELECT column(s) FROM table1
INTERSECT
SELECT column(s) FROM table2;

INTERSECT operator example

I am using the database for all examples. It is available on my Github public repo

This query gives us all the customer_id which has placed an order after opening an account.


SELECT customer_id
FROM customers
INTERSECT
SELECT customer_id
FROM orders;

//Output
customer_id
"TOMSP"
"LONEP"
"OLDWO"
...
...
89 rows

EXCEPT

Syntax:


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

EXCEPT operator example

This query gives us the customer_id who has opened an account but never placed an order.


SELECT customer_id
FROM customers
EXCEPT
SELECT customer_id
FROM orders;

//Output
customer_id
"FISSA"
"PARIS"

Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Global JavaScript methods to convert variables to numbersJavaScriptSQL Basics: What does the asterisk (*) mean in a query?SQL/MySQLAmpersand (Parent Selector) in SCSS (Sass)SCSSThe difference between Class declarations and Class expressions in JavaScriptJavaScriptHow to check if a link has http or https in it in JavaScript?JavaScriptAlternate Style Sheets in CSSCSS