Why does erroneous inner query not make outer query erroneous

Question

Help understand why erroneous inner query does not make outer query erroneous

The following query returns 19

proc sql;
select count(distinct name)
    from sashelp.class
        where name in (select name from sashelp.iris
                        where species is not missing)
;quit; *returns 19;

However, I would expect it to return an error, because the inner query does indeed return an error (because the column 'name' is not found in sashelp.iris):

proc sql;    
select name from sashelp.iris
                            where species is not missing
;quit; *returns an error (column not found);

Can some explain the logic why I am not getting an error message in the first instance?

Answer 1

You did not qualify the reference to name so it used the only variable it found called name. So you ran this query:

proc sql;
select count(distinct A.name)
  from sashelp.class A
  where A.name in
(select A.name
  from sashelp.iris B
  where B.species is not missing
)
;
quit;

If you actually refer to NAME from IRIS you will get the error message.

220   proc sql;
221   select count(distinct A.name)
222     from sashelp.class A
223     where A.name in
224   (select B.name
225     from sashelp.iris B
226     where B.species is not missing
227   )
228   ;
ERROR: Column name could not be found in the table/view identified with the correlation name B.
ERROR: Unresolved reference to table/correlation name B.
229   quit;