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
2
votes
2 answers

Maximum values possible in a WHERE IN query

I have a table with over 3000000 entries, and i need to delete 500000 of them with given ID's. My idea is to create a query like: DELETE FROM TableName WHERE ID IN (id1, id2, ...........) which I generate with a simple C# code. The question is: is…
Noam Shaish
  • 1,613
  • 2
  • 16
  • 37
2
votes
1 answer

laravel whereIn multiple columns

I am looking for laravel way for this query: select * from `table` where (product_id, request_id) NOT IN ((66, 10),(76,23)) Perhaps something like: $ids = =array( ['66', '10'], ['76',…
Azima
  • 3,835
  • 15
  • 49
  • 95
2
votes
1 answer

Laravel Eloquent query model which has exactly a number of association in a n-to-m relationship

I have a project where I have the following Models : Products FeatureTypes Features Variants And the tables and relationships are the following : I have a request where I want to retrieve the Variants from a Product with some specific Features.…
dib258
  • 705
  • 8
  • 25
2
votes
2 answers

Does h2 have a query/clause similar to the WHERE IN in MySQL?

My code currently goes as follows: public List getChildrenByParentId(List parentIds) throws DeviceOrganizationDAOException { List children = new ArrayList<>(); …
Nuwan Jayawardene
  • 374
  • 3
  • 17
2
votes
1 answer

Multiple IN Conditions on a DELETE FROM query throwing a #245 Conversion Type Error

I have a table setup like the following: Parameters ╔═══╦═════════╦════════╗ ║ID ║ Name ║ Value ║ ╠═══╬═════════╬════════╣ ║ 7 ║ first ║ 0 ║ ║ 7 ║ second ║ -1 ║ ║ 7 ║ third ║ -1 ║ ╚═══╩═════════╩════════╝ It contains more…
Dr Pepper
  • 43
  • 7
2
votes
4 answers

how to use an array in php mysql query?

I've been trying to retrieve all site_keywords from the database, using where site_keywords in $keyword. But it doesn't show any error or output. $user_query = $_REQUEST['user_query']; $search=preg_split('/\s+/',$user_query); $keywords =…
user8214181
  • 45
  • 3
2
votes
3 answers

Treat WHERE IN parameters as AND and not OR

I have this table structure/data : I want to select the row_id(s) that have the combination of (97,6,2) AND (99,1,4) on (attribute_id,store_id,value) respectively. In this example, we want to get the row_id 8664 as it respects this condition. What…
androniennn
  • 3,117
  • 11
  • 50
  • 107
2
votes
2 answers

Potsgres Performance: WHERE = Versus WHERE IN (...) for single values

I am working on an application that uses Postgres as its backing database. I am working on a component that executes a SELECT query on the DB, using a multi-select input control on the UI to supply the input values for the query. Previously, this…
ecbrodie
  • 11,246
  • 21
  • 71
  • 120
2
votes
3 answers

Pandas equivalent of delete from dataframe where in (...)

I have a dataframe with an email column. I'm trying delete all records from this dataframe where the email address exists in a second dataframe. In SQL this would be delete from df1 where email in (select email from df2) Thanks
andyabel
  • 335
  • 4
  • 15
2
votes
2 answers

How to generate a query using an array of delimited columns & values

I have this array: $filter=['color*black','color*blue','color*red','paint*apex','paint*dalton']; Each value in $filter has two substrings separated by *. The first substring represents a database table column and the second represents a desired…
Abilash Erikson
  • 341
  • 4
  • 26
  • 55
2
votes
2 answers

SQL WHERE IN with Left Join is not returning all rows I expect

I'm building a little conjugation/radicalization app, and I have stumbled upon a problem. I have this SQL request: SELECT DISTINCT RA.* FROM radical RA left join conjugated CO on CO.word_id = RA.id where CO.conjugation IN…
Jo Colina
  • 1,870
  • 7
  • 28
  • 46
2
votes
2 answers

Convert MySQL search for a pair of values in a table to Eloquent query

I am having trouble reproducing this kind of a MySQL query using Eloquent SELECT * FROM foo WHERE (column1, column2) IN (('foo', 1), ('bar', 2)) There is a method in Eloquent query builder called whereIn(), but it can receive just one…
Matko Đipalo
  • 1,676
  • 1
  • 11
  • 23
2
votes
1 answer

Slow WHERE IN End of Query

Sample data from my table test_table: date symbol value created_time 2010-01-09 symbol1 101 3847474847 2010-01-10 symbol1 102 3847474847 2010-01-10 symbol1 102.5 3847475500 2010-01-10 …
Trevor Nederlof
  • 2,546
  • 5
  • 22
  • 40
2
votes
2 answers

Database Index when SQL statement includes "IN" clause

I have SQL statement which takes really a lot of time to execute and I really had to improve it somehow. select * from table where ID=1 and GROUP in (select group from groupteam where department= 'marketing' ) My question is if I should create…
Veljko
  • 1,708
  • 12
  • 40
  • 80
2
votes
4 answers

Using Laravel query builder method whereIn() with sub query

I am developing a Web Application using Laravel 5.3. I am now having an issue with Laravel query builder method, whereIn. I have table like this. student - id, name, dob course - id, code student_course - student_id, course_id What I want to do is…
Wai Yan Hein
  • 13,651
  • 35
  • 180
  • 372
1 2 3
…
33 34