JOIN vs. Subselect (Was: This SELECT hangs ...)

alan@effluvia.den.mmc.com (Alan Popiel) writes: >When working with even moderately large tables, I get better performance >using "where col_x in (select ... )" then with joins. Perhaps the "IN" >syntax avoids the cartesian product problem. I have had the exact same experience. I was taught that a join is always preferable over a subselect, but when the table(s) are getting large, performance really sucks. A subselect on the large table speeds things up a lot. Could one of the gurus please comment on this? From what table size onward is a subselect preferable over a join? Are there other things I could do to speed up performance on large tables when they are joined (apart from using indices, which I am doing anyway)? Regards, Richard -- +----------------------------+-------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3421 | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | Munich, Germany | | +----------------------------+-------------------------------------------+