Sahar Atias Sahar Atias - 1 year ago 72
MySQL Question

How to make sql return values even if it doesn't exist in a subquery?

So I got a table called pet_size which represents... pet sizes!
The sizes can be:

id name
1 small
2 medium
3 large

Also, there's 0 which represents unknown or not provided, but it is not added to the table.

Then I got this query:

SELECT, pet_breed.breed_name, pet_type.type_name, pet_size.size_name
FROM pet_breed,
WHERE pet_breed.petTypeId =
AND pet_breed.sizeId =

The above works perfectly for any record except these who got 0 in the pet_breed.sizeId, since it doesn't exist in, what's the most elegant way to fix it, besides of adding a dummy value to pet_size table?

I want that if the sizeId is 0, just use "unknown" as the size_name.

And finally if the above query can be optimized in any way?

Answer Source

You're using the old style joins, update to use correct LEFT JOIN and you'll get the result you're expecting

,ISNULL(ps.size_name,'Unknown') size_name
FROM pet_breed pb
LEFT JOIN pet_type pt
    ON pb.petTypeId = 
LEFT JOIN pet_size ps
    ON pb.sizeId =

I've updated this with table aliases which will make your code more readable too.

Your old joins were acting as INNER JOINS which will only return rows with matching values in all tables.

Edit: Re-read the question and put an ISNULL in to catch where data doesn't match.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download