Query Conditionally Across Two Tables

[Gman]

Hi NG,

I'd just like some clarification as to why something doesn't always
work. My query is across two tables, where there may be record in
another table - and maybe not.

For the sake of explanation I've created two tables below. (Yes -- in
this case the two tables could be combined but in the real scenario they
can't... and nor will there be an entry in the second table for every
record in the first table - it's not really for an animal database.)

=====TABLE ANIMALS=======
PKID AnimalName AnimalColor
1 Monkey Grey
2 Giraffe Brown
3 Lion Brown
4 Crocodile Green

=====TABLE FURRINESS=======
AnimalID IsFurry
4 No

So let's say I want to get all the animals that are either Brown or are
Furry. The following query works:

SELECT PKID, AnimalName, AnimalColor
FROM animals, furriness
WHERE
animalcolor = "Brown"
OR ( animals.pkid = furriness.animalid
AND furriness.isfurry = True )

but *only* if there is at least one record in the FURRINESS table. It
doesn't matter what the record is. If there is no record in that table I
get no results at all.

I know I can get the results I want by using a UNION e.g.

SELECT PKID, AnimalName, AnimalColor
FROM animals
WHERE animalcolor = "Brown"
UNION (
SELECT PKID, AnimalName, AnimalColor
FROM animals, furriness
WHERE
animals.pkid = furriness.animalid
AND furriness.isfurry = True
)

I'm just a little confused as to why the first query works fine
providing there is one record in the FURRINESS table (even though that
record won't appear in the query's results anyway). Thus, is a UNION the
correct way to go (I guess I could also use a LEFT JOIN too)?

Thanks

 

[Gman]

Just to add.... I've found posts e.g.

http://makeashorterlink.com/?C56316B6C

where this has been discussed. But no full explanation.

Does the query engine just look at the FROM tables first and think "Hah!
There's no records in the second table so there's no point in trying to
run the query"?

When there are records it runs the query and we're good.

In short... I just don't understand WHY it doesn't work!

 

[John Spencer]

When you don't specify a join then you get a cross product (or cartesian
join) of the tables. That means for every combination of the rows in the
two tables you get a row in the results. So if there are ZERO records in
one of the tables, you end up with ZERO records in the result. Zero times
anything is Zero.

If you change the query to use an outer join (Left Join or Right Join) then
you can get records returned. That would require a query something like the
following.

SELECT PKID, AnimalName, AnimalColor
FROM animals LEFT JOIN furriness
On Animals.PKID = furriness.animalid
WHERE animalcolor = "Brown"
or furriness.isfurry = True
OR (animalcolor = "Brown" and isfurry is Null)

 

[Gman]

Hi John,

That makes a lot of sense - indeed I had read something along those
lines but it wasn't as clear as you made it.

Thanks a lot for your explanation.

Gman

....now I just have to teach my monkeys how to type.....