Interview Questions and Answers
What is the difference between INTERSECT and INNER JOIN
What is the difference between INTERSECT and INNER JOIN
1. INTERSECT filters duplicates and returns only DISTINCT rows that are common between the LEFT and Right Query, where as INNER JOIN does not filter the duplicates.To understand this difference, insert the following row into TableA
Now execute the following INTERSECT query. Notice that we get only the DISTINCT rows
Result :
![intersect operator in sql example](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY6AwR5aNduxXwTALX7TAxbiTachkhwUYPC-gcP7uaDYUHko0SspX5fnFrpVRleS1jNvwSWG0GSjUq-S4KjbX8mxJ72C93-mFBprTUMDjBuigF61rlbU2BTJCBMZRq7vnfLl3marKs79U/s1600/intersect+operator+in+sql+example.png)
Now execute the following INNER JOIN query. Notice that the duplicate rows are not filtered.
Result :
![inner join sql duplicate rows](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyCNU1EooZe-lXPsuW_P59RNYApoeQwdCQQsEiqty-3rGc0WhIvhAnFObH0Invj0bUVbDb4bvuBnOdKdfutZkqwFPhPqRzO3KWZHVYG0Cp4xvKc7NCPrI1iamLwzIcwUk1JowJ6Lju0Gc/s1600/inner+join+sql+duplicate+rows.png)
You can make the INNER JOIN behave like INTERSECT operator by using the DISTINCT operator
Result :
![inner join remove duplicate rows](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjiXUznZcoG2NN4psuhjBksZbVV4rCHrCo_L8L0813whq1Duw5K8AyC0btBjTQHmY-felEMby_7mkMUVopYhQBs1fCi6jLhu4DvXtuZ2ht1ObN2wUiqaWPFY8UXFI44_kW8VagRkdcxmk/s1600/inner+join+remove+duplicate+rows.png)
2. INNER JOIN treats two NULLS as two different values. So if you are joining two tables based on a nullable column and if both tables have NULLs in that joining column then, INNER JOIN will not include those rows in the result-set, where as INTERSECT treats two NULLs as a same value and it returns all matching rows.
To understand this difference, execute the following 2 insert statements
INTERSECT query
Result :
![sql intersect null values](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAJX491yhLv1g2A01gpWdbPMa9mx8D1cwOxmmNkfSTiQbpOmZrc-SgTgPZ1MqomgX5007SCtWRGukSThFZgV9JznwdtCt4FGhANy-MgxrO041vfBjzFyTidlKwU25Wg0CffQaz9WnLfKE/s1600/sql+intersect+null+values.png)
INNER JOIN query
Result :
![inner join null values](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCC_saPpCOBDGaZY4oqcwO9fFuAUtehjM7bcCwofshyphenhypheniqwlC5EKSJeim_o-K8gFFBphiS9rBUiFcjTDyCql6xSa74Jt9ixtM-PefzpGgNOibKvL9MBpZCqApr8MBMLGv5Cw5dakBpPaj8/s1600/inner+join+null+values.png)
Insert into TableA values (2, 'Mary', 'Female')
Now execute the following INTERSECT query. Notice that we get only the DISTINCT rows
Select Id, Name, Gender from TableA
Intersect
Select Id, Name, Gender from TableB
Result :
![intersect operator in sql example](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY6AwR5aNduxXwTALX7TAxbiTachkhwUYPC-gcP7uaDYUHko0SspX5fnFrpVRleS1jNvwSWG0GSjUq-S4KjbX8mxJ72C93-mFBprTUMDjBuigF61rlbU2BTJCBMZRq7vnfLl3marKs79U/s1600/intersect+operator+in+sql+example.png)
Now execute the following INNER JOIN query. Notice that the duplicate rows are not filtered.
Select TableA.Id, TableA.Name, TableA.Gender
From TableA Inner Join TableB
On TableA.Id = TableB.Id
Result :
![inner join sql duplicate rows](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyCNU1EooZe-lXPsuW_P59RNYApoeQwdCQQsEiqty-3rGc0WhIvhAnFObH0Invj0bUVbDb4bvuBnOdKdfutZkqwFPhPqRzO3KWZHVYG0Cp4xvKc7NCPrI1iamLwzIcwUk1JowJ6Lju0Gc/s1600/inner+join+sql+duplicate+rows.png)
You can make the INNER JOIN behave like INTERSECT operator by using the DISTINCT operator
Select DISTINCT TableA.Id, TableA.Name, TableA.Gender
From TableA Inner Join TableB
On TableA.Id = TableB.Id
Result :
![inner join remove duplicate rows](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjiXUznZcoG2NN4psuhjBksZbVV4rCHrCo_L8L0813whq1Duw5K8AyC0btBjTQHmY-felEMby_7mkMUVopYhQBs1fCi6jLhu4DvXtuZ2ht1ObN2wUiqaWPFY8UXFI44_kW8VagRkdcxmk/s1600/inner+join+remove+duplicate+rows.png)
2. INNER JOIN treats two NULLS as two different values. So if you are joining two tables based on a nullable column and if both tables have NULLs in that joining column then, INNER JOIN will not include those rows in the result-set, where as INTERSECT treats two NULLs as a same value and it returns all matching rows.
To understand this difference, execute the following 2 insert statements
Insert into TableA values(NULL, 'Pam', 'Female')
Insert into TableB values(NULL, 'Pam', 'Female')
INTERSECT query
Select Id, Name, Gender from TableA
Intersect
Select Id, Name, Gender from TableB
Result :
![sql intersect null values](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAJX491yhLv1g2A01gpWdbPMa9mx8D1cwOxmmNkfSTiQbpOmZrc-SgTgPZ1MqomgX5007SCtWRGukSThFZgV9JznwdtCt4FGhANy-MgxrO041vfBjzFyTidlKwU25Wg0CffQaz9WnLfKE/s1600/sql+intersect+null+values.png)
INNER JOIN query
Select TableA.Id, TableA.Name, TableA.Gender
From TableA Inner Join TableB
On TableA.Id = TableB.Id
Result :
![inner join null values](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCC_saPpCOBDGaZY4oqcwO9fFuAUtehjM7bcCwofshyphenhypheniqwlC5EKSJeim_o-K8gFFBphiS9rBUiFcjTDyCql6xSa74Jt9ixtM-PefzpGgNOibKvL9MBpZCqApr8MBMLGv5Cw5dakBpPaj8/s1600/inner+join+null+values.png)
Post a Comment
0 Comments