Updated May 18, 2023
Introduction to PostgreSQL EXCEPT
The PostgreSQL EXCEPT operator is used with two SELECT statements. Let’s consider as a first SELECT statement and second SELECT statement, in order to return all of the records from the first SELECT statement which are not returned from the second SELECT statement, we generally use the PostgreSQL EXCEPT operator. The SELECT statement defined with the PostgreSQL EXCEPT operator defines a dataset. It retrieves all records from the result of the first SELECT statement’s dataset and then removes all of the records of the second SELECT statement’s dataset from the result of the first SELECT statement’s dataset.
Syntax:
SELECT column_name_1, column_name_2, ... column_name_n
FROM table_1
[WHERE conditions_table1]
EXCEPT
SELECT column_name_1, column_name_2, ... column_name_n
FROM table_2
[WHERE conditions_table2];Explanation:
- column names: We have to define the columns which we want to compare in the first and second SELECT statement. It is not required for both SELECT statements to have the same fields, only we need to make sure that they are having similar data-type.
- table1 and table2: We have defined the name of the table from which we want to retrieve the records. It is mandatory to define at least one table in the FROM clause.
- WHERE conditions: This is an optional clause which can be used to filter the records of the tables based on the conditions met.
How does EXCEPT Operator work in PostgreSQL?
The PostgreSQL EXCEPT operator works like it will return all of the records from the light brown area. In the above diagram, we can see that records which exist in the left side area (the light brown area) and do not exist in the right side area (records2).
In order to combine the PostgreSQL SELECT statements with the help of the PostgreSQL EXCEPT operator, we need to consider the following rules:
- Both of the SELECT statements should have the same number of columns.
- Also, both of the SELECT statements should have the same order for columns.
- The data types of the columns defined in the first and second SELECT statement should be the same.
Examples
Given below are the examples mentioned:
We will create two tables of name ‘student’ and ‘teacher’ by using the PostgreSQL CREATE TABLE statement.
Code:
create table student
(
stud_id serial PRIMARY KEY,
stud_fname VARCHAR(80) NOT NULL,
stud_lname VARCHAR(80) NOT NULL
);Code:
create table teacher
(
teach_id serial PRIMARY KEY,
teach_fname VARCHAR(80) NOT NULL,
teach_lname VARCHAR(80) NOT NULL
);Now, we will insert some data into the ‘student’ table by using the PostgreSQL INSERT INTO statement as follows:
Code:
INSERT INTO student(stud_fname, stud_lname)
VALUES
('Smith','Johnson'),
('Williams','Jones'),
('Harper','James'),
('Jack','Liam'),
('Harry','Mason'),
('Jacob','Oscar'),
('Michael','Charlie'),
('William','Joe'),
('Oliver','John');select * from student;Output:
Now, we will insert some data into the ‘teacher’ table by using the INSERT INTO statement as follows:
Code:
INSERT INTO teacher(teach_fname, teach_lname)
VALUES
('William','Joe'),
('Oliver','John'),
('Jack','Richard'),
('Harry','Joseph'),
('George','Thomas'),
('Brown','Charles');select * from teacher;Output:
Example #1
The PostgreSQL EXCEPT operator with a single column.
To return only one column which is having the same data-type.
Code:
SELECT stud_id
FROM student
EXCEPT
SELECT teach_id
FROM teacher;Output:
In the above statement, it will return all of the stud_ids from the student table by skipping the stud_ids which are matching with the teach_id from the teacher table. Which means if stud_id is the same as teach_id, then it will not get considered in the result set.
Example #2
The PostgreSQL EXCEPT operator with multiple columns.
To return multiple columns which are having the same data-type.
Code:
SELECT stud_id, stud_fname, stud_lname
FROM student
WHERE stud_id <= 5
EXCEPT
SELECT teach_id, teach_fname, teach_lname
FROM teacher
WHERE teach_id < 3;Output:
In the above statement, the operator will return all of the stud_id, stud_fname, and stud_lname columns from the student table by skipping the stud_id, stud_fname, and stud_lname columns, which are matching with the teach_id, teach_fname, teach_lname columns from the teacher table. Which means if stud_id, stud_fname, and stud_lname are the same as teach_id, teach_fname, and teach_lname, then it will not get considered in the result set.
Example #3
The PostgreSQL EXCEPT operator using order by.
To return multiple columns which are having the same data-type along with order by clause.
Code:
SELECT stud_id, stud_fname, stud_lname
FROM student
WHERE stud_id <= 5
EXCEPT
SELECT teach_id, teach_fname, teach_lname
FROM teacher
WHERE teach_id < 3
ORDER BY 3;Output:
In the above example, we have used the number 3 in the order by clause which means it will order the result set by the stud_lname | teach_lname column.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL EXCEPT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
