sqloracle-databaseselectsubquerycorrelated-subquery

Why is this SQL query valid?

Why doesn't this SQL query return ar error (in Oracle)?

field_99 exists only in table_1!

SELECT *
  FROM table_1
 WHERE table_1.field_1 IN
     (SELECT table_2.field_1
        FROM table_2
       WHERE table_2.field_2 IN ('0000')
         AND (field_99 LIKE '%AAA%' OR field_99 LIKE '%BBB%' OR field_99 LIKE '%CCC%'));

If it is OK, must it return the same results as the following [correct] one?

SELECT *
  FROM table_1
 WHERE table_1.field_1 IN
     (SELECT table_2.field_1
        FROM table_2
       WHERE table_2.field_2 IN ('0000'))
   AND (field_99 LIKE '%AAA%' OR field_99 LIKE '%BBB%' OR field_99 LIKE '%CCC%');

I ran the first query and expected it would return an error.

Solution

  • This query works because the inner query gets the value of field_99 from the enclosing row in the outer query.

    Since there's no correlation between the field_99 value and any value coming from the inner query, it's indeed pointless (not outright wrong, as you noted, the query works, but pointless).
    You can safely rewrite the query as you suggested, and IMHO, it would also be easier to understand.