UNION, UNION ALL, INTERSECT, EXCEPT in SQL

In SQL, sometimes you need to filter for entries which are either present in one table, or both, or present in one but not in other (all depending upon your need). To address this, we have 4 functions which can act as our savior:

• UNION: Results all the entries from the table removing duplicates.

• UNION ALL: Results all the entries from the table retaining duplicates.

• INTERSECT: Results common entries from the table.

• EXCEPT: Results all the entries from first table which are not present in second table

In this tutorial we shall be covering all 4 of them in detail.

Dataset:

For this tutorial we shall make use of 2 datasets: patient_info and hospital_record

CREATE TABLE patient_info(
patient_id int,
department varchar(20),
gender char(1),
age int)

INSERT INTO
patient_info(patient_id,department,gender,age)
VALUES
(1001,'Cardiology','M',24),
(1002,'Radiology','M',32),
(1003,'E&A','F',44),
(1004,'Radiology','M',32),
(1005,'Neurology','M',38);

CREATE TABLE hospital_record(
patient_id int,
department varchar(20),
length_of_stay int,
last_visit_date date)

INSERT INTO
hospital_record(patient_id,department,length_of_stay,last_visit_date)
VALUES
(1001,'Cardiology',20,'12Aug2020'),
(1002,'Radiology',0,'13Feb2019'),
(1004,'Radiology',2,'31Jan2020'),
(1005,'Neurology',4,'9Aug2018'),
(1005,'Cardiology',18,'9Aug2018'),
(1004,'Neurology',65,'19Jan2020');

Our data looks as follows:

SELECT * FROM patient_info;

SELECT * FROM hospital_record;

Basic Syntax for all 4 keywords:

SELECT <common columns> FROM table1

(KEYWORD)

SELECT <common columns> FROM table2;

1. UNION

Task: Obtain unique patient IDs from both the tables.

SELECT patient_id FROM patient_info
UNION 
SELECT patient_id FROM hospital_record;

2. UNION ALL

Task: Obtain unique patient IDs from both the tables (retaining duplicate patient_id) from both the tables.

SELECT patient_id FROM patient_info
UNION ALL
SELECT patient_id FROM hospital_record;

We have 5 records in patient_info and 6 in hospital_record. Thus UNION ALL results in 11 rows.

3. INTERSECT

Task: Obtain common patient IDs and department combinations available in both the tables.

SELECT patient_id,department FROM hospital_record
INTERSECT
SELECT patient_id,department FROM patient_info;

4. EXCEPT

Task: Obtain patient IDs which are available in patient_info but not in hospital_record.

SELECT patient_id FROM patient_info
EXCEPT
SELECT patient_id FROM hospital_record;

Task: Obtain patient IDs, department combinations which are available in hospital_record but not in patient_info.

SELECT patient_id,department FROM hospital_record
EXCEPT
SELECT patient_id,department FROM patient_info;