So, if we are talking about the OLTP environment, and we want to generate an efficient SQL statement executed from our application, where the user can choose a random set of search criteria, and any of criteria can be omitted. In this case the process of SQL generation (no matter, whether we generate it in the stored procedure, db function or in the programming language method) should look like sketched below, For the first iteration we assume that we only have four tables to join, and that a select list contains attributes from all 4 tables, so that none can be omitted

DECLARE v_select TEXT;

v_select_list TEXT;

v_conditions TEXT;

BEGIN

v_select_list:=<select list>

v_select:=v_select_list ||

‘FROM t1

LEFT OUTER JOIN t2 ON t2.id=t1.t2_id

LEFT OUTER JOIN t3 ON t3.id=t1.t3_id

LEFT OUTER JOIN t4 ONt4.id=t1.t4-id

WHERE‘;

/*Now we start to generate a condition. Remember, that any of the lists of values of any of the attributes can be NULL. */

IF v_string1 IS NOT NULL THEN

v_condition:=’attr1 IN(‘||v_string1||’)’;

AND IF; — first condition added

IF v_string2 IS NOT NULL THEN

IF v_condition is NOT NULL THEN – if we already have something in conditions

v_condition:=v_condition|| “AND ‘ –then we need to add ‘AND’

END IF;

V_coundition :=v_condition||

‘attr2 IN (‘||v_string2||’)’;

END IF;

Then you need to repeat a similar sequence of actions for tables t3 and t4.

Yes, it has to be written in such a “non-automated” way, unless you can write something, which will analyze this list of tables more or less “automatically” in less time than this query generation…

And yes, if some of the tables do not contribute to the select list and at the same time the joins with them are not semi-joins, we can skip the join altogether, if the user did not select any conditions on this table. And yes, that means, that you also need to generate the from-list differently, depending on whether you need all tables in the join, or not… And yes, execution seconds may depend on it…