SQL question

[Xors-ted]

Please answer the following SQL question
There are 3 tables.
cat dog animal
id, name id, name id, color

what's the sql for listing the name of the animals with color = brown?

[Ron Weller]

SELECT [name id] FROM Cat WHERE color = 'brown'
UNION SELECT [name id] FROM dog WHERE color = 'brown'
UNION SELECT [name id] FROM animal WHERE color = 'brown';

[jcaccia]

Use

SELECT c.name FROM cat c, animal a WHERE c.id = a.id AND a.color = 'brown'
UNION
SELECT d.name FROM dog d, animal a WHERE d.id = a.id AND a.color = 'brown'

or

SELECT x.name FROM (
    SELECT id, name FROM cat
    UNION
    SELECT id, name FROM dog
) x, animal a
WHERE x.id = a.id AND a.color = 'brown'

The efficiency and execution times of these two queries vary depending on the organisation of the tables, indexes, etc.

[Wiseman82]

From a performance point of view, you might want to use UNION ALL rather than UNION.

UNION will return a distinct result set - if you know there will be no duplicates, it's more efficient to use UNION ALL.

SELECT [name] FROM Cat WHERE color = 'brown'
UNION ALL
SELECT [name] FROM dog WHERE color = 'brown'
UNION ALL
SELECT [name] FROM animal WHERE color = 'brown';