SQL Intersect Operator With Example

Introduction

A set operator in SQL is a keyword that is used to combine common results from both queries into a single set. Sometimes, when working with SQL server, you have to work on multiple tables together. Instead of combining both tables, you may combine common values to make it a little more relevant, and it is done through set operators in SQL. There are different types of set operators that are used by vendors, and you can pick any one of them as per your requirements. Here, we will majorly discuss intersect SQL operator with example and Minus SQL operator.

The frequently used set operators are:

• UNION
• UNION ALL
• MINUS SQL
• INTERSECT
• EXCEPT

Let’s take a look at each of these. Here, our main focus is to discuss the Intersect SQL operator in detail, and the rest will be discussed in brief. Take a look at how to use set operators in SQL. Here is the basic syntax for your reference:

Read: What Is Candidate Key SQL? [Primary Key vs Candidate Key]

SELECT your_select_query
Set_operator
SELECT another_select_query;

It uses two or more select queries with a set operator in between. There are a few things to consider when working with set operators in SQL. When selecting columns, the number of columns should be matched between queries and data types should always be compatible. For example, if you have taken three columns for the first query, then it should be 3 for the second query as well. The data type should also be the same.

For example, if you are using the character data type for the first query, then it should be the same for another as well. Also, if you want to arrange the final set in order then use the Order by clause at the end of the query before the set operator.

Intersect SQL Operator in Oracle

The Intersect SQL operator is used to combine similar rows from two queries. It returns rows that are common between both results. To use Intersect operator in SQL, both queries should use the same length for columns and it must-have compatible data types too. Let us understand the concept with the help of a simple example.

Read: What is SQL Formatter? Features of SQL Formatter Online

Here, we have to work with two queries. The Orange circle is the first query, and the Blue circle is the second query. Now, use the Intersect operator and combine these two queries. A common green area of two circles is the final result after using the intersect operator keeping only rows in common to both queries. It is very much similar to the UNION operator, and we can use the same rules for both operators, but MYSQL does not support the intersect operator. So, it can be used with SQL Server only. The Venn Diagram for Intersect Operator in SQL can be given as:

The basic syntax of SQL intersect can be given as:

