You are here: Home / Blog / posts / Programming Magic / Emulating Oracle's Multi-Column IN Statement with MS SQL Server

Emulating Oracle's Multi-Column IN Statement with MS SQL Server

by admin published Jan 28, 2014 02:35 PM, last modified Jan 28, 2014 02:35 PM
This entry describes how to emulate Oracle's multi-column IN statement using an anonymous INNER JOIN 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.

Figure 1:

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>

 

When:

Where:

Contact