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
9
votes
5 answers

MySQL WHERE IN Query - ORDER BY Match

I'm trying to rephrase my question, cause my last one wasn't clear to everyone. This is my Test Table +----------+---------+-------+ | rel_id | genre | movie | +----------+---------+-------+ | 1 | 1 | 3 | | 2 | 8 | …
richie
  • 189
  • 1
  • 2
  • 11
9
votes
1 answer

using array in WHERE IN in CakePHP

I'm working on CakePHP 3.2 I want to use an array in the query WHERE IN () The code is as $filter_p_t = $this->request->query('p_t'); $pros10 = $this->Products->find() ->where([ 'SellerProducts.stock >' => 0, 'SellerProducts.status' => 1, …
Anuj TBE
  • 9,198
  • 27
  • 136
  • 285
9
votes
3 answers

Where IN a Comma delimited string

I would like to retrieve certain users from a full list of a temp table #temptable. The query looks like this: DECLARE @List varchar(max) SELECT @List = coalesce(@List + ',','') + '''' + StaffCode + '''' FROM tblStaffs SELECT UserName FROM…
Weihui Guo
  • 3,669
  • 5
  • 34
  • 56
9
votes
2 answers

SELECT from table with Varying IN list in WHERE clause

I am facing a issue in project I am working on, I can not give you actual code but I have created a executable sample code as below Here temp and temp_id are two tables temp table contains comma separated list of ids which is VARCHAR2 temp_id…
eatSleepCode
  • 4,427
  • 7
  • 44
  • 93
9
votes
2 answers

Performing a WHERE - IN query in CouchDB

I would like to query for a list of particular documents with one call to CouchDB. With SQL I would do something like SELECT * FROM database.table WHERE database.table.id IN (2,4,56); What is a recipe for doing this in CouchDB by either _id or…
johowie
  • 2,475
  • 6
  • 26
  • 42
7
votes
1 answer

How to check if value is in a list or if the list is empty?

I'm using psycopg2 to access a PostgreSQL database through Python 3, and I'm attempting to make a query where I want to select all users whose name are in a list, if the list is not empty. If the provided list is empty, I want to ignore the…
Markus Meskanen
  • 19,939
  • 18
  • 80
  • 119
7
votes
1 answer

Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

This might not be very sensible, but I'ld like to let MySQL return me the exact duplicate rows if there are duplicate criteria in the WHERE IN clause. Is this possible? Take this example: SELECT columns FROM table WHERE id IN( 1, 2, 3, 4,…
Decent Dabbler
  • 22,532
  • 8
  • 74
  • 106
7
votes
2 answers

PostgreSQL: WHERE IN and NOT WHERE IN

I have two tables A and B, A referred to B by field A.id_b B.id, so that any id_b value present in B.id. I have three queries: First one: SELECT COUNT(b.id) FROM B b WHERE b.id NOT IN ( SELECT a.id_b FROM A a) It gives me 0; Second one,…
devdRew
  • 4,393
  • 3
  • 24
  • 33
6
votes
3 answers

How to write WHERE IN clause from a dynamic ArrayList for Android SQLite query

How can i write the where in clause in Android SQLite query? Function to retrieve a single customer public Cursor getCustomers(int groupId) { return db.query(TABLE_CUSTOMERS, new String[] { KEY_CUSTOMER_ID, KEY_NAME}, KEY_GROUP_ID+" = "+groupId,…
Mithun Sreedharan
  • 49,883
  • 70
  • 181
  • 236
6
votes
4 answers

SQL Server selecting a string from table using in clause

I'm having a strange SQL Server issue. Using the following query: SELECT id FROM table WHERE id IN ('id1', 'id2', .......) when id is nchar(30) and 'id1','id2' are values, I get a result which isn't in the values I entered. Is it possible that SQL…
Noam Shaish
  • 1,613
  • 2
  • 16
  • 37
5
votes
3 answers

Checking if specific tuple exists in table

Is there a way to check if a specific tuple exists in a table in a where-in statement? Something like: create table Test(A int, B int); insert into Test values (3, 9); insert into Test values (6, 7); insert into Test values (7, 6); insert into Test…
Acorn
  • 49,061
  • 27
  • 133
  • 172
5
votes
3 answers

Pass string into SQL WHERE IN

I am working on a query page where a user selects a value which represents different types, each identified by an ID. The problem is selecting these IDs from the data base using the WHERE IN method. This is my SQL statement SELECT M.REG_NO,…
Anto
  • 419
  • 2
  • 9
  • 19
5
votes
2 answers

How to make Laravel whereIn not sorted automatically

my array from $temp is Array ( [0] => 22 [1] => 26 [2] => 20 [3] => 24 ) or 22|26|20|24 when I use whereIn like this $robjeks = DB::table('objek')->whereIn('id', $temp)->get(); the result is 20|22|24|26| it's automatically sorted. I want it's not…
Mading Ne
  • 133
  • 2
  • 13
5
votes
2 answers

SQL exclude rows matching all of multiple criteria

I currently have a query merging two tables to create a new one for analysis. After getting some funny results when trying to chart it for presentation, I learned that some of it is fake data that was never cleaned up. I've been able to identify the…
Minadorae
  • 301
  • 3
  • 5
  • 13
5
votes
1 answer

How to make multiple WHERE IN column query in Doctrine query builder?

I would like to update multiple records in db using WHERE IN statement with two column check. Pure MySql raw query looks something like this.. and it works: UPDATE poll_quota q SET q.count = q.count+1 WHERE q.form_id=14 AND ((q.field_id,q.value) IN…
Tomk
  • 103
  • 1
  • 8
1
2
3
…
33 34