mysql - 右连接与左连接

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

不是真的，因为 RIGHT JOIN 和 LEFT JOIN 是对称的。即:

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)

注意优化器重写中的 LEFT JOIN(表被交换):

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 RIGHT JOIN B != A LEFT JOIN B) 除非 (A INNER JOIN B = A LEFT JOIN B)。这是因为 A RIGHT JOIN B 与 A LEFT JOIN B 不对称(它与 B LEFT JOIN A 对称)。

在您的情况下，A RIGHT JOIN B 将与 A LEFT JOIN B 相同，除非您要加入的列中有 NULL 值。如果有 NULL 值，那么 A LEFT JOIN B 将与 A RIGHT JOIN B 不同。如果您添加新文章而不添加关联的新闻组(反之亦然)，那么结果也会改变。