If we re-access the table from the main query, we consider it "from scratch" or in a truncated version of the main question. For example:

SELECT DISTINCT IDX1.<столбец_1> FROM <таблица_1> IDX1 INNER JOIN <таблица_2> IDX2 ON IDX1.<столбец_n> = IDX2.<столбец_m> AND IDX2.<столбец_1> IN (<список>) WHERE NOT EXISTS (SELECT <таблица_1>.* FROM <таблица_1> IDX1 WHERE <условия>)

So, referring to the <таблице_1> in the subquery, are we dealing with a complete table, or a truncated condition imposed when joining <таблицы_2> ?

  • In general terms, nothing can be said. the optimizer can convert not exists, for example, to not in or to the left join. And perform it at all only once, if it is not correlated. Those. No one said that the optimizer will do what is written here. he can easily execute the subquery first, and then select the appropriate one from the tables at the top. And maybe vice versa, I downloaded to fulfill all the conditions in ON (or only some of them work with one table so far) and then execute a subquery for the results. So the answer is one - see explain plan for a specific situation - Mike

2 answers 2

When accessing a table in a subquery, only those conditions that are written in this subquery will work.

For the sake of fairness, it should be noted that in the subquery you can refer to the tables of a higher level and then they may have general conditions. For example:

 Select * from Table1 t1 where exists (select 1 from Table1 t2 where t1.col1 = t2.col2)

    In general, if the goal is optimization, then you can use WITH ... CLAUSE and the hint of MATERIALIZE :

     with view_name as ( select /*+ materialize */ from table_name where ... ) select ... from view_name v1 where col_name not in (select col_name from view_name);

    In your case, you should try the variant with set difference and compare the execution plans:

     SELECT DISTINCT IDX1.col1 FROM ( SELECT col1 FROM tab1 MINUS SELECT col1 FROM tab1 WHERE ... ) as IDX1 JOIN tab2 IDX2 ON IDX1.<столбец_n> = IDX2.<столбец_m> AND IDX2.<столбец_1> IN (<список>)'
    • It is worth noting that materialize undocumented hint, and its use is not recommended - Viktorov