Are conditional subqueries optimized out, if the condition is false?

Question

I have a table foo and a table bar, where each foo might have a bar (and a bar might belong to multiple foos).

Now I need to select all foos with a bar. My sql looks like this

SELECT * 
  FROM foo f 
 WHERE [...] 
   AND ($param IS NULL OR (SELECT ((COUNT(*))>0) 
                             FROM bar b 
                            WHERE f.bar = b.id))

with $param being replaced at runtime.

The question is: Will the subquery be executed even if param is null, or will the dbms optimize the subquery out?

We are using mysql, mssql and oracle. Is there a difference between these regarding the above?

Answer 1

In this case I recommend doing the optimization yourself in the application code, rather than relying on the optimizer from 3 different RDBMSs to consistently handle this the way you want.

If $param is null, just select from foo table. If not, join to the bar table.

pseudo-code:

if ($param is null)
  SELECT * 
  FROM foo f 
  WHERE [...] 
else
  SELECT distinct f.* 
  FROM foo f 
  inner join bar b on f.bar = b.id
  WHERE [...] 
end if

Answer 2

It depends. If you are passing that query to the DBMS each time, then the compiler should realize that it does not need to call the subquery. If it is a static procedure, then it depends on how the compiler stores the execution plan. If it stores the execution plan the first time the procedure is called and the first time it is called $param is not null, then it might actually call the subquery each time the procedure is run.

On a different note, you should consider Exists instead of Count(*) for this type of check

Select ..
From foo f
Where ....
    And ( $param Is Null
            Or Exists   (
                        Select 1
                        From bar b
                        Where b.id = f.bar
                        ))