Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

'In' clause in SQL server with multiple columns

I have a component that retrieves data from database based on the keys provided. However I want my java application to get all the data for all keys in a single database hit to fasten up things. I can use 'in' clause when I have only one key.

While working on more than one key I can use below query in oracle

SELECT * FROM <table_name> 
where (value_type,CODE1) IN (('I','COMM'),('I','CORE'));

which is similar to writing

SELECT * FROM <table_name> 
where value_type = 1 and CODE1 = 'COMM' 

and

SELECT * FROM <table_name> 
where value_type = 1 and CODE1 = 'CORE' 

together

However, this concept of using 'in' clause as above is giving below error in 'SQL server'

ERROR:An expression of non-boolean type specified in a context where a condition is expected, near ','.

Please let know if their is any way to achieve the same in SQL server.

like image 466
Ravi Maurya Avatar asked Feb 04 '16 06:02

Ravi Maurya


People also ask

Can we use two columns in in clause?

If we need to query a table based on some set of values for a given column, we can simply use the IN clause. But if query need to be performed based on multiple columns, we could not use IN clause(grepped in SO threads.) From other SO threads, we can circumvent this problem using joins or exists clause etc.

How do I use multiple columns in SQL Server?

To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.

How do I select multiple columns based on condition in SQL?

When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause.


3 Answers

This syntax doesn't exist in SQL Server. Use a combination of And and Or.

SELECT * 
FROM <table_name> 
WHERE 
  (value_type = 1 and CODE1 = 'COMM')
  OR (value_type = 1 and CODE1 = 'CORE') 

(In this case, you could make it shorter, because value_type is compared to the same value in both combinations. I just wanted to show the pattern that works like IN in oracle with multiple fields.)


When using IN with a subquery, you need to rephrase it like this:

Oracle:

SELECT * 
FROM foo 
WHERE 
  (value_type, CODE1) IN (
    SELECT type, code 
    FROM bar
    WHERE <some conditions>)

SQL Server:

SELECT * 
FROM foo 
WHERE 
  EXISTS (
    SELECT * 
    FROM bar 
    WHERE <some conditions>
      AND foo.type_code = bar.type 
      AND foo.CODE1 = bar.code)

There are other ways to do it, depending on the case, like inner joins and the like.

like image 139
Stefan Steinegger Avatar answered Oct 11 '22 19:10

Stefan Steinegger


Normally you can not do it, but can use the following technique.

SELECT * FROM <table_name> 
where (value_type+'/'+CODE1) IN (('I'+'/'+'COMM'),('I'+'/'+'CORE'));
like image 3
Roohi Ali Avatar answered Oct 11 '22 17:10

Roohi Ali


If you have under 1000 tuples you want to check against and you're using SQL Server 2008+, you can use a table values constructor, and perform a join against it. You can only specify up to 1000 rows in a table values constructor, hence the 1000 tuple limitation. Here's how it would look in your situation:

SELECT <table_name>.* FROM <table_name> 
JOIN ( VALUES
    ('I', 'COMM'),
    ('I', 'CORE')
) AS MyTable(a, b) ON a = value_type AND b = CODE1;

This is only a good idea if your list of values is going to be unique, otherwise you'll get duplicate values. I'm not sure how the performance of this compares to using many ANDs and ORs, but the SQL query is at least much cleaner to look at, in my opinion.

You can also write this to use EXIST instead of JOIN. That may have different performance characteristics and it will avoid the problem of producing duplicate results if your values aren't unique. It may be worth trying both EXIST and JOIN on your use case to see what's a better fit. Here's how EXIST would look,

SELECT * FROM <table_name> 
WHERE EXISTS (
    SELECT 1
    FROM (
        VALUES
            ('I', 'COMM'),
            ('I', 'CORE')
    ) AS MyTable(a, b)
    WHERE a = value_type AND b = CODE1
);

In conclusion, I think the best choice is to create a temporary table and query against that. But sometimes that's not possible, e.g. your user lacks the permission to create temporary tables, and then using a table values constructor may be your best choice. Use EXIST or JOIN, depending on which gives you better performance on your database.

like image 3
Andrew Avatar answered Oct 11 '22 17:10

Andrew