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:
In the above syntax, we have the following parameters, which we discussed in the following table:
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:
After executing the above command, we will get the below result:
The following command returns the data from the most_reliable_cars table:
On implementing the above command, we will get the below output:
In the below example, we will use an EXCEPT operator which contains one field with a similar data type:
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.
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.
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:
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:
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.
In the PostgreSQL Except section, we have learned the following topics: