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

MySQL, Given a list, selecting the missing rows from a Table

This has been driving me crazy for the past few minutes I have a table, lets say table_alphabet with three columns. letter(pri) col1 col2 a 24 55 b 45 45 c 23 44 ... y 33 55 z …
The Unknown
  • 19,224
  • 29
  • 77
  • 93
4
votes
3 answers

Passing C# String as SQL parameter for SELECT WHERE IN

I am writing a function to take as many multiple selected items from a Listbox and pass the vaules to a SQL Query to INSERT values into a table after selecting the filtered values from another table. The code I have typed is below and it doesn't…
schaud
  • 89
  • 1
  • 3
  • 6
4
votes
7 answers

NULL usage in WHERE IN SQL statement

I have below query Select RPAD(x.QUOTE_ID,20,' ') from csa_sli_all.T_CONV_XREF_CUST_QUOTE x , csa_sli_all.T_CONV_quote q where q.select_indicator is null and q.QUOTE_ID = X.QUOTE_ID and q.HOLD_CODE IN ('CAQ' , NULL ) it doesnt…
Priyanka Kaushik
  • 153
  • 6
  • 13
3
votes
1 answer

Creating a "where in" HQL query

I am having trouble writing a HQL query which uses a "where in" clause. Simplified classes look like this: class Parent { public virtual Int64 Id { get; private set; } public virtual string Name { get; private set; } } class Child { …
vgru
  • 49,838
  • 16
  • 120
  • 201
3
votes
1 answer

How to use PostgreSQL array in WHERE IN clause?

I am trying to simplify the following SQL statement (wrapped into function for clarity). What is a simpler/natural syntactical way to use array inside where ... in (/*array*/) clause? (without select * from unnest(...) boilerplate) CREATE OR REPLACE…
diziaq
  • 6,881
  • 16
  • 54
  • 96
3
votes
2 answers

Can I use an "IN" filter on an Advantage Database Table (TAdsTable)?

I want to apply a filter to an advantage table using multiple values for an Integer field. The equivalent SQL would be: SELECT * FROM TableName WHERE FieldName IN (1, 2, 3) Is it possible to do the same on an AdsTable within having to repeat the…
Anthony
  • 33
  • 3
3
votes
1 answer

Select where not in with select

Im trying to do this SELECT IN SQL SERVER SELECT cast(datediff(DAY, min([fbh].FBH_DATA_INICIAL), CASE WHEN max([fbh].FBH_DATA_Final) = '9999-12-31' THEN cast(getdate() AS date) …
Fabio Donadelli
  • 31
  • 1
  • 5
3
votes
1 answer

Using GROUP BY in AWS QLDB / Partiql

I have the following table in my AWS QLDB table: INSERT INTO Testing << { 'MyId': 1, 'MyList': [ 'Item1', 'Item2', 'Item3'] }, { 'MyId': 2, 'MyList': [ 'Item2', 'Item3', 'Item4'] }, { 'MyId': 3, 'MyList': [ 'Item4', 'Item5',…
David
  • 4,744
  • 5
  • 33
  • 64
3
votes
1 answer

Is there a way to split a list in the following format : 'string1', 'string2', 'string3',

I need to perform a filter to my existing XtraReport and I want to see only some specific records which I have their ID's. When I execute following code it is applied successfully. XtraReportOrder report = new XtraReportOrder(); report.FilterString…
husnu
  • 354
  • 3
  • 15
3
votes
2 answers

Unnest (or similar) in SQLite

I'd like to return multiple rows as a single row to be handled by an Android Cursor Adapter. I currently have a table with a date column and I'd like to return, as a single row, all the rows that have the same date. Consider the following table: …
StaticCrazee
  • 167
  • 1
  • 11
3
votes
1 answer

MySQL select where in but not in with join

I have three tables in my database: Products id (int, primary key) name (varchar) Tags id (int, primary key) name (varchar) ProductTags product_id (int) tag_id (int) I'm doing SQL query to select products with assigned tags with given…
Darrarski
  • 3,882
  • 6
  • 37
  • 59
3
votes
1 answer

"Query is too complex" exception in MS Access 2010

The following query is generating an exception. How can I simplify it? UPDATE Word SET CorrectnessCount=@CorrectnessCount WHERE GroupNo=@GroupNo AND (Name=@Adduce OR Name=@Assuage OR Name=@Athwart OR Name=@Auscultation OR Name=@Bedizen…
user366312
  • 16,949
  • 65
  • 235
  • 452
3
votes
2 answers

Multiple column where_in clause in codeigniter

Below is codeigniter single column where_in clause $this->db->where_in('x1',$val); how can I pass multiple column in CodeIgniter where_in clause like below MySQL query select * from tab1 where (col1,col2) in ((1,2),(2,3)) Any help appreciated
Thiyagu
  • 746
  • 3
  • 11
  • 29
3
votes
1 answer

How to multi-split a string (WHERE IN query)

I am passing a string as param (via massivejs) into my query. The string is formatted as: param = 'red, blue, green'. The param itself does not have a fixed length (',' being the delimiter) as it is populated through what the user sends in (but is…
Suman
  • 43
  • 5
3
votes
2 answers

Where Clause in LINQ with IN operator in Vb

To get details of a person 'Adam' Sql query SELECT * FROM tableName WHERE Name = 'Adam' Linq query in Vb From tableName In something Where row.Field(Of String)("name").Trim = "Adam" But to get details of multiple people 'Adam', 'Chris' Sql…
rANth
  • 397
  • 2
  • 7
  • 18
1 2 3
…
33 34