Difference between INTERSECT and INNER JOIN?
|
Sno |
Intersect
|
Inner join
|
|
1
|
It
is SET operator
|
It
is JOIN operator
|
|
2 |
It
return duplicates
|
It
remove duplicates
|
|
3 |
It never return NULL values, If exists |
It return NULL values, If exists
|
|
4 |
It matches Limited set of columns |
It compares all rows between two sets |
Examples:
CREATE TABLE #a (id INT)
CREATE TABLE #b (id INT)
INSERT INTO #a VALUES (1), (NULL), (2)
INSERT INTO #b VALUES (1), (NULL), (3), (1)
select * from #a select * from #b
| #A | #B |
| id | id |
| 1 | 1 |
| NULL | NULL |
| 2 | 3 |
| 1 |
1)select #a.id from #a INNER JOIN #b on #a.id=#b.id
o/p) 1
1
2) select * from #a INTERSECT select * from #b
o/p) NULL
1
Comments
Post a Comment