SQL Query with numerous ORs

Question

I have a query like:

SELECT * 
    FROM myDB.Table
        WHERE KEY='a' OR
        WHERE KEY='b' OR
        WHERE KEY='c' OR ...

I need this to be done for about 150 table rows in a table with about 500,000. Is there a better/faster way to do this?

Answer 1

SELECT * FROM myDB.Table WHERE KEY IN ('a', 'b', 'c', etc...)

The IN operator allows you to specify multiple values in a WHERE clause.

Answer 2

If you have 150 values that you need to include in an IN statement, it may be more efficient to write a user defined function that takes a comma-separated list of values for the join, and returns it as a table variable, which can then be joined directly in your query.

Here is an example taken from this site http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx (although there are many others):

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput NVARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] NVARCHAR(10) )
AS
BEGIN

    DECLARE @String    NVARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END
GO

• Change the output key type (NVARCHAR(10)) and @String to match your key data type.

You'd then join on this table like this:

SELECT MyTable.*
FROM [myDB].[Table] MyTable 
INNER JOIN [myDB].[ufn_CSVToTable] ( 'a,b,c' ) KeyFilter
  ON MyTable.[KEY] = KeyFilter.[String]