基本資料：mysql> select version();+-----------+| version() |+-----------+| 5.0.16 |+-----------+mysql> select * from t1;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+mysql> select * from t2;+------+-------+| code | price |+------+-------+| 1 | 100 || 3 | 900 || 5 | 2500 |+------+-------+內連，左連，右連都正確：mysql> select * from t1 inner join t2 on t1.id = t2.code;+----+------+------+-------+| id | name | code | price |+----+------+------+-------+| 1 | aa | 1 | 100 || 3 | cc | 3 | 900 |+----+------+------+-------+mysql> select * from t1 left join t2 on t1.id = t2.code+----+------+------+-------+| id | name | code | price |+----+------+------+-------+| 1 | aa | 1 | 100 || 2 | bb | NULL | NULL || 3 | cc | 3 | 900 |+----+------+------+-------+mysql> select * from t1 right join t2 on t1.id = t2.code;+------+------+------+-------+| id | name | code | price |+------+------+------+-------+| 1 | aa | 1 | 100 || 3 | cc | 3 | 900 || NULL | NULL | 5 | 2500 |+------+------+------+-------+全連有錯：FULL JOIN 錯誤一：第一個表名不能出現在on的關聯關系中，例如：mysql> select * from t1 full join t2 on t1.id = t2.code;ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause'語句更改為如下后運行不再報錯：mysql> select * from t1 full join t2 on id = t2.code;或者：mysql> select * from t1 full join t2 on id = code;FULL JOIN 錯誤二：上面語句運行后，結果如下：+----+------+------+-------+| id | name | code | price |+----+------+------+-------+| 1 | aa | 1 | 100 || 3 | cc | 3 | 900 |+----+------+------+-------+顯然，這不是正確的full join結果集，而是inner join的結果集。替代方案如下：mysql> select * from t1 left join t2 on id = code union select * from t1 right join t2 on id = code;+------+------+------+-------+| id | name | code | price |+------+------+------+-------+| 1 | aa | 1 | 100 || 2 | bb | NULL | NULL || 3 | cc | 3 | 900 || NULL | NULL | 5 | 2500 |+------+------+------+-------+INTERSECT錯誤：mysql> select * from t1 left join t2 on id = code intersect select * from t1 right join t2 on id = code;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intersect select * from t1 right join t2 on id = code' at line 1說明MySQL不支持INTERSECT。替代方案如下：mysql> select * from t1 inner join t2 on id = code;+----+------+------+-------+| id | name | code | price |+----+------+------+-------+| 1 | aa | 1 | 100 || 3 | cc | 3 | 900 |+----+------+------+-------+MINUS錯誤：mysql> select * from t1 left join t2 on id = code minus select * from t1 right join t2 on id = code;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'minus select * from t1 right join t2 on id = code' at line 1說明MySQL不支持MINUS。而在MS SQL Server中運行(select * from t1 left join t2 on id = code except select * from t1 right join t2 on id = code;)可以得到期望結果如下：+------+------+------+-------+| id | name | code | price |+------+------+------+-------+| 2 | bb | NULL | NULL |+------+------+------+-------+運行如下語句：mysql> select * from t1 left join t2 on id = code where (id,name,code,price) not in (select * from t1 right join t2 onid = code);Empty set (0.00 sec)或者：mysql> select id,name,code,price from t1 left join t2 on id = code where (id,name,code,price) not in (select id,name,code,price from t1 left join t2 on id = code);Empty set (0.00 sec)都沒有得到期望中的結果。(是否因為有NULL值字段，無法用IN 和 NOT IN 來匹配啦？菜鳥在此拋磚引玉。)換用下面語句檢驗用 NOT IN 替代 MINUS，能得出期望結果：mysql> select * from t1,t2 where (id,name,code,price) not in (select * from t1,t2 where id = code);+----+------+------+-------+| id | name | code | price |+----+------+------+-------+| 2 | bb | 1 | 100 || 3 | cc | 1 | 100 || 1 | aa | 3 | 900 || 2 | bb | 3 | 900 || 1 | aa | 5 | 2500 || 2 | bb | 5 | 2500 || 3 | cc | 5 | 2500 |+----+------+------+-------+可以看到這是廣義笛卡爾積減去INNER JOIN的結果集。

posted on 2014-03-03 19:30 何云隆 閱讀(157) 評論(0) ?編輯 ?收藏 所屬分類: MySQL

總結

以上是生活随笔為你收集整理的mysql 全连接问题_mysql 解决全连接问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。