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.
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'));