PostgreSQL Except Operator

In PostgreSQL, EXCEPT operator find the unique rows from one query which do not appear in other. It helps to compare the datasets.

Here, we provide venn diagram to understand this operator.

Syntax

Following is the syntax of EXCEPT operator in PostgreSQL −

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

Example of Except Operator

To learn the EXCEPT operator in PostgreSQL, first create a table with some rows and columns. Then, create another table that combines with the previous table to find the uniqueness of rows and shows how the EXCEPT operator works.

Now, you follow the below steps −

Step 1: Create the table and then insert some value for filling data.

CREATE TABLE candidates (
    candidate_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    skills VARCHAR(100),
    experience INT
);

-- Insert data into candidates table
INSERT INTO candidates (candidate_id, first_name, last_name, age, skills, experience)
VALUES 
(1, 'John', 'Doe', 28, 'Python, SQL', 3),
(2, 'Raj', 'Kumar', 25, 'Java, C++', 2),
(3, 'Sam', 'Brown', 30, 'JavaScript, SQL', 4),
(4, 'Tony', 'Stark', 27, 'Python, Java', 3),
(5, 'Albert', 'Clark', 29, 'C++, Python', 5);

Step 2: Now, create the another table of 4 rows which is named as hired_candidates.

CREATE TABLE hired_candidates (
    candidate_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    skills VARCHAR(100),
    experience INT
);

-- Insert data into hired_candidates table
INSERT INTO hired_candidates (candidate_id, first_name, last_name, age, skills, experience)
VALUES 
(1, 'John', 'Doe', 28, 'Python, SQL', 3),
(3, 'Sam', 'Brown', 30, 'JavaScript, SQL', 4),
(5, 'Albert', 'Clark', 29, 'C++, Python', 5);

Step 3: Next, we are applying an EXCEPT operator and the query is provided below −

-- Find candidates who are not hired from candidates table but not in hired_candidates table
SELECT candidate_id, first_name, last_name, age, skills, experience
FROM candidates
EXCEPT
SELECT candidate_id, first_name, last_name, age, skills, experience
FROM hired_candidates;

The above query produces the following result −