PostgreSQL EXCEPT

In this section, we are going to understand the working of the PostgreSQL EXCEPT operator, which is used to return the rows in the initial command that do not appear in the output of the second command.

And the examples of the PostgreSQL Except operator with single and multiple Expression and the order by clause.

What is PostgreSQL Except operator?

In PostgreSQL, the EXCEPT operator is used to retrieves rows by equating the output of two or more commands, just like UNION and INTERSECT Operators.

In other words, we can say that the PostgreSQL EXCEPT operator retrieve the different rows from the left (first SELECT) command, which are not in the result set of the right (second SELECT) command. And all the SELECT command will define a dataset.

The EXCEPT operator will return all records from the initial dataset and then eliminate all values from the second dataset.

The below image represents the final output, which is produced by the EXCEPT operator.

In the above image, the mustard yellow area represents the records that are retrieved after executing the EXCEPT command. And these records are present in the circle P and not in the circle Q.

Each SELECT command within the EXCEPT command must have a similar number of output fields with similar data types.

Syntax of PostgreSQL EXCEPT Operator

The illustration of PostgreSQL except operator is as follows:

SELECT expression1, expression2, ... expression_n
FROM table P
[WHERE conditions]
EXCEPT
SELECT expression1, expression2, ... expression_n
FROM table Q
[WHERE conditions];

In the above syntax, we have the following parameters, which we discussed in the following table:

Parameter	Description
expression1, expression2, expression_n	These are the columns or calculations, which we want to compare between the two SELECT commands. And they do not need to be matched fields in each of the SELECT commands, whereas the equivalent columns must contain the equivalent data types.
Tables	These are the tables from which we want to get records.
WHERE conditions	These are the condition that must be encountered for the values to be sorted. And it is an optional parameter.

Example of PostgreSQL Except operator

To understand the working of the PostgreSQL Except operator, we will take the top_rated_cars and most_reliable_cars tables created in the PostgreSQL UNION tutorial:

To see the data from a top_rated_cars table, we will use the SELECT command, as shown in the below command:

Output

After executing the above command, we will get the below result:

The following command returns the data from the most_reliable_cars table:

Output

On implementing the above command, we will get the below output:

Example of PostgreSQL EXCEPT with Single Expression

In the below example, we will use an EXCEPT operator which contains one field with a similar data type:

SELECT Car_name
FROM top_rated_cars
EXCEPT
SELECT Car_name
FROM most_reliable_cars;

Output

After executing the above command, we will get the following output:

In the above example, the EXCEPT operator retrieves all car_name values in the top_rated_cars table and not in the most_reliable_cars table.

It implies that if a car_name value exist in the top_rated_cars table and also been in the most_reliable_cars, the car_name value would not be appeared in the EXCEPT command output.

Example of PostgreSQL Except with various Expression

We will now see one sample example to understand PostgreSQL Except operator's working with more than one column.

In the below example, we will use the Except operator in PostgreSQL to retrieve various columns.

For this, we are taking the employee and department table from the Organization database, which we created in the earlier section of the PostgreSQL tutorial.

SELECT address, phone
FROM employee
WHERE employee_name<>'ross'
EXCEPT
SELECT address, phone
FROM department
WHERE address<>'London';

Output

After implementing the above command, we will get the below output, which displays the records from the employee table where the address and phone values do not match the department table's address and phone values.

In the above example, the Except operator command will retrieve employee table's values with an address, phone value that does not match the department table's address and phone columns value.

PostgreSQL Except with ORDER BY clause

If we want to sort the EXCEPT operator's output, we place the ORDER BY clause at the query list's final command.

Syntax of PostgreSQL Except with ORDER BY clause

The below illustration is used to represent the PostgreSQL Except with the order by clause:

SELECT select_list
FROM table1
EXCEPT
SELECT select_list
FROM table2
ORDER BY sort_expression;

Example of PostgreSQL EXCEPT with Order by clause

In the following example, we are using a similar table as top_rated_car and most_reliable_cars to understand the usage of an EXCEPT operator with the ORDER BY clause:

SELECT * FROM top_rated_cars
EXCEPT
SELECT * FROM most_reliable_cars
ORDER BY Car_name;

Output

We will get the below result set after executing the above command, which displays the sorted top_rated_cars in ascending order represented by the ORDER BY Car_name. And the Car_name column fields are in the place of #1 in the output.

In the above example, subsequently, the column names are dissimilar between the two SELECT commands.

It is more beneficial to reference the columns in the ORDER BY clause by their position in the output.

Overview

In the PostgreSQL Except section, we have learned the following topics:

We have used the PostgreSQL Except Operator to returns the rows in the initial command that do not appear in the output of the second command.
We have also understood the working of PostgreSQL Except operator with single and multiple
We have also used the PostgreSQL Except with ORDER By clause to order the command outputs.

Next TopicMongoDB vs PostgreSQL

← prev next →