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.
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
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"