<\/p>\n
Table joins are set operations (\u96c6\u5408\u8fd0\u7b97); they are not merely to retrieve data by using the FKs defined on the tables.<\/p>\n
The \u201cLoop Query\u201d means using \u201cprocedural processing logic\u201d 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.
\nFor example, the SQL statement (using table join) <\/p>\n
SELECT t1.col1, t2.col2 FROM tab1 t1, tab2 t2 WHERE t1.key# = t2.join_field;<\/code><\/pre>\n\u2026can be rewritten by using \u201cLoop Query\u201d like below\u2026 <\/p>\n
FOR rec in (SELECT key#, col1 FROM tab1) LOOP SELECT col2 FROM tab2 WHERE join_field = rec.key#; END LOOP;<\/code><\/pre>\nIf 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 \u201cloop query\u201d might performs better than Join sometimes. However, we can rewrite the general table join by taking advantage of some techniques (like \u201cinline view\u201d, \u201cscalar sub-query\u201d, etc), which can make the table join performs well.
\nExample 1:<\/p>\n
SELECT a.fld1, \u2026\u2026, b.col1, \u2026.. FROM tab2 b, tab1 a WHERE a.key1 = b.key2 AND a.fld1 = '10' ORDER BY a.fld2<\/code><\/pre>\nCan be rewritten using inline view as follows, <\/p>\n
SELECT x.fld1, \u2026., x.fldn, y.col1\u2026.., y.coln FROM (SELECT fld1, \u2026.., fldn FROM tab1 WHERE fld = '10' ORDER BY fld2) x, Tab2 y WHERE y.key2 = x.key1<\/code><\/pre>\nExample 2: <\/p>\n
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#<\/code><\/pre>\nCan be rewritten as follows\u2026 <\/p>\n
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#<\/code><\/pre>\nExample 3: <\/p>\n
SELECT a.*, decode(a.type, \u20181\u2019, b.client_name, \u20182\u2019, project_name) name FROM tab a, clients b, projects c WHERE a.issue_date like '200503%' AND b.client_no(+) = decode(a.type, \u20181\u2019, a.type_code) AND c.project_no(+) = decode(a.type, \u20182\u2019, a.type_code)<\/code><\/pre>\nCan be rewritten using scalar sub-query as follows\u2026<\/p>\n
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%'<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Table Joins<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,48],"tags":[],"class_list":["post-1334","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-sql"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1334","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1334"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1334\/revisions"}],"predecessor-version":[{"id":1335,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1334\/revisions\/1335"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1334"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}