Select Query by Pair of Fields Using an in Clause

SQL multiple columns in IN clause

You could do like this:

SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));

The sqlfiddle.

SQL WHERE.. IN clause multiple columns

You can make a derived table from the subquery, and join table1 to this derived table:

select * from table1 LEFT JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL

MySQL multiple columns in IN clause

To make effective use of the index, you could rewrite the IN predicate

(x0, y0, x1, y1) IN ((4, 3, 5, 6),(9, 3, 2, 1))

Like this:

(  ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 ) 
OR ( x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 )
)

In' clause in SQL server with multiple columns

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.

In clause for a list of pair of conditions

Section 4.6.9 of the JPA specification makes it clear that this is not supported by JPQL, at least not in the form of an in-clause:

4.6.9 In Expressions
The syntax for the use of the comparison operator [NOT] IN in a conditional expression is as follows:

in_expression ::=
    {state_valued_path_expression | type_discriminator} [NOT] IN
    { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter } 

in_item ::= literal | single_valued_input_parameter

The state_valued_path_expression must have a string, numeric, date, time, timestamp, or enum value.

The literal and/or input parameter values must be like the same abstract schema type of the state_valued_path_expression in type. (See Section 4.12).

The results of the subquery must be like the same abstract schema type of the state_valued_path_expression in type.

It just doesn't operate on tuples.

Your best bet is probably to create a Specification to construct the combination of AND and OR you require. See this blog article how to create Specifications

Parameterized IN clause using multiple columns

All you have to do is to pass a list of arrays, where each array contains a key and value, like this:

HashMap<String , String > map = new HashMap<>();
map.put("key0", "value0");
map.put("key1", "value1");
Set<String> keys = map.keySet();
List<String[]> valuesMap = new ArrayList<>();
for(String key:keys){
    String[] entry = {key,map.get(key)};
    valuesMap.add(entry);
}
String sql = "select * from mytable where (key, value) in (values :valuesMap)";
SqlParameterSource params = new MapSqlParameterSource("valuesMap", valuesMap);
jdbcTemplate.query(sql, params, rowMapper);

This is mentioned in the Spring documentation: http://docs.spring.io/spring-framework/docs/current/spring-framework-reference/html/jdbc.html#jdbc-in-clause

Select where in multiple pairs - postgresql

I checked my database and it should return 8 rows but it is returning an empty table

Your check is wrong. The query is doing what you intend.

One possibility is that the names are incorrect because they have additional characters -- perhaps hidden. You can easily check this. Does this return anything?

SELECT *
FROM table
WHERE name = 'john';

If this returns no rows and you think it should, then you need to figure out what is going wrong. Does this condition work?

WHERE name LIKE '%john%'

The issue could be spaces at the beginning of the name, hidden characters, or look-alike characters.

If the first query does return rows, then the problem could be with age. Does this return anything?

SELECT *
FROM table
WHERE name = 'john' AND age = 23;

A problem with age is more subtle, but one possibility is a floating point representation, where the value is really 22.9999999997 rather than 23.

Efficiently select rows in SQL server using list of pairs?

You should pass the values using correct types. One method is to construct a derived table and use join:

select t.*
from table t join
     (values (2261, 7807403), . . . 
     ) v(id1, id2)
     on t1.id1 = v.id1 and t.id2 = v.id2;

You should not be converting values to strings for comparison. That is not only "ridiculous", but it can seriously impede performance.

As for NULLs, they will not match unless you have special logic. SQL does not have a null-safe comparison.

select t.*
from table t join
     (values (2261, 7807403), . . . 
     ) v(id1, id2)
     on (t1.id1 = v.id1 or t1.id1 is null and t2.id2 is null) and 
        (t.id2 = v.id2 or t1.id1 is null and t2.id2 is null);

Note that this might affect the execution plan.

---

Related Topics