SQL Intersect With Examples

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

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.

Let’s look into some example for SQL Intersect using these tables.

    1. SQL Intersect

Output:

STATE COUNTRY
California USA
Hyderabad India
Texas USA
Delhi India
Intersect

 

SQL Intersect

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

    1. SQL Intersect with order by

Output:

STATE COUNTRY
California USA
Delhi India
Hyderabad India
Texas USA

Intersect_OrderBy-450x315

 

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.

    1. SQL Intersect based on Country column

Output:

STATE COUNTRY
Delhi India
Hyderabad India

Intersect_where-450x243

 

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.

By admin

Leave a Reply