Emulating Oracle's Multi-Column IN Statement with MS SQL Server
How to emulate standard SQL's multi-column "IN" statement using a Microsoft SQL Server:
The standard SQL "IN" statement is very handy; however, MS SQL does not support the SQL standard that allows for multiple columns as does other database servers such as Oracle.
Therefore, the SQL standard multi-column IN statement, whilst perfectly acceptable in Oracle (Figure 1) fails altogether if used with an MS SQL Server. However, not to worry, it can be re-written using an anonymous INNER JOIN (Figure 2) to achieve similar results.
Note that since the IN portion of the WHERE clause has been replaced by the anonymous INNER JOIN, the corresponding filter criteria has been removed from the WHERE clause, and only the "<other_filter_criteria>" remains. This is because it has been accounted for by the "ON" portion of the INNER JOIN to achieve the same results.
SQL Standard Multi-Column "IN" statement:
SELECT * FROM <table_name> WHERE (column1, column2, ..., columnN) IN ( SELECT DISTINCT column1, column2, ..., columnN FROM <other_table_name> WHERE <where_clause> ) [AND <other_filter_criteria>...]
Figure 2: Multi-Column "IN" statement simulated BY using AN Anonymous INNER JOIN:
SELECT * FROM <table_name> T1 INNER JOIN ( SELECT DISTINCT column1, column2, ..., columnN FROM <other_table_name> WHERE <where_clause> ) T2 ON T1.column1 = T2.column1 AND T1.column2 = T2.column2... AND T1.columnN = T2.columnN WHERE <other_filter_criteria>