EXCEPT & INTERSECT in SQL Server – Same as MINUS & UNION in Oracle

Need to run a query to find the distinct different rows in an oracle table compared to a SQL server table. These tables are comparable because the source SQL Server table is replicated to the Oracle Schema.

Note: This example shows the use of Except in a more complex scenario but it can be used in its simplest form to return results from 2 SQL server tables (with the proviso that they are comparable tables)

MSDN – EXCEPT and INTERSECT

This SQL requires a link server to be in place linking to the oracle schema with select permissions.

SELECT Person_ID, Misc_Data_ID
FROM OPENQUERY(ORA_TEMP, 'select * from MYSchema.misc_data')
EXCEPT
SELECT Person_ID, Misc_Data_ID 
FROM misc_data
  WHERE (Person_ID IN 
    (SELECT Person_ID FROM Person 
    WHERE (Customer_ID IN 
        (SELECT Customer_ID FROM Customer WHERE Business_Subset_ID = 123456))))

In the above SQL, EXCEPT finds distinct rows which are in the oracle schema which are NOT in the SQL table. To make sure that we are returning exact rows, i use the primary key on the oracle table for the except comparison.

INTERSECT works in the same way, but finds all common rows between the 2 tables.

SELECT Person_ID, Misc_Data_ID
FROM OPENQUERY(ORA_TEMP, 'select * from MYSchema.misc_data')
INTERSECT
SELECT Person_ID, Misc_Data_ID 
FROM misc_data
  WHERE (Person_ID IN 
    (SELECT Person_ID FROM Person 
    WHERE (Customer_ID IN 
        (SELECT Customer_ID FROM Customer WHERE Business_Subset_ID = 123456))))

EXCEPT is doing the same functionality as the MINUS operation in Oracle, whereas INTERSECT is the same as the UNION operation.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: