Yesterday, I’m asked by my friend about SQL Server script / clause for script like “minus” in ORACLE. As a beginner in SQL Server, of course I don’t know and suggesting him to search in internet. Because of that, I search in internet and find 2 interesting clause, EXCEPT & INTERSECT.
What a difference between “EXCEPT” & “INTERSECT” ?
Please execute below script
[code language=”sql” title=”Sample EXCEPT”]
SELECT * FROM (
SELECT ‘arif’ AS column1, 1 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 2 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 3 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 4 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 5 AS column2
) a
EXCEPT
SELECT ‘arif’, 1
[/code]
[code language=”sql” title=”Sample INTERSECT”]
SELECT * FROM (
SELECT ‘arif’ AS column1, 1 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 2 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 3 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 4 AS column2
UNION ALL
SELECT ‘arif’ AS column1, 5 AS column2
) a
INTERSECT
SELECT ‘arif’, 1
[/code]
You may see 2 different results between above script. For the first script, it will give output 4 rows whereas the second script only 1 row. It show that EXCEPT will remove data which is has same value with him whereas INTERSECT will remove data which has different value.
I hope it help.
Marifnst, 2013-06-03
Leave a Reply