What is Except in PostgreSQL?

Apart from different functions and commands, there are also certain keywords and operators in PostgreSQL that are there to serve some pre-defined purposes. “EXCEPT” is also an operator in PostgreSQL that is used to display the intersection of two or more tables. We will explain this thoroughly in the following section of this article. Overall, this article will be based on the usage of the “EXCEPT” operator of PostgreSQL in Windows 10.

What is Except in PostgreSQL in Windows 10?

By the intersection of two or more tables, we essentially mean to access all those records of one table that are not part of the others. The “EXCEPT” operator in PostgreSQL is used to achieve this goal that we have just stated. This operator compares two or more tables and then displays only those records of the table mentioned before this operator that is not present in the table or tables stated after this operator.

Usage of Except in PostgreSQL in Windows 10

To explain the usage of the “EXCEPT” operator in PostgreSQL in Windows 10, we have created a thorough example. Instead of creating multiple different examples, we have worked with one single and simple example and have tweaked it a little bit in every next step for building a better understanding. This example is discussed below:

Example: Displaying the Intersection of Two Tables in PostgreSQL in Windows 10
In this example, our main goal is to display the intersection of two tables in PostgreSQL in Windows 10, i.e. we want to display all those records of the first table that are not present in the second table. You should take time out to read the following steps to gain more clarity:

Step 1: Create PostgreSQL Tables in Windows 10
Instead of making it a complex example, we have tried to make it extremely simple to understand. This is the sole reason behind us creating only two PostgreSQL tables. Once you learn to work with the “EXCEPT” operator in PostgreSQL through this example, you will be able to play around with more than two tables as well. Anyhow, we will create the first table with the query stated below:

# CREATE TABLE worker(WorkerID INT NOT NULL, WorkerName VARCHAR (255) NOT NULL);

We have simply created a table named “worker” with two attributes, i.e. WorkerID and WorkerName.

You can verify the successful table creation from the following response:

For creating the second PostgreSQL table, we will execute the query shown below:

# CREATE TABLE manager(ManagerID INT NOT NULL, ManagerName VARCHAR (255) NOT NULL);

We have created a table named “manager” with two attributes, i.e. ManagerID and ManagerName.

You can verify the successful table creation from the following response:

Step 2: Insert Some Data into the Newly Created PostgreSQL Tables
After creating the two PostgreSQL tables, we will insert some sample data into them. For the first table, we will execute the query shown below for the insertion of records:

# INSERT INTO worker VALUES(1, ‘Ahsan’), (2, ‘Shaan’), (3, ‘Khalid’), (4, ‘Hammad’), (5, ‘Fahad’);

We have inserted five records to our first table, as you can see from the following output response:

For the second table, we will execute the query shown below for the insertion of records:

# INSERT INTO worker VALUES(1, ‘Ahsan’), (2, ‘Shaan’), (3, ‘Khalid’);

We have inserted three records to our second table, as you can see from the following output response:

Step 3: Display all the Records of the PostgreSQL Tables
Now, we will display all the records of both the tables to confirm the successful insertion of records in them. For the first table, we will execute the query shown below:

# SELECT * FROM worker;

The records from the “worker” table are shown in the following image:

For the second table, we will execute the query shown below:

# SELECT * FROM manager;

The records from the “manager” table are shown in the following image:

Step 4: Display all those IDs from the First Table which are not present in the Second Table
When we have successfully inserted a few records into our PostgreSQL tables, we will attempt to display all those IDs from the first table that are not present in the second table. You can check out the below-shown query for this:

# SELECT WorkerID FROM worker EXCEPT SELECT ManagerID FROM manager;

This query will display all those IDs from the “worker” table which are not a part of the “manager” table, as shown in the following image:

Step 5: Modify the Previous Step while arranging the Output in Ascending Order
In the above step, you would have noticed that the IDs displayed in the output were not ordered. To sort the result in ascending order, we will execute the same query with a slight modification as shown below:

# SELECT WorkerID FROM worker EXCEPT SELECT ManagerID FROM manager ORDER BY WorkerID;

The “ORDER BY” clause in PostgreSQL is used to arrange the output in the ascending order of the specified attribute, which is “WorkerID”. This is shown in the following image:

Step 6: Display all those IDs and Names from the First Table which are not present in the Second Table
Now, we will make the usage of the “EXCEPT” operator a little more complex by displaying the complete records from the first table that are not present in the second table instead of only displaying the IDs. You can check out the below-shown query for this:

# SELECT WorkerID, WorkerName FROM worker EXCEPT SELECT ManagerID, ManagerName FROM manager;

This query will display all those records from the “worker” table which are not a part of the “manager” table, as shown in the following image:

Step 7: Modify the Previous Step while arranging the Output in Ascending Order
In the above step, you would have noticed that the records displayed in the output were not in order. To sort the result in ascending order, we will execute the same query with a slight modification as shown below:

# SELECT WorkerID, WorkerName FROM worker EXCEPT SELECT ManagerID, ManagerName FROM manager ORDER BY WorkerID;

The sorted output of the query mentioned above is shown in the following image:

Conclusion

In this article, we discussed the usage of the “EXCEPT” operator in PostgreSQL in Windows 10. For explaining this usage, we first defined the capabilities of this PostgreSQL operator. After that, we shared a comprehensive example in which we started from the very basic usage of the “EXCEPT” operator while gradually taking it to a reasonable complexity level. Once you go through all the steps of this example, you will be able to understand the working of the “EXCEPT” operator in PostgreSQL in Windows 10. After building this understanding, you will be in a good position to create different scenarios in which this PostgreSQL operator can be used very effectively in Windows 10.

What is Except in PostgreSQL?