Stack Overflow
Stack Overflow

Questions tagged [where-in]

An SQL-standard condition of the form WHERE SOME_COLUMN IN (1,2,3) or using a subquery to create the list, eg WHERE SOME_COLUMN IN (SELECT X FROM MYTABLE WHERE Y)

For example:

SELECT *
FROM MY_TABLE
WHERE MY_COLUMN IN (1,2,3,5,8,13)

or commonly using a subquery to generate the list:

SELECT *
FROM MY_TABLE
WHERE MY_COLUMN IN (
    SELECT SOME_COLUMN FROM SOME_TABLE WHERE <some condition>
)

The subquery version is a source on many performance problems, because most optimizers do not optimize this properly.

Most IN (subquery) queries can be rewritten to use a join that does perform well.

497 questions
629
votes
23 answers

Can I bind an array to an IN() condition in a PDO query?

I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition. I'd like to be able to do something like…
Andru
  • 7,011
  • 3
  • 21
  • 25
164
votes
11 answers

MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

I've got a couple of duplicates in a database that I want to inspect, so what I did to see which are duplicates, I did this: SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1 This way, I will get all rows with…
quano
  • 18,812
  • 25
  • 97
  • 108
127
votes
5 answers

Python/psycopg2 WHERE IN statement

What is the correct method to have the list (countryList) be available via %s in the SQL statement? # using psycopg2 countryList=['UK','France'] sql='SELECT * from countries WHERE country IN (%s)' data=[countryList] cur.execute(sql,data) As it is…
Matt
  • 7,022
  • 16
  • 53
  • 66
54
votes
3 answers

NHibernate using QueryOver with WHERE IN

I would create a QueryOver like this SELECT * FROM Table WHERE Field IN (1,2,3,4,5) I've tried with Contains method but I've encountered the Exception "System.Exception: Unrecognised method call: System.String:Boolean…
Faber
  • 2,194
  • 2
  • 27
  • 36
41
votes
4 answers

MySQL - SELECT ... WHERE id IN (..) - correct order

I have the following query SELECT * FROM table WHERE id IN (5,4,3,1,6) and I want to retrieve the elements in the order specified in the id in.., meaning it should return: 5 .... 4 .... 3 .... 1 .... 6 .... Any ideas on how to do that?
Ciprian Mocanu
  • 2,166
  • 3
  • 25
  • 44
39
votes
5 answers

MySQL multiple columns in IN clause

I have a database with four columns corresponding to the geographical coordinates x,y for the start and end position. The columns are: x0 y0 x1 y1 I have an index for these four columns with the sequence x0, y0, x1, y1. I have a list of about a…
nbeuchat
  • 6,575
  • 5
  • 36
  • 50
30
votes
7 answers

SQL use CASE statement in WHERE IN clause

Is it posible to use case in where in clause? Something like this: DECLARE @Status VARCHAR(50); SET @Status='published'; SELECT * FROM Product P WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3) …
POIR
  • 3,110
  • 9
  • 32
  • 48
24
votes
13 answers

LINQ to Entities - where..in clause with multiple columns

I'm trying to query data of the form with LINQ-to-EF: class Location { string Country; string City; string Address; … } by looking up a location by the tuple (Country, City, Address). I tried var keys = new[] { new {Country=…,…
millimoose
  • 39,073
  • 9
  • 82
  • 134
17
votes
13 answers

SQL IN condition in Java

I have multiple conditions to check as shown below, if(pouch.getStatus().equals("Finalized") || pouch.getStatus().equals("Ready") || pouch.getStatus().equals("Checkout") || pouch.getStatus().equals("Confirmed") ||…
Santosh Jadi
  • 1,479
  • 6
  • 29
  • 55
14
votes
6 answers

Passing an array to sqlite WHERE IN clause via FMDB?

Is it possible to pass an array to a SELECT … WHERE … IN statement via FMDB? I tried to implode the array like this: NSArray *mergeIds; // An array with NSNumber Objects NSString *mergeIdString = [mergeIds componentsJoinedByString:@","]; NSString…
Stefan
  • 1,051
  • 3
  • 11
  • 23
13
votes
3 answers

MySQL select join where AND where

I have two tables in my database: Products id (int, primary key) name (varchar) ProductTags product_id (int) tag_id (int) I would like to select products having all given tags. I tried: SELECT * FROM Products JOIN ProductTags ON…
Darrarski
  • 3,882
  • 6
  • 37
  • 59
12
votes
3 answers

MySQL specify arbitrary order by id

Is it possible to specify an arbitrary order for a MySQL SELECT statement? E.g., SELECT * FROM table_name WHERE id IN (1, 3, 2, 9, 7) ORDER BY (1, 3, 2, 9, 7); The order of the numbers listed directly after IN do not seem to matter.
ma11hew28
  • 121,420
  • 116
  • 450
  • 651
12
votes
4 answers

SQL IN equivalent in CAML

Is there a "nice" way to create a CAML query for SharePoint that does something like this? SELECT * FROM table WHERE Id IN (3, 12, ...) Or am I stuck with a nightmare of nested nodes? EDIT: This was my solution to generate the nodes. ///…
Chris
  • 4,393
  • 1
  • 27
  • 33
10
votes
3 answers

SQL Read Where IN (Long List from .TXT file)

I have a long list about 5000+ of ID's (numbers). ID 4 5 6 9 10 14 62 63 655 656 657 658 659 661 662 I would like to know if there a way to call to read the ID's from the txt file instead of typing all 5000 in the query? example SELECT count(*)…
user206168
  • 1,015
  • 5
  • 20
  • 40
10
votes
1 answer

MySql IN clauses, trying to match IN list of tuples

I am trying to select duplicate records based on a match of three columns. The list of triples could be very long (1000), so I would like to make it concise. When I have a list of size 10 (known duplicates) it only matches 2 (seemingly random ones)…
user3877299
  • 101
  • 1
  • 6
1
2 3
…
33 34