JOIN vs. WHERE ... IN (subselect)
От | Dean Gibson (DB Administrator) |
---|---|
Тема | JOIN vs. WHERE ... IN (subselect) |
Дата | |
Msg-id | 5.1.0.14.2.20030516231811.02deac70@imaps.ultimeth.net обсуждение исходный текст |
Ответы |
Re: JOIN vs. WHERE ... IN (subselect)
Re: JOIN vs. WHERE ... IN (subselect) |
Список | pgsql-sql |
Using PostgreSQL 7.3.2 on Linux. One of the types of queries that I like to do on our database is: SELECT * FROM table1 WHERE indexed_column1 IN (SELECT column2 FROM table2 WHERE <condition>); However, in our database table1 is quite large (~1M rows), and the above query takes "forever", EVEN IF table2 CONSISTS OFONLY ONE ROW! However, my third-party SQL book says that the above is equivalent to: SELECT table1.* FROM table1, table2 WHERE indexed_column1 = column2 AND <condition>; And indeed, the above query executes virtually instantaneously if "<condition>" results in a small number of rows from table2. I'm fairly new to SQL; are they really equivalent? If so, is there some reason that the first form is not optimized/transformedinto the second form, or is this a planned enhancement? -- Dean ps: If indexed_column1 has the same name as column2, then the query: SELECT table1.* from table1 JOIN table2 USING( column ) WHERE <condition>; Also executes quickly. I just like the readability of the first query above, but as they say, you gotta do what works ...
В списке pgsql-sql по дате отправления: