在这种情况下,左联接与右联接相同吗?
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>
SQL连接的直观说明。
这意味着values(NEWSGROUP.ID)= value(ARTICLE.NEWSGROUP_ID)
我想为您描述SQL连接
SQL Server中左联接和右联接之间的差异的可能重复
并非如此,因为RIGHT JOIN和LEFT JOIN是对称的。那是:
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内连接B = A左连接B),否则(A右连接B!= A左连接B)。这是因为A RIGHT JOIN B与A LEFT JOIN B不对称(它与B LEFT JOIN A对称)。
在您的情况下,除非您要连接的列中包含NULL值,否则A RIGHT JOIN B将与A LEFT JOIN B相同。如果存在NULL值,则A LEFT JOIN B将与A RIGHT JOIN B不同。如果添加新文章而不添加关联的新闻组(反之亦然),则结果也会改变。
Codeproject的图像说明了SQL连接的简单基础,该图像取自:http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx解释了SQL连接
对于您当前的数据,是的,它们是相同的。但是,由于NEWSGROUP_ID可为空,因此随着数据更改它们可能会有所不同。
就个人而言,我总是尽可能使用LEFT JOIN(从主表到子表),实际上,在6年以上的SQL开发中,我只需要使用RIGHT JOIN几次!