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

Popular posts from this blog

Difference between truncate and delete in sql server with example

DELETE: 1. Delete is a DML command. 2. Delete statement is executed using a row lock, each row in the table is locked   for   deletion.          3. We can specify filters in where clause 4. It deletes specified data if where condition exists. 5. Delete activates a trigger because the operation are logged individually. 6. Slower than truncate because, it keeps logs. 7. Rollback is possible. 8.Table identity column is reset to seed value TRUNACTE: 1. Truncate is a DDLcommand. 2. Truncate table always locks the table and page but not each row. 3. Cannot use where condition. 4. It removes all the data. 5. truncate table cannot activate a trigger because the operation does not log      individual row deletions. 6. Faster in performance wise, because it does not keep any logs. 7. Rollback is not possible. 8.Table identity column is not reset *Delete and truncate both can be rolled back whe...
Read more