CODEWITHSUNDEEP
sqloracle-databasesql-in
Mansi Raval
Mansi Raval

Reputation: 371

Behavior of IN in Oracle

I have written a subquery to join two tables and fetch the data, where one ID column is common for both to join. But the column name is different as below.

This one I have written:

SELECT parent_id ,name   
FROM parent_table 
WHERE parent_id IN (SELECT parent_id 
                    FROM child_table 
                    WHERE country IN ('US'));

It's giving all of the rows from the parent table as the subquery SELECT parent_id FROM child_table WHERE country IN ('US') seems incorrect. There is no parent_id column in the table.

Below query is correct one:

SELECT parent_id ,name   
FROM parent_table 
WHERE parent_id IN (SELECT child_id 
                    FROM child_table 
                    WHERE country IN ('US'));

Now my question is: Why the first query is not giving any error since the subquery is incorrect?

The subquery executed in isolation will return ORA-00904.

Upvotes: 1

Views: 71

Answers (2)

jarlh
jarlh

Reputation: 44716

A sub-query may include references to outer query tables. Since you don't qualify the columns, and the child_table has no parent_id column, your query

SELECT parent_id ,name   
FROM parent_table WHERE parent_id IN ( SELECT parent_id FROM child_table WHERE country IN ('US'));

will be evaluated as

SELECT parent_id ,name   
FROM parent_table WHERE parent_id IN ( SELECT parent_table.parent_id FROM child_table WHERE country IN ('US'));

Which will return all parent_table rows as long as the child_table has at least one 'US' row.

It's good programming practice to always qualify the columns (when more than one table is involved.) Also use table aliases to save some typing.

SELECT pt.parent_id, pt.name   
FROM parent_table pt WHERE pt.parent_id IN ( SELECT ct.child_id FROM child_table ct WHERE ct.country IN ('US'));

Upvotes: 2

MT0
MT0

Reputation: 167832

Oracle is using the parent_id column from the outer query.

If you qualify the columns with the tables they are referencing then Oracle is doing:

SELECT parent_table.parent_id,
       parent_table.name   
FROM   parent_table
WHERE  parent_table.parent_id IN (
  SELECT parent_table.parent_id
--       ^^^^^^
  FROM   child_table
  WHERE  child_table.country IN ('US')
);

Which will return all rows and will not raise an exception.

But you were expecting:

SELECT parent_table.parent_id,
       parent_table.name   
FROM   parent_table
WHERE  parent_table.parent_id IN (
  SELECT child_table.parent_id
--       ^^^^^
  FROM   child_table
  WHERE  child_table.country IN ('US')
);

Which would raise an exception as there is no parent_id column in child_table.

Upvotes: 3

Related Questions