连接查询(内连,外连,交叉)

mysql> select * from t1;
+----+----------+---------+
| id | nickname | playNum |
+----+----------+---------+
|  1 | 1        | 10      |
|  2 | 2        | 20      |
|  3 | 3        | 30      |
+----+----------+---------+

mysql> select * from t2;
+----+----------+---------+
| id | nickname | playNum |
+----+----------+---------+
|  1 | 1        | 10      |
|  2 | 2        | 200     |
|  3 | 33       | 300     |
+----+----------+---------+

inner join：
mysql> SELECT t1.*  FROM  t1 inner JOIN t2 ON t1.playNum = t2.playNum;
+----+----------+---------+
| id | nickname | playNum |
+----+----------+---------+
|  1 | 1        | 10      |
+----+----------+---------+

left join:
mysql> SELECT t1.*  FROM  t1 left  JOIN t2 ON t1.playNum = t2.playNum;
+----+----------+---------+
| id | nickname | playNum |
+----+----------+---------+
|  1 | 1        | 10      |
|  2 | 2        | 20      |
|  3 | 3        | 30      |
+----+----------+---------+

right join:
mysql> SELECT t1.*  FROM  t1 right  JOIN t2 ON t1.playNum = t2.playNum;
+------+----------+---------+
| id   | nickname | playNum |
+------+----------+---------+
|    1 | 1        | 10      |
| NULL | NULL     | NULL    |
| NULL | NULL     | NULL    |
+------+----------+---------+

内连： inner join  ... on

SQL> select * from joina;

        ID NAME
---------- -------------------
         1 1
         2 2
         3 3
         4 4
         5 5
         5 5

SQL> select * from joinb;

        ID NAME
---------- ----------------
         1 1
         2 2
         3 3
         4 4
         6 6
         6 6

SQL> select a.id from joina a inner join joinb b on (a.id = b.id);

        ID
----------
         1
         2
         3
         4

外连又分为3种：

left  join ... on

SQL> select a.id  from joina a left join joinb b on (a.id = b.id);

        ID
----------
         1
         2
         3
         4
         5
         5

SQL> select a.id  from joina a , joinb b where a.id = b.id(+);

        ID
----------
         1
         2
         3
         4
         5
         5

right join ...on 

SQL> select a.id  from joina a right join joinb b on (a.id = b.id);

        ID
----------
         1
         2
         3
         4



已选择6行。

SQL> select a.id  from joina a , joinb b where a.id(+) = b.id;

        ID
----------
         1
         2
         3
         4



已选择6行。//有2行为NULL值

full  join ...on 

SQL> select a.id  from joina a full join joinb b on (a.id = b.id);

        ID
----------
         1
         2
         3
         4
         5
         5



已选择8行。//有2行为NULL值

交叉连：cross join 笛卡尔积

SQL> select * from joina;

        ID
----------
         1
         2
         3

SQL> select * from joinb;

        ID
----------
         4
         5
         6

SQL> select a.id ,b.id from joina a cross join joinb b;

        ID         ID
---------- ----------
         1          4
         2          4
         3          4
         1          5
         2          5
         3          5
         1          6
         2          6
         3          6

已选择9行。

自然连接：natural  join   注意：自然连接只能发生在两个表中有相同名字和数据类型的列上。

SQL> select * from n1;

        ID NAME                        ID2
---------- -------------------- ----------
         1 a                             3
         2 b                             6
         3 c                             7

SQL> select * from n2;

       ID3 NAME                        ID4
---------- -------------------- ----------
         2 c                             7
         3 d                             5
         1 a                             3

SQL> select * from n1 natural join n2;

NAME                         ID        ID2        ID3        ID4
-------------------- ---------- ---------- ---------- ----------
c                             3          7          2          7
a                             1          3          1          3

自然连接分为3步：

1：表n1 和n2的笛卡尔积

SQL> set lin 150
SQL> select * from n1 cross join n2;

        ID NAME                        ID2        ID3 NAME                        ID4
---------- -------------------- ---------- ---------- -------------------- ----------
         1 a                             3          2 c                             7
         1 a                             3          3 d                             5
         1 a                             3          1 a                             3
         2 b                             6          2 c                             7
         2 b                             6          3 d                             5
         2 b                             6          1 a                             3
         3 c                             7          2 c                             7
         3 c                             7          3 d                             5
         3 c                             7          1 a                             3

已选择9行。

2：然后就是选择n1.name=n2.name

SQL> select * from n1 a cross join n2 b where b.name=a.name;

        ID NAME                        ID2        ID3 NAME                        ID4
---------- -------------------- ---------- ---------- -------------------- ----------
         3 c                             7          2 c                             7
         1 a                             3          1 a                             3

3：去掉相同列且值也相同的重复列

SQL> select a.id ,a.name ,a.id2 ,b.id3 ,b.id4 from n1 a cross join n2 b where b.name=a.name;

        ID NAME                        ID2        ID3        ID4
---------- -------------------- ---------- ---------- ----------
         3 c                             7          2          7
         1 a                             3          1          3