Join Tables MySQL

本文关键字：MySQL Tables Join | 更新日期: 2023-09-27 18:00:03

我有多个表，并进行了2个子选择（UserRecord、CustomerRecord），我想将其合并到1个表中

UserRecord
========================
| RecordID | UserName |
========================
| 1        | Sara     |
| 1        | Tom      |
| 2        | Sara     |
| 2        | Kurt     |
| 3        | Fre      |
========================
Table: CustomerRecord
============================
| RecordID | CustomerName |
============================
| 1        | Jef          |
| 2        | Alex         |
| 2        | Peter        |
============================
Table: This should be the result
=======================================
| RecordID | UserName | CustomerName | 
=======================================
| 1        | Sara     | -            |
| 1        | Tom      | -            |
| 1        | -        | Jef          |
| 2        | Sara     | -            |
| 2        | Kurt     | -            |
| 2        | -        | Alex         |
| 2        | -        | Peter        |
| 3        | Fre      | -            |
=======================================

- = null

我试过左，右，左外，右外。。。加入两张桌子，但我没有得到我想要的。

SELECT *
FROM UserRecord AS ur
INNER JOIN CustomerRecord AS cr ON ur.RecordID = cr.RecordID;

Join Tables MySQL

您想要的不是联接，而是UNION:

SELECT RecordID, UserName, NULL AS CustomerName FROM UserRecord
UNION
SELECT RecordID, NULL AS UserName, CustomerName FROM CustomerRecord

它只是将两个表中的记录相加。

我只想补充一点，订单将不是你在预期结果中显示的订单。如果顺序很重要，那么您应该从此UNION中进行SELECT，并在此外部SELECT上添加显式order BY子句。类似于：

SELECT * FROM (
    SELECT RecordID, UserName, NULL AS CustomerName FROM UserRecord
    UNION
    SELECT RecordID, NULL AS UserName, CustomerName FROM CustomerRecord
) ORDER BY RecordID, UserName, CustomerName

您可以使用一个简单的union

select recordid, username, null as customername from userrecord
union
select recordid, null, customername from customerrecord
相关文章：