MySQL (InnoDB) Select from 2 tables and always get empty result set

Question

Hello,

I'm a little confused. I'm using MySQL 5+ with InnoDB as my engine. If I run the following statement and the table 'user_temp' is empty I always get an empty result set no matter what's in the 'users' table.

SELECT * FROM `users`, `user_temp` 

If I put something in 'user_temp' I'll get back all the results. Is this suppose to work like this?

Thanks.

Answer 1

That's an INNER JOIN. But you are specifying a join field. I think you want an OUTER JOIN. Maybe even a FULL OUTER JOIN

Your example can be re-written as:

SELECT * FROM users, user_temp
INNER JOIN user_temp
ON users.id_user = user_temp.id_temp

If no rows match on id_user, which would definitely be the case if one of the tables was empty, then you would get 0 records in your result set.

Try:

SELECT * FROM users, user_temp
LEFT JOIN user_temp
ON users.id_user = user_temp.id_temp

Answer 2

Use a LEFT OUTER JOIN to unconditionally get results from users, e.g.:

SELECT 
    u.*, ut.*
FROM 
    `users` u
    LEFT OUTER JOIN `user_temp` ut
    ON ut.user_id = u.user_id

Here is a good visual explanation of the various join types.