Stack Overflow
Stack Overflow

Questions tagged [in-subquery]

Use this tag for questions related to subqueries (i.e. a query that is nested inside a SELECT, INSERT, etc.), that use IN at their outer statement.

An example of an is:

WHERE th.parent = 1015 IN (
    SELECT DISTINCT(th1.tid)
    ...
);

as demonstrated in MySQL DELETE FROM with subquery as condition.

Note that this tag is general in its scope, i.e. that it is not restricted in SQL questions only, but any question referring to this type of IN subqueries is welcome to use this tag.

128 questions
112
votes
9 answers

MySQL DELETE FROM with subquery as condition

I am trying to do a query like this: DELETE FROM term_hierarchy AS th WHERE th.parent = 1015 AND th.tid IN ( SELECT DISTINCT(th1.tid) FROM term_hierarchy AS th1 INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent !=…
mikl
  • 23,749
  • 20
  • 68
  • 89
66
votes
4 answers

JPA 2.0, Criteria API, Subqueries, In Expressions

I have tried to write a query statement with a subquery and an IN expression for many times. But I have never succeeded. I always get the exception, " Syntax error near keyword 'IN' ", the query statement was build like this, SELECT t0.ID,…
Keating
  • 3,380
  • 10
  • 34
  • 42
30
votes
8 answers

MySQL WHERE IN

Is there a way (without JOIN) to use the WHERE clause on 2 columns (OR) IN a subquery? Currently, I'm doing WHERE 'col1' IN ( SELECT id FROM table ) OR 'col2' IN ( SELECT id FROM table ) And I'm sure I can do better :) . i've also tried…
Max13
  • 919
  • 2
  • 9
  • 27
8
votes
4 answers

IN subquery's WHERE condition affects main query - Is this a feature or a bug?

Assume the two tables: Table A: A1, A2, A_Other Table B: B1, B2, B_Other In the following examples, is something is a condition checked against a fixed value, e.g. = 'ABC' or < 45. I wrote a query like the following (1): Select * from A Where A1 IN…
ADTC
  • 8,999
  • 5
  • 68
  • 93
7
votes
4 answers

LINQ subquery IN

I'm a newbie with the IQueryable, lambda expressions, and LINQ in general. I would like to put a subquery in a where clause like this : Sample code : SELECT * FROM CLIENT c WHERE c.ETAT IN ( SELECT DDV_COLUMN_VAL FROM DATA_DICT_VAL WHERE…
Patrice Cote
  • 3,572
  • 12
  • 43
  • 72
6
votes
1 answer

MySQL using IN/FIND_IN_SET to read multiple rows in sub query

I have two tables, locations and location groups CREATE TABLE locations ( location_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(63) UNIQUE NOT NULL ); INSERT INTO locations (name) …
leejmurphy
  • 994
  • 3
  • 17
  • 28
6
votes
1 answer

Am I crazy: PostgreSQL IN operator with nested query returning unexpected results

The following query returns 2036 rows: SELECT "FooUID" from "Foo" f LEFT JOIN "Bar" b ON f."BarUID" = b."BarUID" WHERE f."BarUID" IS NOT NULL AND b."BarUID" IS NULL But the following statement only updated 1870 rows: UPDATE "Foo" f1 set "BarUID" =…
Paul Bellora
  • 54,340
  • 18
  • 130
  • 181
5
votes
4 answers

SQL select with "IN" subquery returns no records if the sub-query contains NULL

I came across this interesting behavior. I see left-join is the way to go, but would still like to have this cleared. Is it a bug or behavior by-design? Any explanations? When I select records from left table, where a value is not present in the…
Robert Cutajar
  • 3,181
  • 1
  • 30
  • 42
5
votes
6 answers

What's wrong with this MySQL query? SELECT * AS `x`, how to use x again later?

The following MySQL query: select `userID` as uID, (select `siteID` from `users` where `userID` = uID) as `sID`, from `actions` where `sID` in (select `siteID` from `sites` where `foo` = "bar") order by `timestamp` desc limit 100 …returns an…
Mathias Bynens
  • 144,855
  • 52
  • 216
  • 248
5
votes
2 answers

SQL:in clause with query taking too long compared to in clause with actual data

I have 3 SQL queries as given: select student_id from user where user_id =4; // returns 35 select * from student where student_id in (35); select * from student where student_id in (select student_id from user where user_id =4); first 2 queries…
thekosmix
  • 1,705
  • 21
  • 35
4
votes
1 answer

MySQL - How to add alias syntax to subquery

I'm new to MySQL and still have issues with its syntax. I have this query: SELECT a, b, c, d, e FROM table1 WHERE status = 'skipped' AND batchid IN (SELECT batchid FROM (SELECT distinct batchid, date_format(uploaddate, '%Y-%m-%d')…
sdoca
  • 7,832
  • 23
  • 70
  • 127
3
votes
5 answers

Query with HAVING and WHERE

I'm trying to create a single query that will combine the following two queries. SELECT campgroundid, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(…
Seth
  • 659
  • 2
  • 7
  • 21
3
votes
1 answer

Hive : Multiple In-elements with single subquery

I was trying to run a query like this on hive (version 1.2.1) : DELETE FROM employee as e WHERE (e.id, e.name) IN ( SELECT emp.id, emp.name FROM employee_final emp) AND e.sno = 120 ; But since hive doesn't support this construct so I tried this…
Piyush Dixit
  • 75
  • 8
3
votes
2 answers

When does PostgreSQL collapse subqueries to joins and when not?

Considering the following query: select a.id from a where a.id in (select b.a_id from b where b.x='x1' and b.y='y1') and a.id in (select b.a_id from b where b.x='x2' and b.y='y2') order by a.date desc limit 20 Which should be rewritable to…
tbz
  • 197
  • 2
  • 10
3
votes
4 answers

New to SQL Server Sub Queries - Hopefully an easy one

I just cant work this out in a single query. Table TblQcProduction has a record for each operator on a Production order. I need to find out who the last operator was that reported against the production order. SELECT IDX, ProdOrder, Operator FROM…
user2819192
  • 31
  • 1
1
2 3
…
8 9