 |
| Oracle Database vs Microsoft SQL Server |
Coming from an Oracle Database background, I had a tough time getting used to some keywords in SQL Server. While most of the keywords are ANSI compliant, there are some keywords that are unique in SQL Server database. This post is to discuss one such exception with the MINUS set operator keyword which was available in Oracle, but not in SQL Server.
Issue: MINUS keyword not working in Microsoft SQL Server. I had this particular scenario, where in I was rewriting an existing query and trying to performance tune it. To make sure the same records are returned when I make changes to queries, I use the MINUS keyword in Oracle to subtract the new query from the existing one and if no rows are returned I use the new query going forward.
I was trying to run a query similar to below,
SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME
MINUS
SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME
Ideally the query should give me an output with no rows as I am trying to subtract same query from itself. Instead of returning the desired output, SQL server will show me two result sets individually executed without returning an error.
Cause: This is because SQL Server doesn't recognize the keyword MINUS.
Solution: The solution is to use the SQL Server equivalent of MINUS set operation which is the EXCEPT keyword. Below is the restructured query for the same using the EXCEPT keyword.
SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME
EXCEPT
SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME
The except keyword basically functions to return unique records in query on top(or left) that doesn't have an identical record in the query on the right side(bottom).
Things to take care when using the EXCEPT keyword are,
- All the data sets(queries) should have identical number of arguments.
- Once the number of arguments are same between different queries, the next check is on the datatype of the arguments. The datatype order in different query sets should be same.
