While I was studying this sort of query:

  SELECT  * FROM employee e 
  WHERE e.EMP_NO = coalesce ( ?, e.EMP_NO)

it became obvious that if the parameter is null it resolves to this
sort of query:

  SELECT  * FROM employee e 
  WHERE e.EMP_NO = e.EMP_NO

which produces the same result set as

  SELECT  * FROM employee e 

Performance wise there is no difference that I can tell. 

However, is there a downside to using 

  WHERE e.EMP_NO = e.EMP_NO

to get a full result set ?

ie, is this just an implementation artefact, or is this correct SQL
that is always guaranteed to produce the same result as if the where
clause had not been specified ?


I've also seen, in stored procedures, this sort of construct...

  where (( e.EMP_NO = :AEMP_NO  ) OR ( :AEMP_NO IS NULL))

which, if the input parameter AEMP_NO is NULL will also behave 
as if a full result set was requested. ie, the stored procedure will
return a single record if AEMP_NO has a value and the entire table if
it is null. The advantage of using that sort of construct is that only
one SQL statement is required, rather than a more complex 'if then
else' but is it safe from future implementation changes ?


Paul
-- 
Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird
 

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to