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