Sahar Atias Sahar Atias - 5 months ago 16
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.id, pet_breed.breed_name, pet_type.type_name, pet_size.size_name
FROM pet_breed,
pet_type,
pet_size
WHERE pet_breed.petTypeId = pet_type.id
AND pet_breed.sizeId = pet_size.id
ORDER BY pet_breed.id


The above works perfectly for any record except these who got 0 in the pet_breed.sizeId, since it doesn't exist in pet_size.id, 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

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

SELECT 
pb.id
,pb.breed_name
,pt.type_name
,ISNULL(ps.size_name,'Unknown') size_name
FROM pet_breed pb
LEFT JOIN pet_type pt
    ON pb.petTypeId = pt.id 
LEFT JOIN pet_size ps
    ON pb.sizeId = ps.id
ORDER BY pb.id

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.