我正在努力学习加入 . 但当我在员工和工资表上尝试完全加入时，它失败了 . 它为什么失败？
我已添加解决方案的结果仅供参考 .
mysql> select * from employee;
+-----+---------+
| eid | ename |
+-----+---------+
| 1 | sampath |
| 2 | maclean |
| 3 | sudheer |
+-----+---------+
3 rows in set (0.00 sec)
mysql> select * from Salary;
+------+--------+------+
| sid | salary | eid |
+------+--------+------+
| 1001 | 5000 | 2 |
| 1002 | 70000 | 3 |
| 1003 | 70000 | 3 |
+------+--------+------+
3 rows in set (0.00 sec)
mysql> select employee.eid, employee.ename, Salary.salary from employee JOIN Salary on employee.eid=Salary.eid;
+-----+---------+--------+
| eid | ename | salary |
+-----+---------+--------+
| 2 | maclean | 5000 |
| 3 | sudheer | 70000 |
| 3 | sudheer | 70000 |
+-----+---------+--------+
3 rows in set (0.00 sec)
mysql> select employee.eid, employee.ename, Salary.salary from employee LEFT JOIN Salary ON employee.eid=Salary.eid;
+-----+---------+--------+
| eid | ename | salary |
+-----+---------+--------+
| 1 | sampath | NULL |
| 2 | maclean | 5000 |
| 3 | sudheer | 70000 |
| 3 | sudheer | 70000 |
+-----+---------+--------+
4 rows in set (0.00 sec)
mysql> select employee.eid, employee.ename, Salary.salary from employee RIGHT JOIN Salary ON employee.eid=Salary.eid;
+------+---------+--------+
| eid | ename | salary |
+------+---------+--------+
| 2 | maclean | 5000 |
| 3 | sudheer | 70000 |
| 3 | sudheer | 70000 |
+------+---------+--------+
3 rows in set (0.00 sec)
mysql> select employee.eid，employee.ename，Salary.salary from employee FULL JOIN Salary ON employee.eid = Salary.eid;错误1054（42S22）：'字段列表'mysql>中的未知列'employee.eid'
更新：以下是Left Join Union Right Join的结果
mysql> SELECT * FROM employee e
-> LEFT JOIN Salary s ON e.eid = s.eid
-> UNION
-> SELECT * FROM employee e
-> RIGHT JOIN Salary s ON e.eid = s.eid;
+------+---------+------+--------+------+
| eid | ename | sid | salary | eid |
+------+---------+------+--------+------+
| 1 | Rai | NULL | NULL | NULL |
| 2 | pinto | 1001 | 50000 | 2 |
| 3 | sudheer | 1002 | 70000 | 3 |
| 3 | sudheer | 1003 | 70000 | 3 |
+------+---------+------+--------+------+
4 rows in set (0.00 sec)
2 回答
mysql不支持完全加入 .
检查以下问题希望它能帮助您解决问题:)
MySQL FULL JOIN not working but RIGHT and LEFT join works
MySQL不支持FULL OUTER JOIN关键字 . 仅支持LEFT JOIN和RIGHT JOIN .
你可以尝试这样做来模仿：