如何简化复杂的mysql完全外连接

Question

我正在尝试在Mysql中创建一个完整的外部连接。我找到了这个基本问题的几个答案，我正在使用“联合”来使其工作。但是，如果不创建几个临时表，我就无法获得正确的语法。我曾尝试生成不带表的查询，但始终无法获得包含partner_id为空的条目的结果。

下面是一组精简的数据，已经按meeting_id进行了过滤：

+-----+---------+--------+------------+------------+
| pid | first   | gender | meeting_id | partner_id |
+-----+---------+--------+------------+------------+
|   2 | Vicki   | F      |         74 |       NULL |
|  54 | Fazal   | M      |         74 |          4 |
|   4 | Lisa    | F      |         74 |         54 |
|  10 | Rod     | M      |         74 |         57 |
|  57 | Kellee  | F      |         74 |         10 |
|  11 | Jake    | M      |         74 |         55 |
|  55 | Rosa    | F      |         74 |         11 |
|  47 | Ralph   | M      |         74 |         46 |
|  46 | Holly   | F      |         74 |         47 |
|  40 | Wes     | M      |         74 |         12 |
|  12 | Lori    | F      |         74 |         40 |
|   5 | Richard | M      |         74 |          6 |
|   6 | Rita    | F      |         74 |          5 |
|  15 | John    | M      |         74 |         16 |
|  16 | Corie   | F      |         74 |         15 |
+-----+---------+--------+------------+------------+

我最初的查询是这样的：

set @mtg=74;

select
    a.pid,
    concat(a.first, ' ', a.last) as guy,
    a.issub as guysub,
    b.pid,
    concat(b.first, ' ', b.last) as gal,
    b.issub as galsub,
    b.partner_id
from 
    scheduled_players a
    left outer join
    scheduled_players b
    on a.partner_id = b.pid
where
    a.gender = 'M' and a.meeting_id = @mtg and b.meeting_id = @mtg

union

select
    a.pid,
    concat(a.first, ' ', a.last) as guy,
    a.issub as guysub,
    b.pid,
    concat(b.first, ' ', b.last) as gal,
    b.issub as galsub,
    b.partner_id
from 
    scheduled_players a
    left outer join
    scheduled_players b
    on b.partner_id = a.pid
where
    a.gender = 'M' and a.meeting_id = @mtg and b.meeting_id = @mtg

;

该查询没有返回具有空partner_id的单个条目。我在StackOverflow上读到了许多答案，似乎where子句可以使外连接恢复为内连接。在我的例子中，我不知道这是如何发生的，但为了测试这一点，我决定创建临时表来包含“where”子句元素。我需要为每个'guys‘和'gals’创建2个临时表，因为我在查询中有2次这两个表。结果如下：

set @mtg=74;

create temporary table if not exists 
meeting_guys as select * from scheduled_players
where meeting_id = @mtg and gender='M';

create temporary table if not exists 
meeting_gals as select * from scheduled_players
where meeting_id = @mtg and gender='F';

create temporary table if not exists 
meeting_guys2 as select * from scheduled_players
where meeting_id = @mtg and gender='M';

create temporary table if not exists 
meeting_gals2 as select * from scheduled_players
where meeting_id = @mtg and gender='F';


select
    a.pid,
    concat(a.first, ' ', a.last) as guy,
    a.issub as guysub,
    b.pid,
    concat(b.first, ' ', b.last) as gal,
    b.issub as galsub,
    b.partner_id
from 
    meeting_guys a
    left outer join
    meeting_gals b
    on a.partner_id = b.pid

union

select
    a.pid,
    concat(a.first, ' ', a.last) as guy,
    a.issub as guysub,
    b.pid,
    concat(b.first, ' ', b.last) as gal,
    b.issub as galsub,
    b.partner_id
from 
    meeting_guys2 a
    right outer join
    meeting_gals2 b
    on b.partner_id = a.pid
;

事实证明这是可行的，我收到了我所期望的结果(我去掉了姓氏，因为他们是真人)：

+------+---------+--------+------+--------+--------+------------+
| pid  | guy     | guysub | pid  | gal    | galsub | partner_id |
+------+---------+--------+------+--------+--------+------------+
|   54 | Fazal   |      0 |    4 | Lisa   |      0 |         54 |
|   10 | Rod     |      0 |   57 | Kellee |      0 |         10 |
|   11 | Jake    |      0 |   55 | Rosa   |      0 |         11 |
|   47 | Ralph   |      0 |   46 | Holly  |      0 |         47 |
|   40 | Wes     |      0 |   12 | Lori   |      0 |         40 |
|    5 | Richard |      0 |    6 | Rita   |      0 |          5 |
|   15 | John    |      0 |   16 | Corie  |      0 |         15 |
| NULL | NULL    |   NULL |    2 | Vicki  |      0 |       NULL |
+------+---------+--------+------+--------+--------+------------+

我能够得到我想要的结果，但我不明白为什么前面的查询不起作用。幸运的是，我有一个可行的解决方案，但我真的很想知道是否有更好、更优化的方法。

Answer 1

首先要指出的是，这是未经测试的，所以您可能只需要调整它，但您听起来更有能力修复奇怪的错误。如果你确实需要我澄清我为什么要做某事，或者你需要我修复什么，只要说一句话。

为了解释为什么您的第一次尝试意外地消除了null记录，您是对的，是where子句在做这件事。对于左连接，您将使用`a.meeting_id = @mtg和(b.meeting_id = @mtg or b.meeting_id is a.meeting_id = @mtg and b.meeting_id = @mtg )‘显然，对于右连接，您将在左表中检查null。

至于另一种解决方案，我使用临时表将结果集限制为匹配的meeting_id的早期(为了性能)，以防您的表很大，然后在派生表中过滤M/F。

希望能对你有所帮助。

set @mtg=74;

create temporary table if not exists 
meeting as 
select
    pid,
    concat(first, ' ', last) as full_name,
    issub,
    partner_id,
    meeting_id,
    gender
from scheduled_players
where meeting_id = @mtg;

select
    M.pid,
    M.full_name as guy,
    M.issub as guysub,
    F.pid,
    F.full_name as gal,
    F.issub as galsub,
    F.partner_id
from 
    (select * from meeting where gender = 'M') M
    left outer join (select * from meeting where gender = 'F') F
        on M.partner_id = F.pid
UNION
select
    M.pid,
    M.full_name as guy,
    M.issub as guysub,
    F.pid,
    F.full_name as gal,
    F.issub as galsub,
    F.partner_id
from 
    (select * from meeting where gender = 'M') M
    right outer join (select * from meeting where gender = 'F') F
        on F.partner_id = M.pid

编辑

如果性能不是问题，那么可能更简单的做法是完全忘记临时表，直接在派生表中引用该表；

select concat(first, ' ', last) as full_name, * from scheduled_players where gender = 'M' and meeting_id = @mtg
select concat(first, ' ', last) as full_name, * from scheduled_players where gender = 'F' and meeting_id = @mtg

您还可以创建单个临时表，然后在单独的查询中插入和更新该表。

在一天结束的时候，只要对你有用就行。