Toad World® Forums

WHERE a.column IN (SELECT ... issue



The following statement brings no error but all rows from the table xp_protokoll,

although the column protokoll_nr does not exists in the table sap_kwl_wsi_001_paramlog.

However, as far as I found this happens only when no alias is given in the IN clause and the wrong column name is part of the other table (xp_protokoll in my case).

Not a big thing, but can be annoying J






FROM xp_protokoll a


FROM sap_kwl_wsi_001_paramlog)

/* Formatted on 12-Dez-2008 9:45:10 (QP5 v5.120.811.25008) */

SELECT a.protokoll_nr,a.folgesatz_nr,a.unit_name,a.msg_typ,a.username,a.terminal,WHERE a.protokoll_nr IN (SELECT protokoll_nr

Since I think we probably will not hear from each other by the end of the year, I would like to wish you all ( the Navigator team all the honestly testers) a Merry Christmas and a Happy New Year.

It has always been fun with you and I’m sure it will in 2009 too!

Warm Regards




From what you say what you really have is an error in your query!

You can try it in sqlplus and the result will be same.

you are making a select that could be explain as

select * from table_a where column_a=column_a;

(table_a as a column named column_a)

That will get you all the rows

the subselect you is using as a return value the column of the outer table because it cannot find the column in the inner table (that’s SQL).

that why you should always qualify the columns :slight_smile:

Merry Xmas and Happy new year



Learned something new today - I didn’t know this little quirk!

May the Blessings of your God, Gods, Spirits, Ancestors,… be upon you andbring you health, happiness and prosperity in the New Year.


Hello Filipe,
… yes, I know and will do so from now on till eternity! :slight_smile:
A little justification: I have never produced such a nonsense before…- honestly.

Thank you very much!