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