Hi I am wanting to write a query that shows accounts related to a selected user An example of my code so far can be seen below. I just need to know what goes where the
|
In order to use wildcards like '%' in a comparison, you have to use the LIKE operator (or SIMILAR/REGEXP for v11 and above), so the following should work:
In contrast, in your sample, "createdby = '%'" would only evaluate to true for the value '%' itself. I'm assuming that createdby is declared as NOT NULL, otherwise you would need to handle NULL values, as well. Note, a more efficient (and comprehensible IMHO) condition, also treating possible NULLs for createdby correctly, would be to use
Note that using either of Volker's suggestions will require a table scan since the predicates are not sargable and therefore a table scan will be required. An even more efficient way of handling this case would be to test if @user is null outside of the query and then query all users or just the one appropriately. I.e.
Provided there is an index on createdby then the ELSE clause query can do an easy index lookup.
(22 Jul '16, 08:29)
Mark Culp
Replies hidden
1
Mark, could you please tell why "WHERE createdby = @user or @user is null" is not sargable? - I would think the optimizer will decide whether @user (treated as a constant for that query) is null or not and would then use the sargable predicate "x = someValue" (in case x is indexed, of course) or would return all rows (thereby necessarily doing a table scan) otherwise. Or do you relate to ASA9 which might work different than current versions here?
(22 Jul '16, 08:49)
Volker Barth
2
Volker: You are correct. I tested v17 and it showed that the latest 17.0.4 optimizer is smart enough to do some work with the constants to eliminate predicates and therefore your suggested solution is good. I did the same test with v9 and the query plan each time (1: @user = NULL, 2: @user not NULL) involves a sequential scan. Without more testing (or looking the code or docs) I do not know at what point the smarts were added to the optimizer.
(22 Jul '16, 09:48)
Mark Culp
Thanks for all the additional comments.
(27 Jul '16, 09:16)
Jongee
|