Mysql 查询不在 WHERE 上返回结果，而是在 AND 上返回结果

Question

这是我用来连接两个表的查询

SELECT `rf_popup`.* 
  FROM `rf_popup` LEFT JOIN 
       `g_metadata` ON (`rf_popup`.`name` = `g_metadata`.`name`) 
 WHERE (`g_metadata`.`g_id` = '2009112305475443' AND 
        `g_metadata`.`value` < rf_popup.cardinality OR 
        `g_metadata`.`g_id` IS NULL) AND 
       `category` IN ('S', 'all') AND 
       `field` IN ('descr', 'all') AND 
       `filler_type` IN ('F2', 'all') 
 ORDER BY `rf_popup`.`priority` DESC LIMIT 5 

这是表 rf_popup

+--------------+------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field        | Type                                                                               | Null | Key | Default | Extra          |
+--------------+------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id           | int(11)                                                                            | NO   | PRI | NULL    | auto_increment |
| name         | varchar(200)                                                                       | YES  |     | NULL    |                |
| text         | text                                                                               | YES  |     | NULL    |                |
| cardinality  | int(11)                                                                            | NO   |     | 1       |                |
| field        | varchar(200)                                                                       | YES  |     | NULL    |                |
| category     | enum('A','H','N','R','S','D','all') | YES  |     | NULL    |                |
| time_to_show | enum('START','END')                                                                | YES  |     | START   |                |
| filler_type  | enum('F1','F2','F3','R1','R2','R3','all')   | YES  |     | FILLER1 |                |
| priority     | int(11)                                                                            | NO   |     | 0       |                |
+--------------+------------------------------------------------------------------------

------------+------+-----+--------+---------- -----+

这是表 g_metadata

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| g_id  | varchar(50)  | YES  | MUL | NULL    |                |
| name  | varchar(50)  | YES  | MUL | NULL    |                |
| value | varchar(200) | YES  |     | NULL    |                |
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+

但查询返回空结果。但是如果我将查询中的 WHERE 替换为 AND。查询返回结果。有什么区别？？应该没有什么区别，所以我很困惑

Answer 1

简单来说，WHERE 过滤整个结果集，JOIN ... ON 只过滤连接的表。

因为这里有一个 LEFT JOIN，将过滤器放在 ON 子句中会限制为 g_metadata 表返回的结果，但是不影响为 rf_popup 返回的结果。也就是说，您将在联接表 (g_metadata) 中获得空列，但仍会提取 rf_popup 的行。

另一方面，将过滤器放在 WHERE 子句中首先执行连接，然后过滤生成的(连接的)结果集。也就是说，不匹配整个 WHERE 子句的行将不会被返回。

---

考虑以下简化示例:

TableA:id-----123TableB:id   | yesNo-----+------1    | yes2    | no3    | yes

1. A simple join:

   SELECT * 
   FROM TableA 
   LEFT JOIN TableB ON TableA.id = TableB.id
   

   返回

   A.id | B.id | yesNo-----+------+------1    | 1    | yes2    | 2    | no3    | 3    | yes

2. Filtering in the ON clause:

   SELECT * 
   FROM TableA 
   LEFT JOIN TableB ON TableA.id = TableB.id
       AND yesNo = 'yes'
   

   返回

   A.id | B.id | yesNo-----+------+------1    | 1    | yes2    | NULL | NULL3    | 3    | yes

3. Filtering in the WHERE clause:

   SELECT * 
   FROM TableA 
   LEFT JOIN TableB ON TableA.id = TableB.id
   WHERE yesNo = 'yes'
   

   返回

   A.id | B.id | yesNo-----+------+------1    | 1    | yes3    | 3    | yes