SQL Intersect

Filed Under: SQL

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

SQL Intersect Representation

SQL Intersect Syntax


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.


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.

  1. SQL Intersect
  2. Select State ,Country from Customer
    Intersect
    Select State,Country from Supplier;
    

    Output:

    STATE COUNTRY
    California USA
    Hyderabad India
    Texas USA
    Delhi India
    SQL Intersect Example

    SQL Intersect

    The above result set is the output of common rows from both the tables.

  3. SQL Intersect with order by
  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 Order By clause

    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.

  5. SQL Intersect based on Country column
  6. 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

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages