|
UNION & Outer Joins (briefly
explained)
There are occasions where you might want to see the results of multiple
queries together, combining their output; use UNION. To merge the output of the
following two queries, displaying the ID's of all Buyers, plus all those who
have an Order placed:
SELECT BUYERID
FROM ANTIQUES
UNION
SELECT OWNERID
FROM ORDERS;
Notice that SQL requires that the Select list (of columns) must match,
column-by-column, in data type. In this case BuyerID and OwnerID are of the same
data type (integer). Also notice that SQL does automatic duplicate elimination
when using UNION (as if they were two "sets"); in single queries, you
have to use DISTINCT.
The outer join is used when a join query is "united" with
the rows not included in the join, and are especially useful if constant text
"flags" are included. First, look at the query:
SELECT OWNERID, 'is in both Orders & Antiques'
FROM ORDERS, ANTIQUES
WHERE OWNERID = BUYERID
UNION
SELECT BUYERID, 'is in Antiques only'
FROM ANTIQUES
WHERE BUYERID NOT IN
(SELECT OWNERID
FROM ORDERS);
The first query does a join to list any owners who are in both tables, and
putting a tag line after the ID repeating the quote. The UNION merges this list
with the next list. The second list is generated by first listing those ID's not
in the Orders table, thus generating a list of ID's excluded from the join
query. Then, each row in the Antiques table is scanned, and if the BuyerID is
not in this exclusion list, it is listed with its quoted tag. There might be an
easier way to make this list, but it's difficult to generate the informational
quoted strings of text.
This concept is useful in situations where a primary key is related to a
foreign key, but the foreign key value for some primary keys is NULL. For
example, in one table, the primary key is a salesperson, and in another table is
customers, with their salesperson listed in the same row. However, if a
salesperson has no customers, that person's name won't appear in the customer
table. The outer join is used if the listing of all salespersons is to be
printed, listed with their customers, whether the salesperson has a customer or
not--that is, no customer is printed (a logical NULL value) if the salesperson
has no customers, but is in the salespersons table. Otherwise, the salesperson
will be listed with each customer.
Another important related point about Nulls having to do with joins: the
order of tables listed in the From clause is very important. The rule states
that SQL "adds" the second table to the first; the first table listed
has any rows where there is a null on the join column displayed; if the second
table has a row with a null on the join column, that row from the table listed
second does not get joined, and thus included with the first table's row data.
This is another occasion (should you wish that data included in the result)
where an outer join is commonly used. The concept of nulls is important, and it
may be worth your time to investigate them further.
ENOUGH QUERIES!
|
