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

mysql: selecting persons who have a certain amount of relational rows

I have two relational tables, person and event_registration, and I would like to select all persons who have registered to 10 or less events. I have found a query which seems to be correct. It can find these persons with the following query: …
Robin Manoli
  • 2,162
  • 2
  • 25
  • 30
0
votes
1 answer

What is wrong whith this sql command?

I am trying to create query which returns the list of students who passed an exam after, At least X number of failures.To achieve this i wrote the following query but i am getting the following error as well: Error in list of values in IN clause.…
Hossein
  • 24,202
  • 35
  • 119
  • 224
0
votes
6 answers

SQL subqueries what has the best performance

I'm having a question about this SQL query: SELECT class FROM Ships WHERE name IN (SELECT ship FROM Outcomes WHERE result = ’sunk’); Can I write in the subquery SELECT * From Outcomes or do I always need to select…
user1007522
  • 7,858
  • 17
  • 69
  • 113
0
votes
2 answers

JPQL query, inclause

I am dynamicallu generating this query, but getting Invocation Exception: Select p From Product p Inner Join ProductFeatureValue pv Where p.productID = pv.productID and p.category.categoryID = 5 and pv.value in ('Blue','Black') Error…
Khushbu Joshi
  • 63
  • 2
  • 8
0
votes
3 answers

sql in clause doesn't work

I have a table with a column ancestry holding a list of ancestors formatted like this "1/12/45". 1 is the root, 12 is children of 1, etc... I need to find all the records having a specific node/number in their ancestry list. To do so, I wrote this…
ndemoreau
  • 3,849
  • 4
  • 43
  • 55
-1
votes
3 answers

MSSQL WHERE A.field1 IN B.field2 when field2 format is '11111','22222','33333','55555'

I have a weird issue to solve and I am not sure what would be the best way to do this from a performance perspective as I have millions of records to parse in table A (but only 1 row in table B per query). Table B is coming from a UI…
mkirouac
  • 113
  • 1
  • 12
-1
votes
2 answers

Mysql query to find first using IN clause with preference from left to right

I have a test table with columns(id, title, language) id: primary_key (auto-increment) unique_key(composite) on language and title id title language 1 Japanese JP 2 Australian AU 3 English EN 4 Hindi HI I would like to have a query…
Another coder
  • 360
  • 5
  • 18
-1
votes
2 answers

Use Indexes to optimise my query OR Mysql

I have read through a lot of documentation on how to use EXPLAIN and indexes on sitepoint and mysql documentation website. Even found a few PDFs. But my query seems a bit complex than all the example I have come across. I have been trying to…
Peter
  • 60
  • 8
-1
votes
5 answers

Using TUPLES to put more than 1000 entries in SQL IN clause

This post is about putting more than 1000 entries in an IN clause in Oracle. Generally, when you put more than 1000 entries in the IN clause, Oracle will throw an error, as Oracle does not handle such number of entries in the IN clause. How to solve…
IMJS
  • 863
  • 2
  • 13
  • 25
-1
votes
1 answer

SQL query command using and and not in clausing, a bit complicated one, lost at last step

EMPLOYEE (fname, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno) KEY: ssn DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) KEY: dnumber. PROJECT (pname, pnumber, plocation, dnum) KEY: pnumber. WORKS_ON (essn, pno, hours) KEY:…
Lin Wei
  • 87
  • 1
  • 5
-1
votes
2 answers

Concat is not working in mysql inclause

Please help me, i try to fetch category list as like below, where category ids is passed in inclause, it returns two rows. mysql> select c_id,c_name from category where c_id in (870,854); +------+---------------+ | c_id | c_name …
s.ganesh
  • 1
  • 2
-1
votes
3 answers

MySQL IN clause query

Simply, I want to find a solution for testing if one column value exits within another, or not, across tables. I've naturally jumped to 'NOT IN clause' but I've also discovered I can't use a colum name within the bracket (b.full_name) SELECT *…
user2505513
  • 343
  • 4
  • 9
  • 24
-1
votes
1 answer

How to have IN operator work to have hard coded values match

Have accountId ={001,3456,78902,456} I wanted to get the details of these accountId from existing table using IN clause and a left join on operation, how should I form the query? Being new to DB end I am not able to put the correct pieces…
AKIWEB
  • 19,008
  • 67
  • 180
  • 294
-2
votes
1 answer

Using query with "IN clause" vs "="

Need suggestion as due to performance improvement i need to convert all loop queries to IN clause. I have following where conditions which may vary based on iteration.. Where recordID=2323 and (origin=626 or destination=319); Where recordID=2323 and…
Prashant Katara
  • 95
  • 1
  • 1
  • 14
-2
votes
1 answer

Conditional IN clause in SQL Server

I have got a stored procedure which has five parameters. I want to include them in where clause as follows If parameter is not null then include it in query as an IN clause That is, parameter value can be like 'Test' or 'Test,Best' etc. I'm…
J. Doe
  • 1
1 2 3
17
18