how to write a search query in SQL

Question

I want to write a search query. I have the following scenario.

There are 3 criteria (ID, NAME , CITY) for searching the result. example:

• IF the user enters ID = 123, then all the rows with ID = 123 should be fetched irrespective of name and city
• IF the user enters ID = 123 and name = 'SAM', then all the rows with ID = 123 and name = 'SAM' should be fetched irrespective of the city
• IF the user enters ID = 123,name = 'SAM' and city = 'NY', then all the rows with the exact match should be fetched.

Answer 1

A typical approach is something like this:

where (id = v_id or v_id is null) and
      (name = v_name or v_name is null) and
      (city = c_city or v_city is null)

Answer 2

The answer provided by Gordon Linoff is definitely good and workable and straight-forward, but it does not address the performance issues in cases like yours.

Namely, the table you are searching against could be very large and have various indexes (say, in your case, an index on NAME and another index on CITY). In that case, a simple, static SQL approach may use one index or another and perform poorly in cases where the criteria for the index it is using is not supplied. (I know Oracle 11g introduced adaptive cursor sharing -- I have yet to see it be really helpful though. I'd like to believe it could make my answer outdated and it should make my answer outdated. But I just haven't seen it really work very well yet. I'd welcome comments about it).

Anyway, if you don't have 11g and/or do not want to rely to heavily on adaptive cursor sharing, I think the current best practice for writing such search queries to to use a REF CURSOR. Like this:

-- Create a table to query from
CREATE TABLE matt1 ( id number, name varchar2(30), city varchar2(30) );

CREATE OR REPLACE PACKAGE matt_query_pkg AS
  FUNCTION get_results ( p_id NUMBER, p_name VARCHAR2, p_city VARCHAR2 ) RETURN SYS_REFCURSOR;
END matt_query_pkg;

CREATE OR REPLACE PACKAGE BODY matt_query_pkg AS
  FUNCTION get_results ( p_id NUMBER, p_name VARCHAR2, p_city VARCHAR2 )RETURN SYS_REFCURSOR IS
    l_rc SYS_REFCURSOR;
    l_sql VARCHAR2(32000);
  BEGIN
    l_sql := 'SELECT id, name, city FROM matt1 WHERE 1=1';
    if p_id IS NULL THEN
      l_sql := l_sql || ' AND (1=1 OR :b_id IS NULL)';
    else
      l_sql := l_sql || ' AND (id = :b_id)';
    end if;

    if p_name IS NULL THEN
      l_sql := l_sql || ' AND (1=1 OR :b_name IS NULL)';
    else
      l_sql := l_sql || ' AND (name = :b_name)';
    end if;

    if p_city IS NULL THEN
      l_sql := l_sql || ' AND (1=1 OR :b_city IS NULL)';
    else
      l_sql := l_sql || ' AND (id = :b_city)';
    end if;

    dbms_output.put_line('Executing:');
    dbms_output.put_line(l_sql);

    OPEN l_rc FOR l_sql USING p_id, p_name, p_city;

    RETURN l_rc;
  END get_results;
END matt_query_pkg;

In an example case where only the ID and NAME criteria is given, it will generate a SQL like this:

SELECT id, name, city 
FROM matt1 
WHERE 1=1 
AND (id = :b_id) 
AND (name = :b_name) 
AND (1=1 OR :b_city IS NULL)

Oracle's optimizer will factor out the (1=1 OR :b_city IS NULL) clause (because it knows it is always true), leaving a SQL that it can then optimize specifically for the criteria given.

NOTE: the point of putting in the (1=1 OR :b_city IS NULL) clause at all is to keep the number of bind variables constant, so you can always run the search using:

OPEN l_rc FOR l_sql USING p_id, p_name, p_city;

If you didn't put in that (1=1 OR...) clause, you'd have to have a different OPEN..FOR statement for every possible combination of null/not null input parameters.

Here is some code to test it, for reference:

-- Test it
insert into matt1 values (1, 'Fred', 'New York');
insert into matt1 values (2, 'Fred', 'Philadelphia');
insert into matt1 values (3, 'John', 'Philadelphia');
insert into matt1 values (4, 'Mark', 'Philadelphia');
insert into matt1 values (5, 'Mark', 'Chicago');
commit;

declare
  l_rc SYS_REFCURSOR;
  l_id NUMBER;
  l_name VARCHAR2(30);
  l_city VARCHAR2(30);   
begin
  l_rc := matt_query_pkg.get_results (NULL, 'Fred', NULL);
  loop
    fetch l_rc INTO l_id, l_name, l_city;
    exit when l_rc%NOTFOUND;
    dbms_output.put_line ('Found: ' || l_id || ', ' || l_name || ', ' || l_city); 
  end loop;
end;

Answer 3

While this is a relatively simple example with a relatively simple solution, I am very hesitant to endorse the approach of trying to build one generic SQL statement to handle all possibilities. I have seen some very complex examples of this, and while it works functionally, the optimizer has not hope of getting a good cardinality estimate, and subsequently a good execution plan. SQL is not inherently a procedural language; whereas PL/SQL or Java or C# or what ever, are. As such, the are good at conditions :) So, why not do something like this, (logically)

If Only ID specified, then run this SQL

select * from table where id = v_id;
;

If ID and Name specified then run this SQL

select * from table
where id = v_id
and name = v_name
;

If all three specified then run this SQL

select * from table
where id = v_id
and name = v_name
and city = v_city

The result is you have three very easily optimizable SQL statements, as opposed to one hard one.

Now the argument is always, "but I have some may queryable fields, there are too many combinations". This is where you earn your salary (or consulting fee!). You need to be pragmatic and break it up into "sets" of queries, so that you have a reasonable compromise between a SQL for every possible combination and one hugely complex unoptimizable SQL. I hope this makes sense.

Answer 4

I think this will work:

SELECT  *
    FROM    myTable AS t1
    WHERE   t1.ID = 123
            OR (
                    t1.ID = 123
                    AND t1.NAME = 'SAM'
                )
            OR (
                    t1.ID = 123
                    AND t1.NAME = 'SAM'
                    AND t1.CITY = 'NY'
                )