文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> OUTER JOIN

OUTER JOIN

分享
更新时间: 2019/08/09 GMT+08:00

OUTER QUERY (+)

Gauss 18.2.0支持Join,所以需添加配置参数。

配置参数取决于如下配置项:

supportJoinOperator=false

输入

SELECT PP.PUBLISH_NO
FROM SPMS_PARAM_PUBLISH PP
WHERE PP.PUBLISH_ID(+) = TB2.PUBLISH_ID;
SELECT  I.APP_CHNAME, I.APP_SHORTNAME
FROM SPMS_APPVERSION SA, SPMS_APP_INFO I
WHERE SA.APP_ID = I.APP_ID(+)
AND SA.DELIVERY_USER = IN_USERID
ORDER BY  APPVER_ID DESC ;

输出

SELECT
PP.PUBLISH_NO
FROM
SPMS_PARAM_PUBLISH PP
WHERE
PP.PUBLISH_ID (+) = TB2.PUBLISH_ID;
SELECT
I.APP_CHNAME
,I.APP_SHORTNAME
FROM
SPMS_APPVERSION SA
,SPMS_APP_INFO I
WHERE
SA.APP_ID = I.APP_ID (+)
AND SA.DELIVERY_USER = IN_USERID
ORDER BY
APPVER_ID DESC;

OUTER JOIN : 1st_table = 2nd_table (+)

输入

SELECT empno, ename, job, dname, loc
FROM emp, dept
WHEREemp.deptno = dept.deptno (+)
     AND salary > 50000;

输出

SELECT empno, ename, job, dname, loc
FROM emp LEFT OUTER JOIN dept
    ON emp.deptno = dept.deptno
 WHERE salary > 50000;

OUTER JOIN : 2nd_table = 1st_table (+)

输入

SELECT empno, ename, job, dname, loc
FROM emp, dept
WHERE dept.deptno = emp.deptno (+)
      AND salary > 50000;

输出

SELECT empno, ename, job, dname, loc
FROM dept LEFT OUTER JOIN emp
ON dept.deptno = emp.deptno
 WHERE salary > 50000;

OUTER JOIN : 1st_table (+) = 2nd_table

输入

SELECT empno, ename, job, dname, loc
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno
      AND salary > 50000;

输出

SELECT empno, ename, job, dname, loc
FROM emp RIGHT OUTER JOIN dept
ON emp.deptno = dept.deptno
WHERE salary > 50000;

OUTER JOIN : 2nd_table (+) = 1st_table

输入

SELECT empno, ename, job, dname, loc
FROM emp, dept
WHERE dept.deptno (+) = emp.deptno
       AND salary > 50000;

输出

SELECT empno, ename, job, dname, loc
FROM dept RIGHT OUTER JOIN emp
ON dept.deptno = emp.deptno
 WHERE salary > 50000;

使用表别名的OUTER JOIN

输入

SELECT s.supplier_id, s.supplier_name, o.order_date
FROM suppliers s, orders o
WHERE s.supplier_id = o.supplier_id ( + )
      AND s.suppller_id > 1000;

输出

SELECT s.supplier_id, s.supplier_name, o.order_date
FROM suppliers s LEFT OUTER JOIN orders o
ON s.supplier_id = o.supplier_id
 WHERE s.suppller_id > 1000;

多连接和表达式OUTER JOIN

输入

SELECT b.zoneno, b.phybrno, t1.zoneno, t1.brno
FROM schema1.tab1 B, schema2.tab2 t1
WHERE SUBSTR(b.zoneno, 1, 4) = SUBSTR(t1.zoneno, 2, 4) (+)
AND SUBSTR(b.phybrno, 1, 4) = SUBSTR(t1.brno, 2, 4)(+);

输出

SELECT b.zoneno, b.phybrno, t1.zoneno, t1.brno
FROM schema1.tab1 B LEFT OUTER JOIN schema2.tab2 t1
ON SUBSTR(b.zoneno, 1, 4)   = SUBSTR(t1.zoneno,
2, 4)
AND SUBSTR(b.phybrno, 1, 4) = SUBSTR(t1.brno, 2,
4);

多表和子查询OUTER JOIN

输入

SELECT stru_id, organ1_no, SUBSTR(p_i_date, 1, 8), v_task_date1, TA000
FROM ccm_TA280362_h d
, ( SELECT * FROM tab1 where
table_code = 'DA200251007' ORDER
BY 1 ) d1
, ( SELECT D.dict_cd, D.dict_name
FROM tab2 D WHERE work_dt =
SUBSTR(p_i_date, 1, 6) ) d2
, ( SELECT * FROM tab3 where
table_code = 'DA200251011' ) d3
, ( SELECT tab4.dict_cd, tab5.c2
FROM tab4, tab5
WHERE tab5.c1 (+) = tab4.c1
 AND table_code = 'DA200251012') d4
WHERE d.TA005 = d1.dict_cd(+)
AND d.TA007 = d2.dict_cd(+)
AND d.TA012 = d3.dict_cd(+)
AND d.TA013 = d4.dict_cd(+)
      AND d.TA000 = v_task_date1;

输出

SELECT stru_id, organ1_no, SUBSTR(p_i_date, 1, 8), v_task_date1, TA000
FROM ccm_TA280362_h d
LEFT OUTER JOIN
( SELECT * FROM tab1 where table_code =
'DA200251007' ORDER BY 1 ) d1
ON d.TA005 = d1.dict_cd
LEFT OUTER JOIN
( SELECT D.dict_cd, D.dict_name FROM tab2 D WHERE
work_dt = SUBSTR(p_i_date, 1, 6) ) d2
ON d.TA007 = d2.dict_cd
LEFT OUTER JOIN
( SELECT * FROM tab3 where table_code =
'DA200251011' ) d3
ON d.TA012 = d3.dict_cd
LEFT OUTER JOIN
( SELECT tab4.dict_cd, tab5.c2
FROM tab5 RIGHT OUTER JOIN tab4
ON tab5.c1 = tab4.c1
WHERE table_code = 'DA200251012' ) d4
ON d.TA013 = d4.dict_cd
WHERE d.TA000 = v_task_date1;

静态值OUTER JOIN

输入

SELECT d.department_name, e.employee_name
FROM departments d, employees e
WHERE d.department_id   = e.department_id (+)
AND e.salary (+)         >= 2000
AND d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

输出

SELECT d.department_name, e.employee_name
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id  = e.department_id
AND e.salary            >= 2000
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区