Syntax
INTERSECT
Select Column_name1, Column_name2 from table2
This is a basic syntax of the intersection feature. This can be changed by adding an asterisk for all the data by not specifying the column names. The command works in a way that both the SELECT statements will work and fetch the record and then the INTERSECT feature will choose the common data and remove the uncommon one.
Working of INTERSECT Feature in PostgreSQL
- This feature is only used when we have common data or duplicate data in both the specified columns.
- It helps to deal with more than one table fetching the same entities from the tables.
To implement the intersect command, we need to create three tables here. We can use any table for example but to understand the concept of intersection, we need to inherit some attributes and create a joint. The first one will contain the whole information regarding the student. Whereas the second and third tables will contain some portion of the information added in the first one. So we will create the first table named student.
student_id serial PRIMARY KEY,
student_name VARCHAR (255) NOT NULL
);
The table contains two columns, student id, and the student’s name. Now, we will further create two tables named subject and practical. Both tables are originated from the parent table student. So to connect them, both the tables will contain the primary key of the student table as a foreign key constraint to create a joint between all the tables. Both the below mentioned tables have one attribute of their own and one attribute as a primary key exported from the student table.
student_id INT PRIMARY KEY,
join_date DATE NOT NULL,
FOREIGN KEY (student_id) REFERENCES student (student_id)
);
CREATE TABLE practical(
student_id INT PRIMARY KEY,
join_date DATE NOT NULL,
FOREIGN KEY (student_id) REFERENCES student (student_id)
);
After the creation of all the tables, we will use an insert statement to enter data in it. Each table will get data after the execution of the command.
You can see that the data is inserted only in the name column; the id column is filled automatically like the serial number. And the values in the id column of the subject and practical tables will be filled from the subject table. But the insertion in the subject and the practical table will be in such a way that some ids in both the tables should be similar to cope with the situation we had applied.
Similarly the subject and the practical tables are inserted by data.
>> INSERT INTO practical VALUES (9, '2000-01-01'), (2, '2002-06-01'), (5, '2006-06-01'), (10, '2005-06-01');
Subject table:
Practical table:
Now, we will use a SELECT statement to see the inserted data in the student table.
Example 1
This example will show the record of those students who have joined both the subjects and the practical from the student table. This will be done by using an INTERSECT statement applied on both subject and the practical table. This command will work in such a way that it will consider the student ids in both the subject and the practical class. Those ids that are common in both tables will be fetched separately.
This will fetch two rows as only two students have enrolled in both the subjects and the practical. Both ids have separate joining dates, that’s why we have not fetched that column.
Now consider a scenario in which we want to fetch the record from the id column and the join_date column from one table that intersects the data in the id column of the practical table, and then guess what will happen?
An error will occur because for the intersect feature to be applied, we must have mentioned the same number of columns in both tables. Otherwise, the query will not work.
So to overcome this error, now we will take both columns for both tables to apply to intersect, but unfortunately there is not a single row that matches with the join_date. If any date of joining in both the tables is the same, then the resultant value must have two columns with a common row of both affected tables.
Example 2
This example includes the same intersect command, but now some additional features are added in the command that is ORDER BY clause. This feature is used to arrange the fetched result. But as we mention ‘DESC’ with this clause, then the result is arranged in descending order.
From the output, you can see that the values were first arranged in ascending order but DESC has converted them reversely.
Example 3
In this example, we will apply the intersect command on the student_id column of the subject and the student table. Then those ids that we have entered only in the subject table will be fetched.
Example 4
If you apply an intersect feature on the same table that you have selected then it will fetch all the records of the table just like the resultant value obtained from the SELECT statement. You can see that we have applied the intersect command on the practical table without specifying the column, so all the data along with the join date is fetched.
Postgres Intersect Command via pgAdmin
Now, we will check the intersect command in the pgAdmin dashboard just like psql. After opening the interface, go to the tables in the Postgres database and sequence options. Keep on expanding until you get the tables option now select the table student and then proceed to the query tool to use a command.
We have altered the above query in which the intersect feature is applied on the practical and the student table.
After applying the query, execute it by selecting the option. You will see that all the common ids are fetched from both the specified tables in the command.
Conclusion
The article ‘Postgres intersect command’ describes the working of this operator in the PostgreSQL database system. Some elementary examples are used here to describe its working in detail. Each common data can be fetched easily by using this INTERSECT feature in the commands to avoid any redundancy and coupling of data in a database.
