• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server INTERSECT

SQL Server INTERSECT

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use INTERSECT operator to extract common records from a combination.

What is INTERSECT in SQL Server?

The INTERSECT operator is used to extract records which are common in the resultset of two or more SELECT queries distinctly (i.e. with the records represented only once in the resultset).

Prerequisites: The prerequisite for a successful INTERSECT is that the query result sets should have the same number of columns in the same order and with the same or similar datatype (i.e. the column datatypes should be either exactly same or it should be possible to convert one datatype into another without casting error).

The INTERSECT operation can be pictorially represented as below.

SQL Server Intersect

Operation

As discussed above the INTERSECT operator can only be used with SELECT queries and the query result sets should be of same size.

SQL Server INTERSECT Syntax

The basic syntax of the INTERSECT operator is given below.

INTERSECT syntax

SELECT_QUERY_1
INTERSECT
SELECT_QUERY_2;

In this syntax,

  • SELECT_QUERY – SQL query which selects rows of information (i.e. records or tuples) from a table. It can be a simple or complex query with conditions.
  • INTERSECT – SQL keyword to combine query result sets of two or more queries and extract the common records distinctly.

SQL Server INTERSECT Examples

Let us consider an example to understand its practical usage and implications. Suppose we have two tables – a table called persons and another table called customers. The persons table contains prospect information and the customers table contains customer information. Data wise the customers table is a subset of the persons table with the persons table containing 10 records and the customers table containing 8 records. The sample tables are shown below. Please note that the column names are different in the tables and that the persons table is one column short. But however, it does not violate the prerequisites since they relate to the resultset and not to the table. The tables can have dissimilar number of columns and column names.

first_namelast_namepers_city
HerbertEinsteinNew York
JackieFrostSeattle
StephenGeorgeWashington
JamesMareNew York
AngelReevesDallas
SelenaSpearsDetroit
SammyLouiseDallas
BrandonPowellSeattle
KimFoxWashington
ValCostnerNew York
Table: persons
fnamelnamecitycontact
StephenGeorgeWashington16553120210
JamesMareNew York16277212992
AngelaCrawfordWashington16633775159
SelenaSpearsDetroit16616165325
PattyCampbellDetroit16107575525
BrandonPowellSeattle16101110778
KimFoxWashington16529929936
EdithPoeSeattle16767335231
Table: customers

1) SQL Server INTERSECT – INTERSECT example

We will do an INTERSECT between the SELECT queries retuning the first and last names from the two tables.

SELECT first_name, last_name FROM persons
INTERSECT
SELECT fname, lname FROM customers;

It will run successfully and generate the following resultset with the column names from the first table persons as the column heading.

first_namelast_name
BrandonPowell
JamesMare
KimFox
SelenaSpears
StephenGeorge

Now let us try to run the following INTERSECT query with the participating SELECT queries returning all columns from the tables. As we can see below the query fails with error because of * trying to fetch all columns from the tables and the numbers of columns being different – 3 in the persons table and 4 in customers.

SELECT * FROM persons
INTERSECT
SELECT * FROM customers;

Msg 205, Level 16, State 1, Server MY-HP245G6, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Was this tutorial helpful?
YesNo
« Previous: SQL Server COALESCE
SQL Server MERGE :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.