Quite true. It's just that this seemed so counter intuitive.

While I have only been working with MSS for just over a year, I have about 20 years working with RDBS (DB2 for mainframe) and SQL, and had never encountered this situation. I just had to see what DB2 would do if I did something like this there: (Yes, I have been spoiled working with what seems a much more mature platform. And I used SELECT instead of DELETE, not wishing to destroy any data.) It returned all the rows from PSFT88.PSXLATITEM, AND returned a warning.

---------+---------+---------+---------+---------+---------+---------+---------+

SELECT *

FROM PSFT88.PSXLATITEM

WHERE EFF_STATUS IN

( SELECT DISTINCT(EFF_STATUS) FROM PSFT88.PSXFERITEM );

---------+---------+---------+---------+---------+---------+---------+---------+

DSNT404I SQLCODE = 12, WARNING: THE UNQUALIFIED COLUMN NAME EFF_STATUS WAS

INTERPRETED AS A CORRELATED REFERENCE

DSNT418I SQLSTATE = 01545 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 0 0 22352 1142404404 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000000' X'00000000' X'00005750' X'4417B534'

X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION