SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.
Intersect will be used to intersect two or more tables and get the matching rows for both the tables. For that both the tables the order of the columns should have common columns to match.
INTERSECT operator in SQL Server 2005 is used to get the matched 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.
Both intersect and inner join can be used for commonly used in some places.
Let us see an example to understand better.
SELECT * FROM userdomain WHERE UserID IN (4,6,7)
INTERSECT
SELECT * FROM * FROM userdomain WHERE UserID IN (4,6,10)
Result will be userid which common in both the places.
Inner join will be used to retrieve value which is common to both the tables. In Inner joins by default it will return the duplicate values also where as in intersect it will show the default values.
For Ex:
SELECT d.Userid,d.domainid FROM UserTable u
INNER JOIN Domaintable d ON u.Userid = d.Userid
In inner join also we can avoid duplicate by use distinct keyword to void duplicate values and inner joins with distinct keyword will avoid duplicate values.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|