Today's Offer - SQL Server Certification Training - Enroll at Flat 20% Off.

- SQL Server Blogs -

SQL Intersect Operator With Example

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 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. The frequently used set operators are:

  • UNION
  • UNION ALL
  • MINUS
  • INTERSECT
  • EXCEPT

Let’s take a look at each of these. Here, our main focus is to discuss the Intersect operator in detail, and 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:


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.

SQL Server Curriculum

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 query before the set operator.

Intersection Operator in SQL

The Intersect 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.

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 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 can be given as:

Intersection Operator in SQL

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 with a single expression and the same data types.

Read: What Does SQL Stand For? The Structured Query Language Explained

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

Here, 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 can we add where condition to the given an example.


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 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 < 50;

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

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.

SQL Server quiz

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 “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 Intersect operator.


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 how the above code looks like when an AND operator replaces 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: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

SQL Intersect Operator - Equivalence

The Intersection operator was not a part of SQL server since the time of its inception, but it was introduced later. Before 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 table to itself 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.

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.
  • 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.

Union Operator in SQL

Here is the basic syntax for the UNION operator:


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 similar to the UNION operator, but it does not work on duplicate values. Let us see the Venn diagram for the UNION ALL operator:

UNION ALL Operator in SQL

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

Read: All you need to know about SQL Replace Function

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.

As a result, UNION is slower than UNION ALL operator and little costlier too. UNION performs distinct operator that takes 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 Operator in SQL

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

MINUS Operator in SQL

The basic syntax for this operator is given below.


FROM first_name, last_name
FROM customer
MINUS
SELECT first_name, last_name
FROM employee;
free SQL Server demo

Except for Operator in SQL

The except operator is another popular set operator that is frequently used with SQL code lines. It works similar to MINUS showing result from one query that doesn’t exist in another query. However, MINUS is an Oracle-specific keyword while Except keywords can be used 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:

Except for Operator in SQL

Final Words:

With this discussion, we come to the end of our blog where we learned different set operators keeping Intersect operator in focus. You can use them with your database when you want to combine two or more queries. Don’t forget to follow tips that we have discussed in the 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: MSBI Interview Questions & Answers for Fresher, Experienced

    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

9 days 14 Dec 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-0 day 05 Dec 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

-0 day 05 Dec 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

1 day 06 Dec 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

15 days 20 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

19 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

4 days 09 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

1 day 06 Dec 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

4 days 09 Dec 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews