SQL

SQL INTERSECT: Everything You Need to Know

intro

Retrieve the common records between two query results using the SQL INTERSECT operator. Learn the syntax, use cases, and best practices.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

SQL provides several set operations to manipulate and analyze data. One of these is the SQL INTERSECT operator, which allows you to retrieve common rows between two query results.

In this article, you will delve into the details of the INTERSECT SQL operator and understand its syntax and usage. Join us as you dig into the INTERSECT operator in SQL!

Introduction to the SQL INTERSECT Operator

In SQL, INTERSECT is an operator that retrieves the common rows between two query results. In particular, it combines two SELECT statements and returns only the dataset that is common between the two SELECT statements.

An Illustration of the SQL INTERCEPT Operator
An Illustration of the SQL INTERCEPT Operator
Copy
        
1 An Illustration of the SQL INTERCEPT Operator

Similar to SQL EXCEPT, the INTERSECT SQL operator can only be used under these two conditions:

  1. There is an equal number and order of columns in both queries. The SELECT statements being compared must return the same number of columns.
  2. The respective data types of the columns in both SELECT statements are compatible with each other.

INTERCEPT in SQL: Operator Syntax

Following is the syntax for the SQL INTERCEPT operator:

Copy
        
1 SELECT column1, column2, ... 2 FROM table1 3 [WHERE condition] 4 INTERSECT 5 SELECT column1, column2, ... 6 FROM table2; 7 [WHERE condition]

Breaking down each line of the syntax:

  1. SELECT column1, column2, ... : Defines the columns that you want to select from the result set.
  2. FROM table1: Specifies the name of the first table from which you want to retrieve data.
  3. INTERSECT: The retrieves the rows in common between two SELECT query results.
  4. SELECT column1, column2, ...: Defines the columns that you want to select from the second SELECT statement.
  5. FROM table2: Indicates the name of the second table that you want to compare with the first table.
  6. [WHERE condition]: The WHERE condition represents an optional clause that can be used to filter the rows returned from each individual SELECT statement before applying the INTERSECT operation. The condition can consist of one or more predicates using comparison operators (=, <>, >, <, >=, <=), logical operators (AND, OR), and other SQL expressions.

Note that this is just an example, but you can apply the INTERSECT SQL operator also on more than two SELECT statements.

SQL INTERSECT Use Cases

In this section, you will look at some of the possible use cases of the SQL INTERSECT operator. Let’s get right into it!

Example #1

Consider a scenario where we’re to find the job titles in a company held by both males and females. First, create the HR table and then fetch the output using the SQL INTERSECT operator.

Step A: First, add an HR table to your database and populate it with some data as below:

Copy
        
1 CREATE TABLE HR ( 2 id INT, 3 name VARCHAR(20), 4 title VARCHAR(20), 5 gender VARCHAR(1), 6 age INT 7 ); 8 9 INSERT INTO HR (id, name, title, gender, age) 10 VALUES 11 (1, 'Martin', 'Sales Representative', 'M', 23), 12 (2, 'Jessica', 'Receptionist', 'F', 21), 13 (3, 'David', 'Sales Representative', 'M', 27), 14 (4, 'Carter', 'Manager', 'M', 25), 15 (5, 'Kate', 'Sales Representative', 'F', 23), 16 (6, 'Josephine', 'Manager', 'F', 26), 17 (7, 'Joel', 'Business Development', 'M', 28);
Creating and Populating the HR Table with DbVisualizer
Creating and Populating the HR Table with DbVisualizer
Copy
        
1 Creating and Populating the HR Table with DbVisualizer

Step B: By running the SQL query below, you’re going to see that the Receptionist and Business Development titles are the only titles with no male and female employees, respectively.

Copy
        
1 SELECT * FROM HR;
A Complete List of Employees
A Complete List of Employees
Copy
        
1 A Complete List of Employees

Step C: Next, implement the SQL INTERSECT operation. The expected result is that:

  • The first SELECT query retrieves all the job titles (title) from the HR table where the gender is male (Gender = 'M').
  • The second SELECT query retrieves all the job titles from the HR table where the gender is female (Gender = 'F').
  • The INTERSECT SQL operation then finds the intersection of these two sets of titles, meaning it returns the titles that are common to the two specified genders.
Copy
        
1 SELECT title 2 FROM HR 3 WHERE Gender = 'M' 4 INTERSECT 5 SELECT title 6 FROM HR 7 WHERE Gender = 'F';
Successful SQL INTERCEPT Operation
Successful SQL INTERCEPT Operation
Copy
        
1 Successful SQL INTERCEPT Operation

You might have asked yourself that if the AND operator can help find common data values, why use the INTERSECT operator? Well, the reason is that the SQL INTERSECT works on rows, but the AND operator on the other hand works on columns.

If you use the ANDoperator here, it won’t work at all. This is because the AND operator will fetch the results where the same row will have both male and female and so it would return no results as shown:

