[thelist] Tricky SQL

rudy r937 at interlog.com
Tue Jun 18 20:17:01 CDT 2002


> Is a union basically just like x + y + z as opposed to a x b x c?

chris, that's a very good way of putting it

a union is like running separate queries and concatenating all the result
sets together

it requires the same number of columns in each (sub)query, and that the
columns all have the same datatypes in their respective positions

with UNION ALL, all rows are retained, but with UNION any duplicated rows
that are completely identical in all columns are reduced to one
occurrence -- hence it is often better to use UNION ALL to avoid the sort
that the database has to run in order to detect duplicate rows (it sorts on
*all* columns)

thus

  SELECT 'mens', score FROM soccer_mens
     WHERE gameDate = yesterday
UNION ALL
  SELECT 'womens', score FROM soccer_womens
     WHERE gameDate = yesterday

here UNION ALL avoids the sort, since there cannot be duplicates across the
subqueries because of the first column in the result sets

by the way, only one ORDER BY is permitted, and it applies to the entire
merged result set *after* the backbreaking sort to remove duplicates if
you've specified UNION

so a union gives x + y + z or perhaps less if duplicates are removed

with a JOIN, on the other hand, you get each row of each table joined with
every other row from all other tables -- like a x b x c -- except that
usually[*] there's a WHERE clause to eliminate nonsensical matches (e.g.
there's no point matching john's address with mary's name and fred's job
title)

for mysql users who cannot use UNION, just run separate queries and handle
the result sets one after the other -- kinda like having "source table" as
the major sort key of the ORDER BY

[*] there's usually a WHERE clause in a JOIN unless (a) you forget it (and
who hasn't done that -- be thankful if it resulted only in a large and
useless result set, and not the sysadmin tracking you down for killing the
company server), or (b) it's a CROSS JOIN on purpose, e.g. to generate test
data by getting all combinations a x b x c to feed into a test table


rudy




More information about the thelist mailing list