无法处理MySQL FULL JOIN查询 [英] Can't process MySQL FULL JOIN query

问题描述

我想FULL JOIN跟随两个表

表1:人员

P_Id      Name            
 1         A        
 2         B       
 3         C
 4         D
 5         E 

表2:发票

  Id       P_Id            
 111        3        
 112        3       
 113        1
 114        1
 115       15 

我使用了这个查询:

SELECT Persons.Name, Persons.P_Id, Invoice.Id
FROM Persons
FULL JOIN Invoice
ON Persons.P_Id=Invoice.P_Id
ORDER BY Persons.Name

但这会产生错误

字段列表"中的未知列"Persons.Name"

MySQL服务器版本为5.5.19，我在Microsoft Windows7中使用了命令行客户端

MySQL server version is 5.5.19 and I used command line client in Microsoft Windows7

内联接，右联接和左联接对我有用，但我无法执行完全联接.有什么错误，请告诉我我仍然是学生.我如何在这里执行FULL JOIN?

INNER JOIN, RIGHT JOIN and LEFT JOIN worked for me but I can't perform FULL JOIN. What is the error please tell me I'm still a student. How can I perform FULL JOIN here ?

谢谢！

推荐答案

为了在mysql中模拟FULL JOIN，您需要UNION两个表中Right Join和Left Join的结果

in order to simulate FULL JOIN in mysql, you need to UNION the result of Right Join and Left Join from both tables

SELECT  Persons.Name, Persons.P_Id, Invoice.Id
FROM    Persons
        LEFT JOIN Invoice
            ON Persons.P_Id=Invoice.P_Id
UNION
SELECT  Persons.Name, Persons.P_Id, Invoice.Id
FROM    Invoice
        LEFT JOIN Persons
            ON Persons.P_Id=Invoice.P_Id

• SQLFiddle演示
• SQLFiddle Demo

这篇关于无法处理MySQL FULL JOIN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！