Oracle中创建临时表

with USER_MST as (select 1 as USER_ID, USER1 AS USER_NAME FROM dualunion allselect 2 as USER_ID, USER2 AS USER_NAME FROM dualunion allselect 3 as USER_ID, USER3 AS USER_NAME FROM dualunion allselect 4 as USER_ID, USER4 AS USER_NAME FROM dualunion allselect 5 as USER_ID, USER5 AS USER_NAME FROM dual),USER_EXTEND AS (SELECT 1 AS USER_ID , 100 AS DEPT_ID FROM dualUNION ALLSELECT 2 AS USER_ID , 100 AS DEPT_ID FROM dualUNION ALLSELECT 3 AS USER_ID , 200 AS DEPT_ID FROM dualUNION ALLSELECT 4 AS USER_ID, 200 AS DEPT_ID FROM dualUNION ALLSELECT 6 AS USER_ID, 500 AS DEPT_ID FROM dual)SELECT A.USER_ID, A.USER_NAME,B.DEPT_IDFROM USER_MST A--JOIN USER_EXTEND B ON A.USER_IDB.USER_ID--LEFT JOIN USER_EXTEND B ON A.USER_IDB.USER_ID--RIGHT JOIN USER_EXTEND B ON A.USER_IDB.USER_ID--INNER JOIN USER_EXTEND B ON A.USER_IDB.USER_ID--LEFT JOIN USER_EXTEND B ON A.USER_IDB.USER_ID WHERE B.DEPT_ID100RIGHT JOIN USER_EXTEND B ON A.USER_IDB.USER_ID WHERE B.DEPT_ID100※需要注意 right join B 表 时select from A 当A.USER_ID 不存在时 不出力 为null