mySQL左连接

Question

我有两张桌子T1L和T2U。对于与left join匹配的记录，如果为Deleted!=0，则我不需要来自这两个表的记录。

SELECT
    IF(U.Column1 IS NULL, L.id, -U.id) AS id,
    IF(U.Column1 IS NULL, L.Column1, U.Column1) AS Column1,
    IF(U.Column1 IS NULL, L.Column2, U.Column2) AS Column2,
    IF(U.Column1 IS NULL, L.Deleted, U.Deleted) AS Deleted
FROM
    T1 L
LEFT JOIN T2 U
    ON L.Column1 = U.Column1 AND L.Column2=U.Column2 AND U.Deleted=0

如果是Deleted!=0，目前它会给我TI的记录。我根本不想要TI或T2的记录。

我如何修改查询来实现这一点呢？

Answer 1

将其移到WHERE子句中，并添加对NULL的额外检查。

SELECT
    IF(U.Column1 IS NULL, L.id, -U.id) AS id,
    IF(U.Column1 IS NULL, L.Column1, U.Column1) AS Column1,
    IF(U.Column1 IS NULL, L.Column2, U.Column2) AS Column2,
    IF(U.Column1 IS NULL, L.Deleted, U.Deleted) AS Deleted
FROM
    T1 L
LEFT JOIN T2 U
    ON L.Column1 = U.Column1 AND L.Column2=U.Column2 
WHERE
    U.Deleted=0
    OR U.Deleted IS NULL

Answer 2

如果我正确地理解了您要做的事情，您应该删除join add A WHERE子句中的"AND U.Deleted=0“：

WHERE U.Deleted=0 OR U.Column1 IS NULL