SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN

INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator. INTERSECT operator returns almost same results as INNER JOIN clause many times.

When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.

Let us see understand how INTERSECT and INNER JOIN are related.We will be using AdventureWorks database to demonstrate our example.

Example 1: Simple Example of INTERSECT

SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (1,2,3)
INTERSECT
SELECT
*
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (3,2,5)

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN IntersectWithWhere

Solarwinds

Explanation:
The ResultSet  shows  the  EmployeeID  which  are  common in both the Queries, i.e  2 and 3.

Example 2:  Using simple INTERSECTbetween two tables.

SELECT VendorID,ModifiedDate
FROM Purchasing.VendorContact
INTERSECT
SELECT
VendorID,ModifiedDate
FROM Purchasing.VendorAddress

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN SimpleIntersect

Explanation:

The Resultset shows the records that are common in both the tables. It shows 104 common records between the tables.

Example 3:  Using INNER JOIN.

SELECT va.VendorID,va.ModifiedDate
FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID
AND vc.ModifiedDate = va.ModifiedDate

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN IntersectWithoutDistinct

Exlanation :
The resultset displays all the records which are common to both the tables. Additionally in example above INNER JOIN retrieves all the records from the left table and all the records from the right table. Carefully observing we can notice many of the records as duplicate records. When INNER JOIN is used it gives us duplicate records, but that is not in the case of INTERSECT operator.

Example 4:  Using INNER JOIN with Distinct.

SELECT DISTINCT va.VendorID,va.ModifiedDate
FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID
AND vc.ModifiedDate = va.ModifiedDate

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN IntersectWithDistinct

Explanation:
The resultset in this example does not contain any duplicate records as DISTINCT clause is used in SELECT statement. DISTINCT removes the duplicate rows and final result in this example is exactly same as example 2 described above. In this way, INNER JOIN can simulate with INTERSECT when used with DISTINCT.

Summary :

INNER JOIN can simulate with INTERSECT when used with DISTINCT.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Effect of Order of Join In Query
Next Post
SQLAuthority News – SQLAuthority Site With New Banner

Related Posts

85 Comments. Leave new

  • @pinal
    Hi sir,
    i have similar type of problem am unable solve.the problem is tblA and tblB having ID column in similar and some other columns. In that i want to select the values which were not ‘Intersect or Matched’.

    Reply
  • Hi Pinal, you have missed a key point which is the main difference between join and except .The JOIN treats two NULLS as two different values whereas the interesect treats two NULLs as a same value.

    Reply
  • Es una buena explicación para diferenciar intersect e inner join

    Reply
  • The Statement “The result set displays all the records which are common to both the tables” although technically right is misleading.

    The statement should be The Statement results in the display of all the records which are common to the result sets of both queries.

    The original explanation is confusing when trying to apply this to the bigger picture. The biggest difference between INNER JOIN and INTERSECT is that INNER JOIN’s operate with in the FROM part of a query which contributes to a result set. The INTERSECT operator determines how result sets (not tables) are joined to make a single specific result set.

    Raj, you could figure out the intersecting result set, use it as a sub query along with select * from X where not exists (INTERSECTING-SUBQUERY).

    Reply
  • Santosh Kumar
    June 24, 2019 4:27 pm

    how to find tables data between two tables select in mysql
    my query like this

    (select sum(feetime) as Duration,sum(fee) as Charge,customeraccount from e_cdr_20190612)between(select sum(feetime) as Duration,sum(fee) as Charge,customeraccount from e_cdr_20190623) where feetime>0 group by customeraccount

    please help me.

    Reply

Leave a Reply

Menu