MYSQL 外连接，不返回不匹配的行

Question

我的数据库中有这两个表

describe external_review_sources;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| ersID | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)  | NO   | UNI | NULL    |                |
| logo  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

，

describe listing_external_review_source_rel;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| lersrID | int(11)      | NO   | PRI | NULL    | auto_increment |
| bid     | int(10)      | NO   |     | NULL    |                |
| url     | varchar(255) | NO   |     | NULL    |                |
| ersID   | int(11)      | YES  |     | NULL    |                |
| active  | int(10)      | NO   |     | NULL    |                |
| order   | int(10)      | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

我以这种方式查询这些表：

    SELECT * 
    FROM 
        listing_external_review_source_rel 
        RIGHT JOIN
        external_review_sources USING(ersID)
    where bid=902028 or bid IS NULL;

+-------+---------------+------+---------+--------+-------+--------+-------+
| ersID | name          | logo | lersrID | bid    | url   | active | order |
+-------+---------------+------+---------+--------+-------+--------+-------+
|     1 | G1            | a    |      17 | 902028 | url11 |      1 |     0 |
|     2 | D1            | b    |      18 | 902028 | url22 |      0 |     0 |
+-------+---------------+------+---------+--------+-------+--------+-------+
2 rows in set (0.00 sec)

正如您所看到的，显示的结果为 bid=902028，但是对于诸如 866696 之类的出价，该出价在listing_external_review_source_rel中不存在，

SELECT * 
FROM listing_external_review_source_rel 
     RIGHT JOIN external_review_sources USING(ersID) 
where bid=866696  or bid IS NULL;
Empty set (0.00 sec)

我希望 结果为空结果是这样的：

+-------+---------------+------+---------+--------+-------+--------+-------+
| ersID | name          | logo | lersrID | bid    | url   | active | order |
+-------+---------------+------+---------+--------+-------+--------+-------+
|     1 | G1            | NULL |     NULL| NULL   | NULL  |   NULL |  NULL |
|     2 | D1            | NULL |     NULL| NULL   | NULL  |   NULL |  NULL |
+-------+---------------+------+---------+--------+-------+--------+-------+
2 rows in set (0.00 sec)

这就是我使用的“或出价为空”条件。

我做错了什么以及什么查询会给我这个结果？我基本上也对结果中存在不匹配的行感兴趣。

Answer 1

大多数人使用 LEFT JOIN，因此我将其重写为更标准：

SELECT *
FROM external_review_sources a LEFT JOIN
     listing_external_review_source_rel b ON a.ersID=b.ersID AND bid=866696;

请记住，外部联接返回 ON 条件匹配的所有行，而在不匹配的情况下返回 NULL。在这种情况下，您的匹配条件不仅仅是 ersID

Answer 2

尝试一下

SELECT * FROM 
    external_review_sources e
    LEFT JOIN
    listing_external_review_source_rel r ON e.ersID = r.ersID AND r.bid = 866696
WHERE
    r.bid IS NULL;

在“外部表”上进行过滤时，过滤器需要是派生表或在 JOIN 中，因为您希望在 WHERE 之前（逻辑上）进行过滤。此外，为了清晰起见，最佳实践是使用 LEFT JOIN。

使用派生表

SELECT * FROM 
    external_review_sources e
    LEFT JOIN
    (
     SELECT * 
     FROM listing_external_review_source_rel
     WHERE bid = 866696
    ) r USING (ersID)
WHERE
    r.bid IS NULL;

Answer 3

尝试这种方式，将 bid 列的测试从 WHERE 子句移到 JOIN 中：

SELECT * 
    FROM listing_external_review_source_rel ler
        RIGHT JOIN external_review_sources ers   
            ON ler.ersID = ers.ersID
                AND ler.bid=866696;