Understanding a basic SQL query

Question

I have a query like

SELECT tran_number
  FROM table_a WHERE customer_id IN
          (SELECT customer_id 
             FROM table_b
            WHERE customer_key = 89564
                  AND (   other_phn_area_code
                       || other_phnum_pfx_num
                       || other_phnum_sfx_num IN
                          (123456789)))
       AND phn_area_code || phnum_pfx_num || phnum_sfx_num IN (123456789)

---

The above code is working fine. The concern is with the inner query (copied inner query alone below)...

(SELECT customer_id 
                 FROM table_b
                WHERE customer_key = 89564
                      AND (   other_phn_area_code
                           || other_phnum_pfx_num
                           || other_phnum_sfx_num IN
                              (123456789)))

When i execute this query, i'm getting error as customer_id: invalid identifier. In real, table_b is not having any field named customer_id. If so, then how it is working, without any issue, when i use it as an inner query above.

Please help me to understand this.

Database details below

Oracle 11G Enterprise edition 11.2.0.2.0
PL/SQL Release 11.2.0.2.0

Answer 1

Thats is a known bug with IN. If you use table alias you will get error

SELECT tran_number 
  FROM table_a WHERE customer_id IN 
          (SELECT b.customer_id  
             FROM table_b b
            WHERE customer_key = 89564 
                  AND (   other_phn_area_code 
                       || other_phnum_pfx_num 
                       || other_phnum_sfx_num IN 
                          (123456789))) 
       AND phn_area_code || phnum_pfx_num || phnum_sfx_num IN (123456789) 

Also use EXISTS to avoid this type of silent behaviour

SELECT tran_number 
  FROM table_a as t1 WHERE EXISTS  
          (SELECT *
             FROM table_b as b
            WHERE customer_key = 89564 
                  AND (   other_phn_area_code 
                       || other_phnum_pfx_num 
                       || other_phnum_sfx_num IN 
                          (123456789))
        AND b.customer_id  =t1.customer_id) 
       AND phn_area_code || phnum_pfx_num || phnum_sfx_num IN (123456789) 

Answer 2

if the where condition of that inner select has a result, then the column customer_id from table_a will be selected. If not then it won't be selected. The outer select checks that with the in condition. That is like saying: "Only return something if the inner select return true."

Answer 3

It's a matter of scope. Oracle validates identifiers starting with the innermost sub-query and working outwards. If we add table aliases to your original query things might become clearer:

SELECT t1.tran_number 
  FROM table_a t1
  WHERE t1.customer_id IN 
          (SELECT t1.customer_id  
             FROM table_b t2 
            WHERE t2.customer_key = 89564 
                  AND (   t2.other_phn_area_code 
                       || t2.other_phnum_pfx_num 
                       || t2.other_phnum_sfx_num IN 
                          (123456789))) 
       AND t1.phn_area_code || t1.phnum_pfx_num || t1.phnum_sfx_num IN (123456789) 

In effect, the outer query is using the sub-querty as a test for EXISTS, i.e. just checking for the existence of a given value of CUSTOMER_KEY and those other columns. If this is not what you want then you should change the column name in the sub-query. (And that's a pretty good bet: you're probably getting puzzling results from the main query and that's why you're investigating the sub-query in isolation).

Using aliases in these scenarios is always good practice. If you had aliased the sub-query like this:

....
  WHERE t1.customer_id IN 
          (SELECT t2.customer_id  
             FROM table_b t2 
            WHERE t2.customer_key = 89564 
....

the error would have been immediately apparent.

---

The SQL Reference does explain the operation of scope in sub-queries, but it's hard to find. What it says is this:

"Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement"

You can find a clearer explanation of scoping in the PL/SQL documentation; SQL sub-queries work in the same fashion. Find out more.