MySQL Order by NULL join

Question

Hi, I'm trying to do an ORDER BY where I want any rows without an entry in the join table to appear at the bottom of the list and then organised by name. Simplified tables are:

users (id, name) photos (id, filename, user_id)

So far I have:

SELECT name FROM users 
LEFT OUTER JOIN photos ON photos.user_id = users.id
ORDER BY *ANSWER HERE*, name DESC

Many thanks.

Answer 1

You can use this:

ORDER BY ISNULL(photos.id), name DESC

The ISNULL() function will return 1 or 0, which will conveniently sort in the right order for you.

Answer 2

SELECT name FROM users 
LEFT OUTER JOIN photos ON photos.user_id = users.id
ORDER BY photos.user_id DESC, name DESC

ORDER BY photos.user_id DESC will show NULL values at the end.