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:
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:
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:
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:
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)
85 Comments. Leave new
Es una buena explicación para diferenciar intersect e inner join
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).
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.
Post some sample data with expected result