0

I needed to use the following query to check for non-linked rows

SELECT * FROM t1 
LEFT JOIN tlink ON tlink.name = 'the_link' AND tlink.ref_id = t1.id
LEFT JOIN t2 ON t2.id = tlink.ref2_id
WHERE t2.id IS NULL

id is the Primary Key column for all 3 tables. This query works on a local machine and my hosting. I'm running MySQL5.1.53. Today, I started coding on another machine, which I just setup the same version of MySQL and that query failed. It returns all the rows even when there's no reference from t2(t2.id is NULL from the result). So I tested with WHERE t2.id = NULL instead, and it works.

The funny thing is, if I recall correctly, WHERE t2.id = NULL has the reverse effect (returning all rows even when t2.id is NULL) when run from my previous machine (which I can't test on it anymore).

So which is the more appropriate way to do it? Does Strict Mode has any effect on this?

EDIT: Some how the same query works now as expected. And WHERE t2.id = NULL id not returning anything. I might have mistook the earlier result of WHERE t2.id = NULL as 'working'

faulty
  • 8,117
  • 12
  • 44
  • 61
  • and you are using some kind of replicate of database on that second machine? Just wondering if databases are identical. – Alex Stybaev Apr 03 '12 at 14:24
  • 1
    Also, http://stackoverflow.com/questions/2749044/what-is-null-and-is-null, http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null – Lukas Eder Apr 03 '12 at 14:25
  • @AlexStybaev it's replicated. Btw, I have code to process and insert into `tlink` the require rows, which it rely on this query to work properly – faulty Apr 03 '12 at 14:30

0 Answers0