Mysql Query does not return results on WHERE, but AND
Here is the query I am using to join two tables
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
THIS IS A TABLE 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 | |
+--------------+------------------------------------------------------------------------
------------ + ------ + ----- + --------- + ----------- --- - +
This is TABLE g_metadata p>
+-------+--------------+------+-----+---------+----------------+
| 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 |
+-------+--------------+------+-----+---------+----------------+
But the query returns an empty result. but if i replace WHERE
in the request with AND
. The query returns results. Who cares? There shouldn't be any difference, so I'm confused
source to share
In simple terms, it WHERE
filters the entire result set and JOIN ... ON
only filters the concatenated table.
Since you have LEFT JOIN
, placing the filter in the clause ON
limits the results returned for the table g_metadata
, but does not affect the results returned for rf_popup
. That is, you will get null columns in the concatenated table ( g_metadata
), but the rows for rf_popup
will still be pulled.
On the other hand, including a filter in a clause WHERE
performs the join first and then filters the resulting (combined) result set. That is, lines that do not match the entire sentence WHERE
are simply not returned.
Consider the following simplified examples:
TableA: id ----- 1 2 3 TableB: id | yesNo ----- + ------ 1 | yes 2 | no 3 | yes
-
Simple connection:
SELECT * FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id
returns
A.id | B.id | yesNo ----- + ------ + ------ 1 | 1 | yes 2 | 2 | no 3 | 3 | yes
-
Filtering in a sentence
ON
:SELECT * FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id AND yesNo = 'yes'
returns
A.id | B.id | yesNo ----- + ------ + ------ 1 | 1 | yes 2 | NULL | NULL 3 | 3 | yes
-
Filtering in a sentence
WHERE
:SELECT * FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id WHERE yesNo = 'yes'
returns
A.id | B.id | yesNo ----- + ------ + ------ 1 | 1 | yes 3 | 3 | yes
source to share