inner left right join 小例子!让你一目了然记住三种连接区别

---首先创建两个表 左表和右表

SQL> CREATE TABLE L AS

  2  SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL
  3  SELECT 'left_2','2' AS v FROM dual UNION ALL
  4  SELECT 'left_3','3' AS v FROM dual UNION ALL
  5  SELECT 'left_4','4' AS v FROM dual;


表已创建。       
SQL> CREATE TABLE R AS
  2  SELECT 'right_3' AS str,'3' AS v,1 AS status FROM dual UNION ALL
  3  SELECT 'right_4' AS str,'4' AS v,0 AS status FROM dual UNION ALL
  4  SELECT 'right_5' AS str,'5' AS v,0 AS status FROM dual UNION ALL
  5  SELECT 'right_6' AS str,'6' AS v,0 AS status FROM dual;


表已创建。

--查询下L R表数据

SQL> select * from l;


STR    V
------ -
left_1 1
left_2 2
left_3 3
left_4 4


SQL> select * from r;


STR     V     STATUS
------- - ----------
right_3 3          1
right_4 4          0
right_5 5          0
right_6 6          0

---分别显示下 inner join ,left join, right join查询处的数据

SQL> 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;


LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4

SQL> 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;


LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4 right_4

 

SQL> 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;


LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6

左右连接还有    L.v = R.v(+)      L.v(+)  = R.v  这样写法！ （+）意识是没有数据的用空值填充

egg,关于一个表添加条件是，条件应该加在哪

----对于其中的R表，我们需要只显示其中status = 1的部分    

--true --
SELECT L.str AS LEFT_str,R.str AS RIGHT_str FROM L LEFT JOIN （select * from R where status=1）R ON L.v = R.v ORDER BY 1,2;
 
--error--

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