mysql - 连接两个表，其中一个表具有特定值

Question

下面的代码返回不在第一个表中的名称。我的问题是在进行连接之前我需要使用第一个表上的位置。

SELECT *
FROM Mock A
    LEFT JOIN List B ON (A.Person = B.Name)
WHERE A.Person IS NULL OR B.Name IS NULL
UNION
SELECT *
FROM KyleMock A
    RIGHT JOIN List B ON (A.Person = B.Name)
WHERE A.Person IS NULL OR B.Name IS NULL

我需要在第一个表上做的是

SELECT * FROM A WHERE Style = 'Turtle'

我试过了

WHERE (A.Person IS NULL OR B.Name IS NULL) AND Style = 'Turtle'

这只是将我的结果限制为 Style = 'Turtle' 的位置，并且不包括其他行。

Answer 1

您可以过滤掉连接条件本身的记录，例如:

SELECT *
FROM Mock A
    LEFT JOIN List B ON (A.Person = B.Name AND A.Style = 'Turtle')
WHERE A.Person IS NULL OR B.Name IS NULL
UNION
SELECT *
FROM KyleMock A
    RIGHT JOIN DraftList B ON (A.Person = B.Name)
WHERE A.Person IS NULL OR B.Name IS NULL