Table Joins

Table joins are set operations (集合运算); they are not merely to retrieve data by using the FKs defined on the tables.

Join VS Loop Query

The “Loop Query” means using “procedural processing logic” to replace the table join operation. It will first query the data from one table and then use the results (a list of constant values) to probe the final result from the other table in one loop.
For example, the SQL statement (using table join)

SELECT t1.col1, t2.col2 FROM tab1 t1, tab2 t2 WHERE t1.key# = t2.join_field;

…can be rewritten by using “Loop Query” like below…

FOR rec in (SELECT key#, col1 FROM tab1) LOOP SELECT col2 FROM tab2 WHERE join_field = rec.key#; END LOOP;

If the SQL (table join) statement involves some operations (like order by, group by, etc) which makes the SQL cannot return the first set of results before processing the whole data set, the “loop query” might performs better than Join sometimes. However, we can rewrite the general table join by taking advantage of some techniques (like “inline view”, “scalar sub-query”, etc), which can make the table join performs well.
Example 1:

SELECT a.fld1, ……, b.col1, ….. FROM tab2 b, tab1 a WHERE a.key1 = b.key2 AND a.fld1 = '10' ORDER BY a.fld2

Can be rewritten using inline view as follows,

SELECT x.fld1, …., x.fldn, y.col1….., y.coln FROM (SELECT fld1, ….., fldn FROM tab1 WHERE fld = '10' ORDER BY fld2) x, Tab2 y WHERE y.key2 = x.key1

Example 2:

SELECT b.dept_name, sum(a.sale_money) FROM tab1 a, tab2 b WHERE a.dept# = b.dept# AND a.sale_date like '200503%' GROUP BY b.dept#

Can be rewritten as follows…

SELECT x.dept#, y.dept_name, sale_money FROM (SELECT dept#, sum(sale_money) sale_money FROM tab1 WHERE sale_date like '200503%' GROUP BY dept#) x, TAB2 y WHERE y.dept# = x.dept#

Example 3:

SELECT a.*, decode(a.type, ‘1’, b.client_name, ‘2’, project_name) name FROM tab a, clients b, projects c WHERE a.issue_date like '200503%' AND b.client_no(+) = decode(a.type, ‘1’, a.type_code) AND c.project_no(+) = decode(a.type, ‘2’, a.type_code)

Can be rewritten using scalar sub-query as follows…

SELECT a.*, (SELECT client_name FROM clients b WHERE b.client_no = a.type_code), (SELECT project_name FROM projects c WHERE c.project_no = a.type_code) FROM tab a WHERE a.issue_date like '200503%'