Result set from where afield = afield

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Result set from where afield = afield

Paul Reeves

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 | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set from where afield = afield

Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set from where afield = afield

Dmitry Yemanov-3
In reply to this post by Paul Reeves
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
Loading...