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
-1
votes
3 answers

How can I select a set of IDs from large table fast?

I have a large table with ID as primary. About 3 million rows and I need to extract a small set of rows base on given ID list. Currently I am doing it on where... in but it's very slow, like 5 to 10s. My code: select id,fa,fb,fc from db1.t1…
user1748365
  • 19
  • 4
-2
votes
2 answers

Merging two tables in Powerquery using both a case clause and a where function (should I use something else?

I am trying to merge two tables in Powerquery. One is from a database, the other is from another source and has a set list of part numbers (pano20). Each part number has a source of supply (sos1) and some part numbers have two or more sources of…
lberecek
  • 51
  • 5
-2
votes
1 answer

Can SQLite3 SELECT (up to) some rows based on a WHERE, and in the same query SELECT other rows on another condition?

Hi it's my first time using SQLite and I am trying to understand if it can count the results it gets and switch conditions to find other results: I am not sure how to write this, so I will use an example: From a table of players, with the same query…
Serbiss
  • 3
  • 6
-2
votes
2 answers

comparison within in clause of postgresql

Is it possible to add condition within the in clause of postgresql for example select ... where (t1.subject,t2.weight) in ((1,2),(2,3)) I want to check whether subject is 1 but weight can be >= 2 not just 2 and so on. So that condition would…
Manavi Thorve
  • 1
-2
votes
1 answer

include additional columns while using WHERE IN

I have a table that looks like this. id name 1 firstName 2 secondName 3 thirdName 4 fourthName I want to keep all rows where the name is present in either the "Country_name_EN" or "Country_Code" columns from…
x89
  • 2,798
  • 5
  • 46
  • 110
-2
votes
1 answer

How to use WHEREIN condition to a Variable in Laravel

I am a beginner to the Laravel framework. In this case, I have used Eager Loading. Also, I have assigned data to a Variable called $articles. After that, I have used WHERE IN Condition to fetch related data. Finally I have returned $articles…
Kanchana Kariyawasam
  • 43
  • 9
-2
votes
1 answer

SQL where in (thousands of records)

I am an in a pickle now and would really appreciate your advice. Got an assignment from our management to take a list of 500000 email addresses and check in our DB if any of them are registered there. The problem is in the amount of users, cause…
Tanya
  • 33
  • 3
-2
votes
1 answer

Entity framework 6.2.0 contains clause as where in not working propertly

(update: sorry, I removed the AsEnumerable() code because when you use AsEnumerable the linq to sql object executes the sql query and brings the full table to memory which can have more than 10000000 rows and what I wolud like to do is to execute a…
Energ888
  • 69
  • 1
  • 7
-2
votes
1 answer

Mysql Query where field in (select column value) not works

I am trying to get some values with the help of where clause and the value for that where clause is the values from a record in the same table. For example, select requestedusers from users where username = 'xyz' When I run the above query it gives…
Ragesh
  • 205
  • 3
  • 11
-2
votes
1 answer

Return a row even if no record is found in where in() MySql

select id from table where email in (a,b,c); I do not have an id for b, so it returns: 1 3 But I want it to return 1 null 3 I do not want to use joins. Thank you!
Adil Khan
  • 144
  • 9
-2
votes
1 answer

Only one row is shown, when using in clause in SQL

query 1: select products from buyde_deal where displayflag = '1' and end_date> now() and start_date < now() limit 1 output : query 2: SELECT id,productname ,cat_id ,subcat_id,shortdescription1,shortdescription2,shortdescription3…
inrsaurabh
  • 602
  • 1
  • 8
  • 17
-2
votes
3 answers

MySQL SELECT WHERE IN Incrementing Value

I have a DB, which holds words with position identifier. For instance the following string: The Quick Brown Fox Jumps Over The Lazy Dog The position identifiers would be: The Quick Brown Fox Jumps Over The Lazy Dog What I would like to…
TVA van Hesteren
  • 1,031
  • 3
  • 20
  • 47
-2
votes
2 answers

SQL using contains or Like '%%' and IN together

is there any way to write SQL query where we can perform contains Check on a column to a sub query/function i.e. in this manner select * from where COLUMN contains IN (select * from func) or select * from where '%'+ COLUMN + '%' IN (select *…
prasadhkumar
  • 11
  • 1
  • 1
-2
votes
2 answers

How to use ComboBox SelectedValue in where clause

I try this code but in the where clause there's an error. var subComTbl = from subCom in myDb.SubComTbls where subCom.AuthorityID.ToString()== Authoritycombo.SelectedValue select subCom; SubComcombo.Visible = true; SubComcombo.DataSource =…
MU Al-t
  • 9
  • 5
-2
votes
2 answers

PHP/MySQL Using multiple WHEREs in one SELECT query

I have 2 tables. Table A: trades: which contains the columns: tradeID, tradeName, tradeShow, and tradeGuy. Table B: offers: which contains the columns: tradeID, offerName, offerGuy. I'm trying to select all columns from table A (trades) WHERE the…
MNOPYZ
  • 55
  • 1
  • 2
  • 13
1 2 3
…
33
34