In a day to day scenario, we do meet with requirements where we need to get the common result set from multiple tables. In order to achieve such requirements, SQL has provided a feature called Intersect. Intersection means common between two or more. Similar to its literal meaning SQL intersect provides the common result between multiple SELECT statements.
SQL Intersect
The SQL Intersect clause is used for combining two or more SELECT queries, but the result set will be the intersection of the queries. In common words, SQL Intersect will provide the common result between multiple SELECT statements.
Rules for Usage of SQL Intersect
- The queries should have the same number of columns as part of the select query.
- The data type for the result sets should be the same.
- Intersect uses column position for combination and not column name.
- The column in each select query must be in the same order.
SQL Intersect Representation
SQL Intersect Syntax
|
1 2 3 4 5 |
SELECT column_name[s] from table1 INTERSECT SELECT column_name[s] from table2; |
MySQL does not support SQL Intersect clause. I am using PostgreSQL database in this tutorial to show SQL Intersect examples.
SQL Intersect Example
Let us consider the following two tables for SQL Intersect.
Customer Table
| CUSTOMER ID | CUSTOMER NAME | STATE | COUNTRY |
|---|---|---|---|
| 1 | Akash | Delhi | India |
| 2 | Amit | Hyderabad | India |
| 3 | Jason | California | USA |
| 4 | John | Texas | USA |
Supplier Table
| SUPPLIER ID | SUPPLIER NAME | STATE | COUNTRY |
|---|---|---|---|
| 1 | Apple | California | USA |
| 2 | TCS | Hyderabad | India |
| 3 | Information System | Delhi | India |
| 4 | Solar Energy | Texas | USA |
Here is the script for creation of tables and insertion of sample data in PostgreSQL database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE TABLE public."Customer" ( "Customer_Id" bigint NOT NULL, "Customer_Name" character varying(50) COLLATE pg_catalog."default" NOT NULL, "State" character varying(20) COLLATE pg_catalog."default" NOT NULL, "Country" character varying(20) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT "Customer_pkey" PRIMARY KEY ("Customer_Id") ) CREATE TABLE public."Supplier" ( "Supplier_Id" bigint NOT NULL, "Supplier_Name" character varying(50) COLLATE pg_catalog."default" NOT NULL, "State" character varying(20) COLLATE pg_catalog."default" NOT NULL, "Country" character varying(20) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT "Supplier_pkey" PRIMARY KEY ("Supplier_Id") ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; INSERT INTO public."Customer"("Customer_Id", "Customer_Name", "State", "Country")VALUES (1, 'Akash', 'Delhi', 'India'), (2, 'Amit', 'Hyderabad', 'India'), (3, 'Jason', 'California', 'USA'), (4, 'John', 'Texas', 'USA'); INSERT INTO public."Supplier"("Supplier_Id", "Supplier_Name", "State", "Country")VALUES (1, 'Apple', 'California', 'USA'), (2, 'TCS', 'Hyderabad', 'India'), (3, 'Information System', 'Delhi', 'India'), (4, 'Solar Energy', 'Texas', 'USA'); |
Let’s look into some example for SQL Intersect using these tables.
-
- SQL Intersect
|
1 2 3 4 |
Select State ,Country from Customer Intersect Select State,Country from Supplier; |
Output:
| STATE | COUNTRY |
|---|---|
| California | USA |
| Hyderabad | India |
| Texas | USA |
| Delhi | India |
SQL Intersect
The above result set is the output of common rows from both the tables.
-
- SQL Intersect with order by
|
1 2 3 4 |
Select State ,Country from Customer Intersect Select State,Country from Supplier order by State; |
Output:
| STATE | COUNTRY |
|---|---|
| California | USA |
| Delhi | India |
| Hyderabad | India |
| Texas | USA |
SQL Intersect with order by
The above result set is the output of common rows from both the tables in a sorted pattern based on the state.
-
- SQL Intersect based on Country column
|
1 2 3 4 |
Select State ,Country from Customer Intersect Select State,Country from Supplier where Country='India'; |
Output:
| STATE | COUNTRY |
|---|---|
| Delhi | India |
| Hyderabad | India |
SQL Intersect with Where clause
The above result set is the output of common rows from both the tables based on where clause depending on the country.