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

Using JSONB_ARRAY_ELEMENTS with WHERE ... IN condition

Online poker players can optionally purchase access to playroom 1 or playroom 2. And they can be temporarily banned for cheating. CREATE TABLE users ( uid SERIAL PRIMARY KEY, paid1_until timestamptz NULL, -- may play in room 1 …
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
3
votes
4 answers

Combine LIKE and IN using only WHERE clause

I know this question has been asked, but I have a slightly different flavour of it. I have a use case where the only thing I have control over is the WHERE clause of the query, and I have 2 tables. Using simple example: Table1 contains 1 column…
NullPointer
  • 2,084
  • 7
  • 24
  • 38
3
votes
3 answers

Where clause to check against two columns in another table

I am struggling to get this answer for some reason. I have two tables, table1 and table2 which look like this: Table1: ID Location Warehouse 1 London Narnia 2 Cyprus Metro 3 Norway Neck 4 Paris Triumph Table2: ID Area …
Johnathan
  • 879
  • 3
  • 12
  • 22
3
votes
2 answers

Codeigniter combine where_in and like active record queries

I have the following active record query: $this->db->select('id, email, first_name, last_name, current_location_state, current_location, avatar, avatar_fb'); $this->db->from('users'); $this->db->like('first_name', $search); …
Daniel White
  • 3,337
  • 8
  • 43
  • 66
3
votes
2 answers

Get all rows matching a list in a SQL query

I have the following SQL query that selects any row matching ANY of the values in the list (9, 10): SELECT r.id, r.title, u.name as 'Created By', c.name as 'Category', c.value, cr.category_id FROM category_resource cr …
Anders
  • 12,556
  • 24
  • 104
  • 151
3
votes
2 answers

Why is this mySQL query extremely slow?

Given is a mySQL table named "orders_products" with the following relevant fields: products_id orders_id Both fields are indexed. I am running the following query: SELECT products_id, count( products_id ) AS counter FROM orders_products WHERE…
Majiy
  • 1,890
  • 2
  • 24
  • 32
3
votes
1 answer

Dapper.Net: IEnumerable parameter throws exception: No mapping exists from object type System.Int64[] to a known managed provider native type

I am using Dapper.Net against SQL Server 2008 R2 in the following code to pass a List parameter to run a SQL query that has a WHERE IN clause, but I get the exception: No mapping exists from object type System.Int64[] to a known managed…
Abe
  • 6,386
  • 12
  • 46
  • 75
2
votes
1 answer

Avoid SQL WHERE NOT IN Clause

I have 3 tables listed below: Blog BlogArticle Article ---- ----------- ------- id id------blog_id -id title article_id__/ title This SQL describe what I want: SELECT * FROM `article` WHERE `article`.`id` NOT IN ( …
pengemizt
  • 837
  • 1
  • 9
  • 16
2
votes
2 answers

SQL / MySQL: How to check "AND" logic that similar to "OR" in "WHERE IN"

I have 2 tables named item and tag. They have a many-to-many relationship so their join table is item_tag as below. -- item table id name 1 Item 1 2 Item 2 3 Item 3 -- tag table id name 1 Tag 1 2 Tag 2 3 Tag 3 -- item_tag…
Mihiran Paranamana
  • 61
  • 7
2
votes
1 answer

Fast Spark alternative to WHERE column IN other_column

I am looking for a fast PySpark alternative to SELECT foo FROM bar WHERE foo IN (SELECT baz FROM bar) Collecting beforehand into a Python list is absolutely not an option as the dataframes handled are quite large and collect takes up tremendous…
LordBertson
  • 398
  • 4
  • 11
2
votes
1 answer

nodejs oracledb select data WHERE IN values array

I am trying to get rows where id in 1,2,3. Here is my code: app.use('/', async function(req, res, next){ try{ var query = await connection.execute(`SELECT * FROM ERROR_LIST WHERE ID IN :1`, [[1,2,3]]); console.log(query); …
Ayxan Əmiraslanlı
  • 423
  • 5
  • 16
2
votes
2 answers

Keep on sort order when using IN statement in WHERE clause

I'm using SQL Server 2005. I have a temporary sorted table (Table_A) that contains 2 columns (ID, RowNumber). Now, I create a new table by selecting all rows from other table (Table_B) that exist (ID value) in the temporary table (Table_A). SELECT *…
Liran Ben Yehuda
  • 1,568
  • 3
  • 12
  • 23
2
votes
1 answer

How to create the SqlQuerySpec in Java in order to retrieve a list of documents with given ids (where-in clause)

I am working on Azure Cosmos DB with SQL Api. I am using Azure SDK from: com.microsoft.azure azure-documentdb 2.4.7 I have a list of ids, and I…
fascynacja
  • 1,625
  • 4
  • 17
  • 35
2
votes
2 answers

Find IDS not present in table

I have a bunch of md5 ids 6c26e28dc6484f0e998fd18b883de4c6 2bf4d0d85709c75adba13ba48011d62c a67a5bcf329d58d2c23ed422214f66b3 ... Some of them are present in the table and some of them are new. How do I find which of these ids are not present in…
PirateApp
  • 5,433
  • 4
  • 57
  • 90
2
votes
3 answers

Postgres limit number of rows for each in WHERE IN id from another table

I have a messaging app where I need to return all of the conversations that a user is part of and the messages associated with each one. I'd like to limit the number of messages per conversation. Table structure is as follows: Users | id | name |…
ryanpback
  • 275
  • 4
  • 17
1 2 3
…
33 34