Questions tagged [in-clause]

The IN clause of a SQL query is a function, generaly used within a WHERE clause, that returns true if a value is in a given set. Use for questions relating to this clause.

The IN clause of a SQL query is a function, generaly used within a WHERE clause, that returns true if a value is in a given set. For example,

SELECT *
FROM foo
WHERE bar IN(24, 37, 82);

will only return rows from foo if bar is either 24, 37, or 82.


More examples:

SELECT foo
FROM (SELECT 27 AS FOO FROM dual)
WHERE foo IN(35, 45, 26, 98, 27)

returns 27

SELECT foo
FROM (SELECT 64 AS FOO FROM dual)
WHERE foo IN(35, 45, 26, 98, 27)

returns no data found

257 questions
396
votes
8 answers

SQLAlchemy IN clause

I'm trying to do this query in sqlalchemy SELECT id, name FROM user WHERE id IN (123, 456) I would like to bind the list [123, 456] at execution time.
wonzbak
  • 7,734
  • 6
  • 26
  • 25
389
votes
33 answers

PreparedStatement IN clause alternatives?

What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list…
Chris Mazzola
  • 5,807
  • 5
  • 22
  • 15
261
votes
10 answers

Linq to Entities - SQL "IN" clause

In T-SQL you could have a query like: SELECT * FROM Users WHERE User_Rights IN ("Admin", "User", "Limited") How would you replicate that in a LINQ to Entities query? Is it even possible?
StevenMcD
  • 17,262
  • 11
  • 42
  • 54
143
votes
14 answers

PreparedStatement with list of parameters in a IN clause

How to set value for in clause in a preparedStatement in JDBC while executing a query. Example: connection.prepareStatement("Select * from test where field in (?)"); If this in-clause can hold multiple values how can I do it. Sometimes I know the…
Harish
  • 3,343
  • 15
  • 54
  • 75
126
votes
11 answers

How to put more than 1000 values into an Oracle IN clause

Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause? I have a comma delimited list of many of IDs that I want to use in an IN clause, Sometimes this list can exceed 1000 items, at which point Oracle throws…
Aaron Palmer
  • 8,912
  • 9
  • 48
  • 77
123
votes
8 answers

IN Clause with NULL or IS NULL

Postgres is the database Can I use a NULL value for a IN clause? example: SELECT * FROM tbl_name WHERE id_field IN ('value1', 'value2', 'value3', NULL) I want to limit to these four values. I have tried the above statement and it doesn't work, well…
Phill Pafford
  • 83,471
  • 91
  • 263
  • 383
113
votes
10 answers

Empty IN clause parameter list in MySQL

What happens when you do a SQL query where the IN clause is empty? For example: SELECT user WHERE id IN (); Will MySQL handle this as expected (that is, always false), and if not, how can my application handle this case when building the IN clause…
Wiz
  • 4,595
  • 9
  • 34
  • 51
112
votes
9 answers

PHP - Using PDO with IN clause array

I'm using PDO to execute a statement with an IN clause that uses an array for its values: $in_array = array(1, 2, 3); $in_values = implode(',', $in_array); $my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN…
iRector
  • 1,935
  • 4
  • 22
  • 30
83
votes
3 answers

MySQL variable format for a "NOT IN" list of values

Going crazy trying to set a variable in a query of type: SET @idcamposexcluidos='817,803,495'; so i can then use it on a WHERE id_campo not in (@idcamposexcluidos) I've tried defining the variable in different formats with no luck and don't seem…
luison
  • 1,850
  • 1
  • 19
  • 33
74
votes
5 answers

MySQL number of items within "in clause"

I have three tables to define users: USER: user_id (int), username (varchar) USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar) USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar) I'd like to…
Bart
  • 6,694
  • 6
  • 43
  • 53
67
votes
8 answers

SQL Server - In clause with a declared variable

Let say I got the following : DECLARE @ExcludedList VARCHAR(MAX) SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22' SELECT * FROM A WHERE Id NOT IN (@ExcludedList) Error : Conversion failed when converting the varchar value ', ' to data type int. I…
Melursus
  • 10,328
  • 19
  • 69
  • 103
60
votes
13 answers

JPA passing list to IN clause in named native query

I know I can pass a list to named query in JPA, but how about NamedNativeQuery? I have tried many ways but still can't just pass the list to a NamedNativeQuery. Anyone know how to pass a list to the in clause in NamedNativeQuery? Thank you very…
Li Ho Yin
  • 1,008
  • 2
  • 10
  • 15
47
votes
10 answers

What is the best approach using JDBC for parameterizing an IN clause?

Say that I have a query of the form SELECT * FROM MYTABLE WHERE MYCOL in (?) And I want to parameterize the arguments to in. Is there a straightforward way to do this in Java with JDBC, in a way that could work on multiple databases without…
Uri
  • 88,451
  • 51
  • 221
  • 321
45
votes
2 answers

passing list to IN clause in HQL or SQL?

I get List by executing a query. This must be passed to another query of IN clause values. How to pass them in HQL? We can convert List to Array and can pass it, that's not a problem. Finally, I must pass the list in List or Array…
Mr.Chowdary
  • 3,389
  • 9
  • 42
  • 66
40
votes
4 answers

How to pass a variable to a IN clause?

Lets say I have a SP that has a SELECT statements as follows, SELECT product_id, product_price FROM product WHERE product_type IN ('AA','BB','CC'); But data goes to that IN clause must be through a single variable that contains the string of…
Thanu
  • 2,481
  • 8
  • 34
  • 53
1
2 3
17 18