The ‘AND’ Operator Fetching  No Results
The AND Operator Fetching No Results
Copy
        
1 The ‘AND’ Operator Fetching No Results

Example #2

Consider a scenario where you have to identify consistent sales records between two sources for the year 2023.

Step A: First, you will create the tables Sales_2023_SourceA and Sales_2023_SourceB with identical structures, then populate them with sample data as shown:

Copy
        
1 CREATE TABLE Sales_2023_SourceA ( 2 SaleID INT, 3 Product VARCHAR(50), 4 Amount DECIMAL(10, 2), 5 SaleDate DATE 6 ); 7 INSERT INTO Sales_2023_SourceA 8 VALUES 9 (1, 'Laptop', 1200.00, '2023-01-05'), 10 (2, 'Tablet', 300.00, '2023-02-10'), 11 (3, 'Smartphone', 800.00, '2023-03-15'); 12 13 CREATE TABLE Sales_2023_SourceB ( 14 SaleID INT, 15 Product VARCHAR(50), 16 Amount DECIMAL(10, 2), 17 SaleDate DATE 18 ); 19 INSERT INTO Sales_2023_SourceB 20 VALUES 21 (1, 'Laptop', 1200.00, '2023-01-05'); 22 (2, 'Tablet', 300.00, '2023-02-10'); 23 (4, 'E-Reader', 150.00, '2023-04-20');
Creating and Populating the Tables in DbVisualizer
Creating and Populating the Tables in DbVisualizer
Copy
        
1 Creating and Populating the Tables in DbVisualizer

Step B: Next, use the SQL INTERSECT operation to find the sales records that are consistent between Sales_2023_SourceA and Sales_2023_SourceB.

Copy
        
1 SELECT SaleID, Product, Amount, SaleDate 2 FROM Sales_2023_SourceA 3 INTERSECT 4 SELECT SaleID, Product, Amount, SaleDate 5 FROM Sales_2023_SourceB;

The INTERSECT operation should return the rows that are common to both Sales_2023_SourceA and Sales_2023_SourceB. Since the SaleID, Product, Amount, and SaleDate for sales records 1 and 2 match in both sources, these records will be returned by the INTERSECT operation.

Successful Execution of the INTERSECT Operation
Successful Execution of the INTERSECT Operation
Copy
        
1 Successful Execution of the INTERSECT Operation

This query result in DbVisualizer above helps you identify that sales records 1 and 2 are consistent across both sales sources, while clearly capitalizing on the fact that there are discrepancies or exclusive records in either source (such as the E-Reader sale in Sales_2023_SourceB and the Smartphone sale in Sales_2023_SourceA).

Best Practices and Tips for Using the INTERSECT SQL Operator

Time to look at some of the best practices that should be taken into consideration when working with the SQLINTERSECT operator:

  • Before using INTERSECT, ensure that the columns you are comparing have compatible data types. Mismatched data types can lead to errors or unexpected results.
  • It is important to consider breaking down complex queries into CTEs (Common Table Expressions) or temporary tables as complex INTERSECT operations can become hard to read. Find out more in our guide on PostgreSQL CTE.
  • If possible, narrow down your result sets using WHEREclauses before applying INTERSECT. This reduces the amount of data the database engine needs to process thereby preventing the slowing down of INTERSECT operations.

It’s a wrap!

Conclusion

The SQL INTERSECT operator offers a precise tool for comparing and identifying common data across different datasets. To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including INTERSECT SQL operations. Download DbVisualizer for free now!

Frequently Asked Questions

What is INTERSECT in SQL?

In SQL, INTERSECT is a set operation that allows for the comparison of two SELECT queries, returning only the rows that are common to both. It effectively filters the result set to include only those records that exist in both queries, ensuring that any data returned meets the criteria specified in both SELECT statements.

What are the requirements for using the SQL INTERSECT operator?

To use the SQL INTERSECT operator, the SELECT statements must have matching column names, compatible data types, and the same number of columns.

Can INTERSECT be used with more than two tables?

Yes, INTERSECT in SQL can be used to compare more than two tables by chaining multiple INTERSECT operations.

How many DBMSs support INTERCEPT?

The SQL INTERSECT operator is supported by several relational database management systems (DBMSs). Here’s a list of some DBMSs that support it:

  • PostgreSQL
  • Oracle DB
  • MS SQL Server
  • MySQL (partial support by using the INNER JOIN clause)
Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26
title

What Is an SQL Query Builder and How Does It Work?

author Antonello Zanini tags SQL 8 min 2024-08-19
title

SQL LIMIT Clause: Complete Handbook

author Antonello Zanini tags MySQL POSTGRESQL SQL 8 min 2024-08-12
title

What Are Vector Databases?

author Lukas Vileikis tags BIG DATA SQL 6 min 2024-08-08

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