右连接与左连接

Question

在这种情况下，左连接与右连接是相同的吗？

mysql> 
mysql> 
mysql> use usenet;show tables;describe ARTICLE;describe NEWSGROUP;
Database changed
+------------------+
| Tables_in_usenet |
+------------------+
| ARTICLE          |
| NEWSGROUP        |
+------------------+
2 rows in set (0.00 sec)

+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| ID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| MESSAGENUMBER | int(11)    | YES  |     | NULL    |                |
| NEWSGROUP_ID  | bigint(20) | YES  | MUL | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| NEWSGROUP | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from ARTICLE right join NEWSGROUP on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID;
+------+---------------+--------------+----+-------------------------------+
| ID   | MESSAGENUMBER | NEWSGROUP_ID | ID | NEWSGROUP                     |
+------+---------------+--------------+----+-------------------------------+
|    1 |             4 |            1 |  1 | gwene.com.androidcentral      |
|    2 |             5 |            1 |  1 | gwene.com.androidcentral      |
|    3 |             6 |            1 |  1 | gwene.com.androidcentral      |
|    4 |             7 |            1 |  1 | gwene.com.androidcentral      |
|    5 |             8 |            1 |  1 | gwene.com.androidcentral      |
|    6 |             9 |            1 |  1 | gwene.com.androidcentral      |
|    7 |            10 |            1 |  1 | gwene.com.androidcentral      |
|    8 |            11 |            1 |  1 | gwene.com.androidcentral      |
|    9 |             4 |            2 |  2 | gwene.com.blogspot.emacsworld |
|   10 |             4 |            3 |  3 | gwene.com.blogspot.googlecode |
|   11 |             5 |            3 |  3 | gwene.com.blogspot.googlecode |
|   12 |             6 |            3 |  3 | gwene.com.blogspot.googlecode |
|   13 |             7 |            3 |  3 | gwene.com.blogspot.googlecode |
|   14 |             8 |            3 |  3 | gwene.com.blogspot.googlecode |
|   15 |             9 |            3 |  3 | gwene.com.blogspot.googlecode |
|   16 |            10 |            3 |  3 | gwene.com.blogspot.googlecode |
|   17 |            11 |            3 |  3 | gwene.com.blogspot.googlecode |
|   18 |             4 |            4 |  4 | gwene.com.economist           |
|   19 |             5 |            4 |  4 | gwene.com.economist           |
|   20 |             6 |            4 |  4 | gwene.com.economist           |
|   21 |             7 |            4 |  4 | gwene.com.economist           |
|   22 |             8 |            4 |  4 | gwene.com.economist           |
|   23 |             9 |            4 |  4 | gwene.com.economist           |
|   24 |            10 |            4 |  4 | gwene.com.economist           |
|   25 |            11 |            4 |  4 | gwene.com.economist           |
+------+---------------+--------------+----+-------------------------------+
25 rows in set (0.00 sec)

mysql> 
mysql> select * from ARTICLE left join NEWSGROUP on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID;
+----+---------------+--------------+------+-------------------------------+
| ID | MESSAGENUMBER | NEWSGROUP_ID | ID   | NEWSGROUP                     |
+----+---------------+--------------+------+-------------------------------+
|  1 |             4 |            1 |    1 | gwene.com.androidcentral      |
|  2 |             5 |            1 |    1 | gwene.com.androidcentral      |
|  3 |             6 |            1 |    1 | gwene.com.androidcentral      |
|  4 |             7 |            1 |    1 | gwene.com.androidcentral      |
|  5 |             8 |            1 |    1 | gwene.com.androidcentral      |
|  6 |             9 |            1 |    1 | gwene.com.androidcentral      |
|  7 |            10 |            1 |    1 | gwene.com.androidcentral      |
|  8 |            11 |            1 |    1 | gwene.com.androidcentral      |
|  9 |             4 |            2 |    2 | gwene.com.blogspot.emacsworld |
| 10 |             4 |            3 |    3 | gwene.com.blogspot.googlecode |
| 11 |             5 |            3 |    3 | gwene.com.blogspot.googlecode |
| 12 |             6 |            3 |    3 | gwene.com.blogspot.googlecode |
| 13 |             7 |            3 |    3 | gwene.com.blogspot.googlecode |
| 14 |             8 |            3 |    3 | gwene.com.blogspot.googlecode |
| 15 |             9 |            3 |    3 | gwene.com.blogspot.googlecode |
| 16 |            10 |            3 |    3 | gwene.com.blogspot.googlecode |
| 17 |            11 |            3 |    3 | gwene.com.blogspot.googlecode |
| 18 |             4 |            4 |    4 | gwene.com.economist           |
| 19 |             5 |            4 |    4 | gwene.com.economist           |
| 20 |             6 |            4 |    4 | gwene.com.economist           |
| 21 |             7 |            4 |    4 | gwene.com.economist           |
| 22 |             8 |            4 |    4 | gwene.com.economist           |
| 23 |             9 |            4 |    4 | gwene.com.economist           |
| 24 |            10 |            4 |    4 | gwene.com.economist           |
| 25 |            11 |            4 |    4 | gwene.com.economist           |
+----+---------------+--------------+------+-------------------------------+
25 rows in set (0.00 sec)

mysql> 
mysql> 

Answer 1

Codeproject有这个图像，它解释了SQL join的简单基础，取自：http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx SQL join explained

​

Answer 2

不完全是，因为右连接和左连接是对称的。这就是：

A LEFT JOIN B = B RIGHT JOIN A

RIGHT JOIN只是语法上的糖。优化器可以将右连接重写为左连接：

mysql> explain extended select * from t right join t t2 using (c1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: index
possible_keys: NULL
          key: c2
      key_len: 5
          ref: NULL
         rows: 4201
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.t2.c1
         rows: 1
     filtered: 100.00
        Extra: 
2 rows in set, 1 warning (0.00 sec)

注意优化器重写中的左连接(表被交换)：

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t`.`c2` AS
`c2` from `test`.`t` `t2` left join `test`.`t` on((`test`.`t`.`c1` = `test`.`t2`.`c1`))  where 1
1 row in set (0.00 sec) 

请注意，(A右连接B != A左连接B)除非(A内部连接B=A左连接B)。这是因为A右连接B与A左连接B不对称(它与B左连接A对称)。

在您的示例中，A右连接B将与A左连接B相同，除非您要连接的列中存在空值。如果有空值，则A左连接B将与A右连接B不同。如果您添加新文章而不添加关联的新闻组(反之亦然)，则结果也将改变。

Answer 3

对于您当前的数据，是的，它们是相同的。但是，由于NEWSGROUP_ID可以为空，因此它们可能会随着数据的变化而不同。

就我个人而言，如果可能，我总是使用左连接(从主表到子表)，事实上，在超过6年的SQL开发中，我只需要使用右连接几次！