如果两个表中什么都没有，怎么不获取任何行？

Question

On our site I have the profile.php file generating user-specific profiles from database using following SQL query.

select
    u.username,
    u.email,
    u.access,
    date_format(u.registerdate, '%e. %c. %Y') as `registered`,
    date_format('00-00-0000 00:00', '%e. %c. %Y') as `last_visited`,
    count(p.id) as `posts`
from
    users u,
    posts p
where
    u.username = ? AND p.post_creator = u.id
;

And below I test if user exists with all of his data, using mysql_num_rows() function (please avoid comments about using deprecated PHP's MYSQL extension, I am only improving friend's old code).

The problem comes here, because if I type unexisting user-name to the WHERE clause (replacing the question mark), instead of returning expected empty result-set MySQL database returns me one row, with most fields filled with NULLs, last_visit containing expected 0. 0. 0000 and posts containing 0 (zero).
It is pretty, but I don't want to test some unique fields (like u.username) whether they aren't empty/NULL or not, in my PHP script, because that is very dirty way to check.

I still wanted to obtain empty result-set. I tried JOIN, then WHERE (...) AND u.id is not null but both didn't work (no behavior change). Then I tried HAVING u.id is not null and after adding a column to SELECT (...) expression, it worked.
But I had known that HAVING clause is intended for filtering result-set after select. And I am asking you:

Is there some cleaner way to do this? Mention of filtering rows this way while SELECT. (Or is this good practice? I haven't found pretty answer on my question.)

Answer 1

I believe your problem is arising because of some strange behavior of group by. An aggregation query with no rows always returns one row, even when all rows are filtered out. An aggregation query with a group by can return 0 rows. So, I think you just want to add a group by:

select u.username, u.email, u.access,
       date_format(u.registerdate, '%e. %c. %Y') as `registered`,
       date_format('00-00-0000 00:00', '%e. %c. %Y') as `last_visited`,
       count(p.id) as `posts`
from users u join
     posts p
     on p.post_creator = u.id
where u.username = ?
group by u.username;

Note that I also changed the query to use explicit join syntax with the join condition in the on clause. Explicit joins are more powerful than implicit joins.