I have a function to try to match partial data to a database row. I want it to find a match if the parameter is non null; if it's null it should ignore that parameter. If one of the parameters has a value but finds no match, the query returns no rows.
In pseudocode, that's pretty much how it'd go:
get all rows where: param_a matches col_a when param_b is not null else don't check this column AND param_b matches col_b when param_b is not null else don't check this column AND param_c matches col_c when param_c is not null else don't check this column AND param_d matches col_d when param_d is not null else don't check this column AND param_e matches col_e when param_e is not null else don't check this column
What I do right now:
SELECT * FROM table WHERE nvl(param_a, col_a) = col_a AND nvl(param_b, col_b) = col_b AND nvl(param_c, col_c) = col_c AND nvl(param_d, col_d) = col_d;
Etc... It works, but I'm not sure it's the best option. A colleague suggested that I use
SELECT * FROM table WHERE (param_a = col_a or param_a is null) AND (param_b = col_b or param_b is null) AND (param_c = col_c or param_c is null) AND (param_d = col_d or param_d is null);
As this is used for an auto-complete feature in a web application, the query is executed a lot, as the user types. Being fast is essential. The strictest columns are filtered first to reduce the number of rows to process.
Is either of these options preferable? If not, how would you do it?
I wrote the question to be generic, but to put it in context: in this case it's for addresses.
param_a is actually postal code,
param_b street name etc... The function gets the string the user writes (ex: 999 Random St, Fakestate, Countryland, 131ABD) and calls a procedure on it that tries to split it and returns a table containing address, city, country, etc... that is used by the select statement (which is the subject of the question).
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM table WHERE nvl(table.param_a, table.col_a) = table.col_a AND nvl(table.param_b, table.col_b) = table.col_b AND nvl(table.param_c, table.col_c) = table.col_c AND nvl(table.param_d, table.col_d) = table.col_d