ORCALE常用的连接

跟上学时记不住数学公式一样，这个内连接、左连接、右连接、全连接总是记不住。今天写个demo记下来，sql可以copy直接使用。要用的时候，查下就行。O(∩_∩)O哈哈~

--  这里主要比较 inner join / left join / right join / full join 区别

DROP TABLE L PURGE;
DROP TABLE R PURGE;

-- 左表
CREATE TABLE L AS SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL
SELECT 'left_2' ,'2' AS v FROM dual UNION ALL 
SELECT 'left_3', '3' AS v FROM dual UNION ALL 
SELECT 'left_4','4' AS v FROM dual;

SELECT * from L;
SELECT * from R;

-- 右表 数据
CREATE TABLE R AS SELECT 'right_3' AS str,'3' AS v ,1 AS status  FROM dual UNION ALL
SELECT 'right_4' AS str,'4' AS v ,0 AS status  FROM dual UNION ALL
SELECT 'right_5' AS str,'5' AS v ,0 AS status  FROM dual UNION ALL
SELECT 'right_6' AS str,'6' AS v ,0 AS status  FROM dual ;


/* 
第一种 inner join 写法  内连接 其实就是等值连接
该方式返回两表匹配的数据，左表的 '1' , '2' 以及右表的 '5','6' 都没有显示。
*/
SELECT l.Str AS left_str,r.str AS right_str
FROM l
INNER JOIN r ON l.v = r.v
ORDER BY 1,2;


SELECT l.Str AS left_str,r.str AS right_str
FROM r
INNER JOIN l ON l.v = r.v
ORDER BY 1,2;


-- 改为等值连接其实就可以用 where 加条件进行
SELECT l.str AS left_str,r.str AS right_str
FROM l,r
WHERE l.v = r.v
ORDER BY 1,2;


/*
LEFT JOIN 连接的方式
该方式以左表为主表，左表返回所有的数据，右表中只返回与左表相匹配的数据。
根据建表的内容，右表的5，6 都不会显示
*/
SELECT l.str AS left_str, r.str AS right_str 
FROM l
LEFT JOIN r ON l.v = r.v 
ORDER BY 1,2;


--  当然Oracle  最熟悉的是用+号去代替
-- 第一个句子，用 + 号实现如下。 记住口诀： 左连接 加号 在右边
SELECT l.str AS left_str,r.str AS right_str
FROM l,r
WHERE l.v = r.v(+)
ORDER BY 1,2


/*
RIGHT JOIN 连接的方式
该方式以左表为主表，左表返回所有的数据，右表中只返回与左表相匹配的数据。
根据建表的内容,左表的1，2 都不会显示
*/
SELECT l.str AS left_str, r.str AS right_str 
FROM l
RIGHT JOIN r ON l.v = r.v 
ORDER BY 1,2;


SELECT l.str AS left_str,r.str AS right_str
FROM l,r
WHERE l.v(+) = r.v
ORDER BY 1,2


/*
FULL JOIN 连接的方式
该方式以左右表均返回所有的数据，但只有数据匹配的才显示在同一行，非匹配的行，只显示个表数据
*/

SELECT l.str AS left_str,r.str AS right_str
FROM l
FULL JOIN r ON l.v = r.v
ORDER BY 1,2;