Why does this SELECT ... JOIN statement return no results?

Question

I have two tables:
1. tableA is a list of records with many columns. There is a timestamp column called "created"
2. tableB is used to track users in my application that have locked a record in tableA for review. It consists of four columns: id, user_id, record_id, and another timestamp collumn.

I'm trying to select up to 10 records from tableA that have not been locked by for review by anyone in tableB (I'm also filtering in the WHERE clause by a few other columns from tableA like record status). Here's what I've come up with so far:

SELECT tableA.* FROM tableA
  LEFT OUTER JOIN tableB ON tableA.id = tableB.record_id WHERE
  tableB.id = NULL AND
  tableA.status = 'new' AND
  tableA.project != 'someproject' AND
  tableA.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-06 23:59:59'
  ORDER BY tableA.created ASC LIMIT 0, 10;

There are currently a few thousand records in tableA and zero records in tableB. There are definitely records that fall between those timestamps, and I've verified this with a simple

SELECT * FROM tableA WHERE  
created BETWEEN '1999-01-01 00:00:00' AND '2010-05-06 23:59:59'  

The first statement above returns zero rows, and the second one returns over 2,000 rows.

Answer 1

tableB.id = NULL 

should be

tableB.id IS NULL 

It is never true as is (Nor is it false. NULL=NULL evaluates to unknown so tableB.id <> NULL would similarly not return any results).

See http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html and http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server

Answer 2

Do any of those records between the timestamps follow your other two guidelines, such as being new but not 'someproject'?

Also, any time you use NULL, you need to use IS or IS NOT, rather than =/!=.

I don't remember if MySQL's structure is different on the idea of constraints, but if tableB.id is your primary key, than it cannot be NULL.

Finally, if there aren't any records in Table B, how is it supposed to search the non-existent records? It will always return zero since there aren't any matching records in tableB and tableA since tableB is empty.

Try entering a dummy record in TableB that matches your search criteria and see if the output changes.

Answer 3

Already answered, but here is some general troubleshooting advice: don't try to solve so many problems at once. My first approach would have been to eliminate all of the filtering where conditions and try it. If I get data, then add half of them back in. If that works, try the other half. Repeat until the problem is staring you in the face. By troubleshooting this way, you'd have quickly narrowed it down to the "tableB.id = NULL" statement, and probably would have had a D'OH! moment and figured it out.