SELECT column1 [,column2 ]
FROM table1 [,table2 ]
[WHERE coondition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here, the given condition can be any expression based on the requirement. Add the table names from which you want to retrieve the data. Where the condition is optional, and it is generally used to make your search very specific.

Points to Remember: Keep in mind that there should be the same number of expressions in both SELECT statements. Also, expressions should have the same data type that you want to club together.

SQL Intersect Example – With a Single expression

Here is a simple example for Intersect operator in SQL with a single expression and the same data types.

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

Here, the Supplier ID is the common column in both the tables, suppliers, and orders. The same will appear in the result set too. Now, let us complicate the example using WHERE condition to the Intersect Query. Let us see how we can add where condition to the given example.

Read: Advanced SQL Server Interview Questions and Answers

SELECT supplier_id
FROM suppliers
WHERE supplier_id > 78
INTERSECT
SELECT supplier_id
FROM orders
WHERE quantity <> 0;

Here, where the condition is added to both expressions. For the first expressions, it will pick records having supplier ID greater than 78. For the second expression, it will select rows from the Orders table whose quantity is not equal to Zero. In this way, where the condition is useful when you want to pick highly specific data.

SQL Intersect Example – With Multiple expression

Now let us look at how to use SQL Intersect operator to return more than one column. For example:

SELECT contact_id, last_name, first_name
FROM contacts
WHERE last_name <> 'Anderson'
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE customer_id 

In this way, you can use multiple expressions together and set the where condition to make the search more specific and relevant.

Read: Top 75 MySQL Interview Questions and Answers (2024) for Experienced & Freshers

SQL Intersect Example – Using Order by Clause

Here is an example of SQL Intersect Operator where Order By clause has been used.

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
INTERSECT
SELECT company_id, company_name
FROM companies
WHERE companny_id > 1000
Order by 2;

Since column names are different for both the tables, it would be great using Order by clause here. In the above example, we have sorted results by arranging columns in ascending order. You have to be little logical when working with multiple operators or clauses together otherwise it may result in errors in the end.

Request for a demo class now to know all about SQL operators.

Intersect vs. AND Operator

The intersect operator is good when you want to find two common rows between two results. The Intersect operator is highly similar to the AND operator, and they usually work on different database objects. The “Intersect” operator is used to combine entire rows while the “AND” operator is used to combine columns within rows. Let us understand both operators with the help of an example below. Here is one query for makes and other query is for females.

SELECT Jobtitle
FROM HumanResources.Employee
WHERE Gender ='M'

To finish, we have to find out titles in common, and it can be quickly done by an Intersect operator.

Read: All About SQL Joins and Subqueries

SELECT Jobtitle
FROM HumanResources.Employee
WHERE Gender ='M'
INTERSECT
SELECT Jobtitle
FROM HumanResources.Employee
WHERE Gender ='F'

The same example can be written using AND operator too. Let us see what the above code looks like when an AND operator replaces the intersect operator.

SELECT Jobtitle
FROM HumanResources.Employee
WHERE Gender ='M'
AND Gender ='F'

It looks much simpler, but it won’t work because the where clause is evaluated for each row, and you are never going to find a Gender value equal for both male and female expressions.

Read: How to Download and Install Microsoft SQL Server Data Tools?

SQL Intersect Operator - Equivalence

The Intersection operator was not a part of the SQL server since the time of its inception, but it was introduced later. Before the Intersection operator, the inner join clause was used for the same purpose. Below is the equivalent statement to find the common job title in both queries.

SELECT DISTINCT M.JobTitle
FROM HumanResources.Employee As M
Inner JOIN
HumanResources.Employee AS F
On M.JobTitle = F.Jobtitle
AND M.Gender ='M'
AND F.Gender = 'F'

It is called the self-join where we are joining a table to match up job titles with the same values. Keep in mind that Null is not the value; it will always return False. The major difference between Inner join and Intersect operators is that Inner join does not consider Null values while Intersect operator does match Nulls.

Read: What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

Major Differences

• Intersect is an operator while Inner join is not an operator but a type of join.
• Inner join does not consider Null values while Intersect operator does match Nulls.
• The intersect operator does not return any duplicate value while Inner joins return NULL values if it is available.
• Intersect work on the entire row, but Inner join works on selected columns.
• Intersect creates a temporary table, but Inner join works on the actual table only.
• Sometimes, the final result set is 100 percent the same for both operators.

Union Operator in SQL

The union operator is used to combine results from two queries; it removes duplicate values too from the table. Here is a Venn Diagram for the Union Operator where each circle is a query result.

Here is the basic syntax for the UNION operator:

Read: SSRS Pie Chart - Having a Slice of the Pie

FROM first_name, last_name
FROM customer
Union
SELECT first_name, last_name
FROM employee;

The UNION and JOIN work similar to a single objective of joining two tables together. The difference is, how are they joining the final set. UNION combines data in separate rows while JOIN combines data in separate columns.

UNION ALL Operator in SQL

This operator also combines the result from two queries. It works similarly to the UNION operator, but it does not work on duplicate values. Let us see the Venn diagram for the UNION ALL operator:

The basic syntax for UNION ALL operator can be given as:

FROM first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee;

How are UNION and UNION ALL operators different?

The major difference between the two operators is that the UNION operator removes duplicate values while UNION ALL does not work on duplicate values. UNION operator performs a DISTINCT operation on the result set to eliminate duplicates. Keep in mind that ALL means you want to combine all records.

Read: Difference Between SQLite and MySQL

As a result, UNION is slower than UNION ALL operator and a little costlier too. UNION performs distinct operators that take time and resources while there is no DISTINCT operator in the case of UNION ALL operator. So, if you are sure that rows are unique in both the tables, then you should try UNION ALL in that case instead of UNION.

MINUS in SQL Server

Another common set operator in Minus SQL. The Minus in SQL Server will find the result present in the first query bit not available in the second query. The Venn diagram for Minus SQL operator can be given as:

The basic syntax for this operator is given below.

FROM first_name, last_name
FROM customer
MINUS
SELECT first_name, last_name
FROM employee;

Except for Operator in SQL

The except operator is another popular set operator that is frequently used with SQL code lines. It works similarly to MINUS showing results from one query that doesn’t exist in another query. However, MINUS is an Oracle-specific keyword while Except keywords can be used in other databases like SQL Server. So, if you see EXCEPT operator somewhere then keep in mind that it is similar to MINUS but for a different database. The Venn diagram for the EXCEPT operator can be given as:

Sign up for online SQL training classes today to master the SQL skills.

Final Words

With this discussion, we come to the end of our blog where we learned different set operators keeping SQL Intersect operator and Minus SQL operator in focus. You can use Intersect SQL operator with your database when you want to combine two or more queries. Don’t forget to follow the tips that we have discussed in the SQL Intersect blog, or the final result set can be redundant. If you are still not sure, join the SQL Server Certification course with us and learn using each of the operators practically. All the Best for practical learning to SQL Set Operators with JanBask Training.

Read: Difference Between Stored Procedure and Function in SQL Server

FaceBook Twitter Google+ LinkedIn Pinterest Email