Re: [Firebird-devel] Result set from where afield = afield
10.07.2017 15:11, Paul Reeves wrote: 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 ? Well-known trick and FB3 supports it at the runtime/optimizer level (using either index scan or full scan depending on parameter value). Dmitry -- 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
Re: [Firebird-devel] Result set from where afield = afield
On 10/07/2017 09:11, Paul Reeves wrote: > 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 > > Only when e.EMP_NO is NOT NULL. The query is really similar to: SELECT * FROM employee e where e.EMP_NO is not null Adriano -- 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
[Firebird-devel] Result set from where afield = afield
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
