Computer Stuff

1. The results of UNION could be ordered (as we can see in the UNION query above) but the ORDER BY clause could be used only with the final resultset — that is, it can refer to the result of the UNION, not to particular queries used in it.

SELECT phone_custid_fn OWNER_ID, phone_phonenum_s
FROM phone
WHERE phone_type_s = 'PHONE' AND phone_salesmanid_fn IS NULL
UNION
SELECT phone_salesmanid_fn, phone_phonenum_s
FROM phone
WHERE phone_type_s = 'PHONE' AND phone_custid_fn IS NULL
ORDER BY 2, 1

You can not use order by in each statement, only at the end of the statement. Note it uses 2, 1 to order.

2. The INTERSECT operator is used to evaluate results returned by two queries but includes only the records produced by the first query that have matching ones in the second. This operator is implemented in Oracle and IBM DB2 UDB but not in Microsoft SQL Server 2000, which uses EXISTS operator for this purpose.

SELECT cust_id_n
FROM customer
INTERSECT
SELECT ordhdr_custid_fn
FROM order_header

It's same as

SELECT cust_id_n
FROM customer
WHERE cust_id_n IN
(SELECT ordhdr_custid_fn
FROM order_header)

MS SQL Server does not have INTERSECT, offering the EXISTS operator instead. The following query could be rewritten in SQL Sever syntax using this operator and a correlated query:

SELECT cust_id_n
FROM customer
WHERE EXISTS
(SELECT ordhdr_custid_fn
FROM order_header
WHERE ordhdr_custid_fn = cust_id_n)

Note: The EXISTS keyword is common for all our "big three" databases, so this query would work for all of them.

While INTERSECT could be replaced with a combination of other SQL methods, it contributes to the clarity of the SQL code and speeds up its execution because it is more optimized than a subquery.

3. When combining the results of two or more queries into a single resultset, you may want to exclude some records from the first query based on what was returned by the second. This keyword is implemented in IBM DB2 UDB only, whereas Oracle uses the MINUS keyword and Microsoft SQL Server 2000 uses EXISTS for the same purpose.

SELECT cust_id_n
FROM customer
MINUS
SELECT ordhdr_custid_fn
FROM order_header;

The DB2 UDB syntax for this query would be identical save for the use of the EXCEPT operator:

SELECT cust_id_n
FROM customer
EXCEPT
SELECT ordhdr_custid_fn
FROM order_header

Microsoft SQL Server 2000 offers the NOT EXISTS operator instead; surprisingly this syntax, while not part of the SQL99 standard, is common to all "big-three" databases. To get the results with the NOT EXISTS operator you need to use a correlated subquery. A correlated subquery differs from a regular subquery in that it accepts a parameter from the outer query as a criterion in the WHERE clause:

SELECT cust_name_s, cust_alias_s, cust_status_s
FROM customer cust
WHERE NOT EXISTS
(SELECT * FROM order_header WHERE ordhdr_custid_fn = cust.cust_id_n)