subquery runs for each record?

Question

my doubt is if i write subquery in where clause , then this subquery will execute for each record or only once. for example,

select *
from emp
where empno in (select empno from emp);

and if i have query like this then what will happen?

SELECT *
FROM emp
WHERE EXISTS (SELECT 'RANDOM' FROM dual)

Answer 1

SQL queries represent the result set that is produced, not the specific steps for generating it. That is, SQL is a descriptive language, not a procedural language.

In fact, what actually gets run is typically a directed acyclic graph (DAG) of operations that has very little resemblance to the original query. Not only is the query converted into nodes of the graph but the optimization step adds further remoteness from the original query.

It is a mistake to think that the subquery will actually run for every row in the outer query. It is totally fine to understand the results of the query this way, but the engine can do whatever it wants, such as convert the subquery into a join, so long as it guarantees that the results specified by the query are what is produced.

And in fact very few SQL databases would actually run the subquery for every row in the outer table, in either query that you wrote.