[thelist] Selecting two distincts
rudy
r937 at interlog.com
Mon May 6 18:09:00 CDT 2002
( SELECT 'Genre' AS Type
, DISTINCT(g.GenreName) AS Name
FROM loggenretlk as lg
JOIN genre as g
ON g.GenreID = lg.GenreID
JOIN weblog as w
ON w.LogID = lg.LogID
WHERE w.LogID = @LogID )
UNION
( SELECT 'Band' AS Type
, DISTINCT(b.BandName) AS Name
FROM logbandtlk as lb
JOIN bands as b
ON b.BandID = lb.BandID
JOIN weblog as w
ON w.LogID = lb.LogID
WHERE w.LogID = @LogID )
ORDER BY Type, Name
good one, michiel
i had never seen anybody put the subselects in parentheses before
using parentheses certainly makes it obvious that the ORDER BY clause, an
example of which i've added above, belongs to the entire query and not the
last subquery as one might assume without the parentheses
<tip type="SQL">
when writing a UNION query, take a moment to decide if there will be any
possibility of duplicate rows -- not duplicate keys, duplicate rows --
resulting from the merged result sets of the unioned subselects
if not, use UNION ALL instead of UNION, in order to avoid an unnecessary
sort
the sort is expensive, too, since it has to include all columns in order
to detect duplicate rows!
</tip>
so the above UNION should really be UNION ALL...
... unless you felt like removing the DISTINCT from each subselect -- this
might introduce duplicates within the each result set (but not across
result sets, because of the Type strings in the SELECT list), and these
would be removed by the single sort for UNION, instead of two sorts for the
DISTINCTs
if that don't make sense, don't worry about it -- database servers are fast
enough that you'll probably not notice the difference, not when dealing
with results sets that are of "normal" size (i.e. have no more than a few
hundred records, typical of web pages)
rudy
More information about the thelist
mailing list