HP 3000 Manuals HP 3000 Manuals
Complex Queries In addition to the simple queries shown in the previous section, you can create complex queries, which may contain more than one SELECT statement. At the highest level, a query is a SELECT statement, which consists of a query expression followed by an optional ORDER BY clause. At the next lower level, you can combine different query blocks into a single query expression with the UNION operator. Lower still, inside each query block is an optional search condition, which can contain predicates that incorporate subqueries. A subquery is always a single query block (SELECT) that can contain other subqueries but cannot contain a UNION. A query expression can contain a maximum of 16 query blocks from all sources, including UNION, subqueries, and the outer query block. Figure 3-1 shows the range of possibilities for complex queries.Figure 3-1. Range of Complex Query Types You can create a complex query by using the following: * UNION operator, which allows you to take the union of all rows returned by several query blocks in one SELECT statement. * Subqueries (also known as nested queries), which allow you to embed a query block within the search condition of an outer SELECT statement. * Special predicates, such as ANY, ALL, SOME, EXISTS, and IN, which allow you to compare the value of an expression with the value of special structures and subqueries. The next sections describe each type of complex query with examples. UNION Queries A SELECT statement can consist of several query blocks connected by UNION or UNION ALL statements. Each individual SELECT statement returns a query result which is a set of rows selected from a specified table or tables. The union of these query results is presented as a table that consists of all rows appearing in one or more of the original query results. If only the UNION statement is used, all duplicate rows are removed from the final set of rows. In this case, the maximum size of a tuple in the query result is given by the following formula:where: SelectListItems is the number of items in the select list. SumListLengths is the sum of the lengths of all the columns in the select list. At compile time, SumKeyLengths is computed assuming columns of NULL and VARCHAR contain no data. At run time, the actual data lengths are assumed. If the UNION ALL operator is used, duplicates are not removed. Candidates for duplicate removal are evaluated by comparing entire tuples, not just a single field. Only if two or more rows are entirely alike are the duplicates removed. In the case of the UNION ALL operator, the maximum size of a tuple in the query result is 3996 bytes, as it is for a non-UNION query expression. You cannot use LONG columns in a UNION statement. Suppose you wanted to find out the part number for all parts that require 30 days or more for delivery, or are supplied by the vendor whose number is 9002. The following query delivers this information using the UNION form of the SELECT statement: SELECT PartNumber FROM PurchDB.SupplyPrice WHERE DeliveryDays >= 30 UNION SELECT PartNumber FROM PurchDB.SupplyPrice WHERE VendorNumber = 9002 ORDER BY PartNumber ---------------- PARTNUMBER ---------------- 1123-P-01 1133-P-01 1143-P-01 1153-P-01 1223-MU-01 1233-MU-01 1323-D-01 1333-D-01 1343-D-01 1523-K-01 1623-TD-01 1823-PT-01 Note that no rows are duplicated. When the UNION statement is not qualified by the ALL statement, all duplicate rows are removed from the query result. Notice that the ORDER BY clause must be at the end of the SELECT statement. It cannot be included in the separate query expressions that make up the overall statement. Only the final query result can be ordered. If the UNION ALL statement is used in the previous query, the result can contain duplicate rows. The following example flags duplicate rows with two types of arrows that are described below: ---------------- PARTNUMBER ---------------- 1123-P-01 1123-P-01 <---- 1123-P-01 <---+ 1133-P-01 1133-P-01 <---+ 1143-P-01 1143-P-01 <---- 1153-P-01 1153-P-01 <---+ 1223-MU-01 1233-MU-01 <---- 1323-D-01 1333-D-01 1343-D-01 1523-K-01 1623-TD-01 1823-PT-01 In the above example, rows are duplicated for the following: * More than one vendor supplies some parts (these duplicates are indicated by <----) * Vendor 9002 supplies some parts that take 30 or more days to deliver (these duplicates are indicated by <---+) Note that you could get the same information in other ways. For example, you could use two separate queries. Alternatively, you could use two predicates in the search condition joined by the OR operator as follows: SELECT PartNumber FROM PurchDB.Supplyprice WHERE DeliveryDays >= 30 OR VendorNumber = 9002 ORDER BY PartNumber This query still contains duplicate rows where more than one vendor supplies a given part; but no duplicates are caused by vendor 9002 supplying some parts, and that some of these take 30 or more days to deliver. The duplicates could be eliminated by using the SELECT DISTINCT instead of SELECT statement. Using Character Constants with UNION If you want to see which SELECT statement in the UNION statement contributed each row to the query result, you can include character constants in your SELECT statements. A second column is then generated that shows the originating query block for each row, as in this example: SELECT PartNumber, 'deliverydays >= 30' FROM PurchDB.SupplyPrice WHERE DeliveryDays >= 30 UNION ALL SELECT PartNumber, 'supplied by 9002 ' FROM PurchDB.SupplyPrice WHERE VendorNumber = 9002 ORDER BY PartNumber ----------------+------------------ PARTNUMBER |(CONST) ----------------+------------------ 1123-P-01 |deliverydays >= 30 1123-P-01 |deliverydays >= 30 <---- 1123-P-01 |supplied by 9002 1133-P-01 |supplied by 9002 1133-P-01 |deliverydays >= 30 1143-P-01 |deliverydays >= 30 1143-P-01 |deliverydays >= 30 <---- 1153-P-01 |deliverydays >= 30 1153-P-01 |supplied by 9002 1223-MU-01 |deliverydays >= 30 1233-MU-01 |deliverydays >= 30 1323-D-01 |deliverydays >= 30 1333-D-01 |deliverydays >= 30 1343-D-01 |deliverydays >= 30 1523-K-01 |deliverydays >= 30 1623-TD-01 |deliverydays >= 30 1823-PT-01 |supplied by 9002 1923-PA-01 |supplied by 9002 The indicated duplicate rows would have been removed if the example contained the UNION statement instead of UNION ALL. Subqueries A subquery, also known as a nested query, is a query block that is completely embedded in a predicate. A subquery may appear within the search condition which is a part of the WHERE or HAVING clause of a SELECT, INSERT, UPDATE or DELETE statement. It is like any other query expression, except that it cannot contain a UNION operator. A subquery may be used only in the following types of predicates: * Comparison predicate * EXISTS predicate * IN predicate * Quantified predicate Subqueries can be used to arrive at a single value that lets you determine the selection criteria for the outer query block. In the following simple example, the subquery (in parentheses) is evaluated to determine a single value used in selecting the rows for the outer query: SELECT * FROM PurchDB.SupplyPrice WHERE PartNumber = (SELECT PartNumber FROM PurchDB.Parts WHERE PartName = 'Cache Memory Unit') Subqueries are most frequently found within special predicates, which are described fully in the next section. Additional examples of subqueries can be found there. Special Predicates The three types of special predicate are listed here: * The quantified predicate (ALL, ANY, or SOME), used to compare the value of an expression with some or all of the values of an operand. * The IN predicate, used to check for inclusion of an expression in a set of values. * The EXISTS predicate, used to check for the existence of a value in an operand. With all these types, subqueries may be used; for ALL, ANY, SOME, and IN predicate, additional forms allow the use of a value list in place of a subquery. For each type of special predicate the examples in the next sections show both subquery and non-subquery forms of the predicate whenever both possibilities exist. Quantified Predicate A quantified predicate compares a value with a number of other values that are either contained in a value list or derived from a subquery. The quantified predicate has the following general form: Expression ComparisonOperator Quantifier {ValueList} {SubQuery } The comparison operators shown here are allowable: = <> < > <= >= The quantifier is one of these three keywords: ALL ANY SOME The value list is of this form: (Val1, Val2, ..., Valn) Using the ANY or SOME Quantifier with a Value List. With the ANY or SOME quantifier (ANY and SOME are synonymous), the predicate is true if any of the values in the value list or subquery relate to the expression as indicated by the comparison operator. Suppose you have a list of the part numbers for parts you have been buying from vendor 9011. You would like to start obtaining those parts from other vendors. The following example shows how you would find the part number and vendor number for all parts supplied by vendor 9011 that are also supplied by some other vendor: SELECT PartNumber, VendorNumber FROM PurchDB.SupplyPrice WHERE PartNumber = ANY ('1343-D-01', '1623-TD-01', '1723-AD-01', '1733-AD-01') AND NOT VendorNumber = 9011 ----------------+------------ PARTNUMBER |VENDORNUMBER ----------------+------------ 1343-D-01 | 9001 1623-TD-01 | 9015 1723-AD-01 | 9004 1723-AD-01 | 9012 1723-AD-01 | 9015 1733-AD-01 | 9004 1733-AD-01 | 9012 The quantifier ANY is used to determine whether PurchDB.SupplyPrice contains any of the part numbers in the value list. If so, the query returns the part number and vendor number of vendors supplying that part. The final predicate eliminates all instances where the part is supplied by vendor 9011. Note that SOME could be used in place of ANY, because SOME and ANY are synonyms. Using ANY or SOME with a Subquery. You can also use the subquery form of the quantified predicate. If you wanted to distribute some of the business you have been giving vendor 9004, you might want to find vendor numbers for each vendor supplying at least one part supplied by vendor 9004. The following query returns this information: SELECT DISTINCT VendorNumber FROM PurchDB.SupplyPrice WHERE PartNumber = ANY (SELECT PartNumber FROM PurchDB.SupplyPrice WHERE VendorNumber = 9004) ------------ VENDORNUMBER ------------ 9004 9007 9008 9009 9011 9012 9015 The subquery obtains the part numbers for all parts supplied by vendor 9004. The quantifier ANY is then used to determine if PartNumber is the same as any of these parts. If so, the vendor number supplying that part is returned in the query result. Some queries may require you to use ANY and SOME constructs in a manner that is not intuitive. Consider the following query: SELECT T1.SalesPrice FROM T1 WHERE T1.PartNumber <> ANY (SELECT T2.PartNumber FROM T2) The inexperienced SQL user might think that this means, "Select the sales price of parts from table T1 whose numbers are not equal to any part numbers in table T2." However, the actual meaning is, "Select the sales price of parts from T1 such that the part number from T1 is not equal to at least one part number in T2." This query returns the sales price of all the parts in T1 if T2 has more than one part. A less ambiguous form using EXISTS is as follows: SELECT T1.SalesPrice FROM T1 WHERE EXISTS (SELECT T2.PartNumber FROM T2 WHERE T2.PartNumber <> T1.PartNumber) Using the ALL Quantifier. With the ALL quantifier, the predicate is true only if all of the values in the value list or subquery relate to the expression as indicated by the comparison operator. Assume you have been buying parts from vendor 9010. To get a discount from this vendor, you have been required to purchase parts in larger quantities than you would like. To avoid large stockpiles of these parts, you want to find vendors whose discount is not dependent on the purchase of such large quantities. The following query uses two subqueries and an ALL quantifier to retrieve the information you want: SELECT VendorNumber, PartNumber, DiscountQty FROM PurchDB.SupplyPrice WHERE DiscountQty < ALL (SELECT DiscountQty FROM PurchDB.SupplyPrice WHERE VendorNumber = 9010) AND PartNumber IN (SELECT PartNumber FROM PurchDB.SupplyPrice WHERE VendorNumber = 9010) ------------+----------------+----------- VENDORNUMBER|PARTNUMBER |DISCOUNTQTY ------------+----------------+----------- 9006|1423-M-01 | 1 9007|1433-M-01 | 15 The first subquery obtains the number of parts needed to qualify for a discount for each part supplied by vendor 9010. Using the quantifier ALL, rows are selected only when the quantity needed for a discount is less than that needed for any part supplied by 9010. The second subquery limits the selection to only those part numbers supplied by vendor 9010. Thus, the query result shows every part supplied by vendor 9010 which can be obtained from another vendor in smaller quantities with a discount. IN Predicate An IN predicate compares a value with a list of values or a number of values derived by the use of a subquery. The IN predicate has the following general form: Expression [NOT] IN {ValueList} {SubQuery } The ValueList and SubQuery forms of the IN predicate are described separately in the following sections. Note that IN is the same as = ANY. Using the IN Predicate with a Value List. If you wanted to obtain the numbers of all vendors who supplied a given list of parts, the following query could be used: SELECT DISTINCT VendorNumber FROM PurchDB.SupplyPrice WHERE PartNumber IN ('1143-P-01', '1323-D-01', '1333-D-01', '1723-AD-01', '1733-AD-01') ------------ VENDORNUMBER ------------ 9004 9007 9008 9009 : Using the IN Predicate with a Subquery. If you wanted a list of all the vendors who supply the same parts that vendor 9004 supplies, the following query could be used: SELECT DISTINCT VendorNumber FROM PurchDB.SupplyPrice WHERE PartNumber IN (SELECT PartNumber FROM PurchDB.SupplyPrice WHERE VendorNumber = 9004) ------------ VENDORNUMBER ------------ 9004 9007 9008 9009 : The subquery determines the part number of every part supplied by vendor 9004. The outer query selects every vendor who supplies one or more of those parts. DISTINCT removes duplicates from the final query result, as many vendors supply more than one such part. EXISTS Predicate The EXISTS predicate, also known as the existential predicate, tests for the existence of a row satisfying some condition. It has the following general format: EXISTS Subquery EXISTS is true only if the query result of the subquery is not empty; that is, a row or rows are returned as a result of the subquery. If the query result is empty, the EXISTS predicate is false. In the following example, suppose you need to determine the names of all vendors who currently supply parts: SELECT v.VendorName FROM PurchDB.Vendors v WHERE EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) ------------------------------ VENDORNAME ------------------------------ Remington Disk Drives Dove Computers Space Management Systems Coupled Systems Underwood Inc. Pro-Litho Inc. Eve Computers Jujitsu Microelectronics Latin Technology KellyCo Inc. Morgan Electronics Seminational Co. Seaside Microelectronics Educated Boards Inc. Proulx Systems Inc. In this example, v and sp are correlation names, which enable ALLBASE/SQL to distinguish the two VendorNumber columns in the predicate without requiring you to repeat each table name in full. You can also use the NOT EXISTS form of the existential predicate. If you wanted to find those vendors who are not currently supplying you with parts you could use a query of the form shown here: SELECT v.VendorName FROM PurchDB.Vendors v WHERE NOT EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) ------------------------------ VENDORNAME ------------------------------ Covered Cable Co. SemiTech Systems Chocolate Chips Correlated Versus Noncorrelated Subqueries In many cases, it is possible to execute the subquery just once, and obtain a result which is passed to the outer query for its use. Here is an example: SELECT * FROM PurchDB.SupplyPrice WHERE PartNumber = (SELECT PartNumber FROM PurchDB.Parts WHERE PartName = 'Cache Memory Unit') This kind of subquery is a noncorrelated subquery. In other cases, however, it is necessary to evaluate a subquery once for every row in the outer query, as in the following: SELECT v.VendorName FROM PurchDB.Vendors v WHERE NOT EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) The predicate in the subquery references the column value v.VendorNumber, which is defined by the outer query block. When this type of relationship exists between a column value in the subquery and a column value in an outer query block, the query is called a correlated subquery. Recognizing correlated subqueries is important when performance is a priority. Correlated subqueries require the optimizer to use an outer loop join algorithm rather than a sort-merge join. Because a sort-merge join is orders of magnitude faster than an outer loop join, correlated subqueries pay a performance penalty. In addition, when the ANY, SOME, ALL, or IN predicate makes use of subqueries, the queries are converted into correlated subqueries using the EXISTS predicate. Therefore, if at all possible, queries using ANY, SOME, ALL, IN, or the correlated form of the EXISTS predicate should be done as joins of two or more tables rather than by using subqueries if performance is an issue. In fact, it is possible to state a query as a join as well as in a form using subqueries; non-correlated subqueries are faster than sort-merge joins. Sort-merge joins are faster than correlated subqueries which use an outer loop join. Outer Joins An inner join returns only tuples for which matching values are found between the common columns in the joined tables. A natural inner join specifies that each pair of common columns is coalesced into a single column in the query result. The term join has become synonymous with the term natural inner join because that type of join is used so frequently. To include in the query result those tuples from one table for which there is no match in the common columns of the other table you use an outer join. The term natural, when applied to an outer join, has the same meaning as with an inner join. Common columns are coalesced into a single column in the query result. No duplicate columns are returned. Outer Joins Using Explicit JOIN syntax. Outer joins may be constructed using the explicit JOIN syntax of the SELECT statement (see the "SELECT" section of the "SQL Statements" chapter). In a two table outer join, the first table listed in the FROM clause of the SELECT statement is considered the left hand table and the second is considered the right hand table. The set of rows in the result may be viewed as the union of the set of rows returned by an inner join (the inner part of the join) and the set of rows from one table for which no match is found in the corresponding table (the outer part of the join). If the unmatched rows from both tables being joined are preserved, the join is a symmetric outer join. If the rows are preserved from only the left hand table, the join is a left asymmetric outer join. (The word asymmetric is usually omitted.) If the rows are preserved from only the right hand table, the join is a right outer join. The current syntax will allow you to specify either a left outer join or a right outer join, but not a symmetric outer join. A technique for creating a symmetric outer join using the UNION operator is described later in the section, "Symmetric Outer Joins Using the UNION Operator." A left outer join obtains the rows from both tables for which there is a matching value in the common column or columns (the inner part) and the rows from the left hand table for which there is no match in the right hand table (the outer part). Each unmatched row from the left hand table is extended with the columns coming from the right hand table. Each column in that extension has a null value. A right outer join obtains the rows from both tables for which there is a matching value in the common column or columns, and the rows from the right hand table for which there is no match in the left hand table. The unmatched rows from the right hand table are extended with the columns coming from the left hand table, with null column values returned in that extension for every result row which has no match in the left hand table. For example, the following right outer join is between the SupplyPrice and the Vendors tables. For all vendors who supply parts, it returns the Part Number, Vendor Name and Vendor City. For all vendors who do not supply parts, it returns just the Vendor Name and Vendor City. SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT JOIN PurchdB.Vendors v ON sp.VendorNumber = v.VendorNumber ORDER BY PartNumber DESC SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT... ----------------+------------------------------+-------------------- PARTNUMBER |VENDORNAME |VENDORCITY ----------------+------------------------------+-------------------- |Chocolate Chips |Lac du Choc <--Unmatched |SemiTech Systems |San Jose <--rows from |Kinki Cable Co. |Bakersfield <--Vendors table 1943-FD-01 |Eve Computers |Snake River 1933-FD-01 |Remington Disk Drives |Concord 1933-FD-01 |Educated Boards Inc. |Phoenix 1933-FD-01 |Latin Technology |San Jose 1933-FD-01 |Space Management Systems |Santa Clara 1933-FD-01 |Eve Computers |Snake River 1923-PA-01 |Jujitsu Microelectronics |Bethesda : --------------------------------------------------------------------------- Number of rows selected is 16 U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e When you use the ON clause of the JOIN syntax, it must contain, at a minimum, the predicate which specifies the join condition. Other predicates may be placed within the SELECT statement, but their location is critical as the following examples show. Additional predicates may be placed in the ON clause. These predicates limit the rows participating in the inner join associated with the ON clause. All rows excluded by such predicates participate in the outer part of the associated join. The following query returns (in the inner part of the join) Part Numbers for all vendors who supply parts and are located in California (italics). It also returns, without the Part Number (in the outer part of the join) all vendors who do not supply parts ( highlighted), and all vendors who do supply parts, but are not located in California. SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT JOIN PurchdB.Vendors v ON sp.VendorNumber = v.VendorNumber AND VendorState = 'CA' ORDER BY PartNumber DESC SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT... ----------------+------------------------------+-------------------- PARTNUMBER |VENDORNAME |VENDORCITY ----------------+------------------------------+-------------------- |Underwood Inc. |Atlantic City |Remington Disk Drives |Concord |Coupled Systems |Puget Sound | Kinki Cable Co. | Bakersfield |Jujitsu Microelectronics |Bethesda |Dove Computers |Littleton | SemiTech Systems | San Jose |KellyCo Inc. |Crabtree |Educated Boards Inc. |Phoenix | Chocolate Chips | Lac du Choc |Morgan Electronics |Braintree |Eve Computers |Snake River 1933-FD-01 |Latin Technology |San Jose 1933-FD-01 |Space Management Systems |Santa Clara --------------------------------------------------------------------------- First 16 rows have been selected. U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e In the above example, the rows participating in the inner join are further restricted by adding to the ON clause, AND VendorState = 'CA'. All vendors that are not in California are placed in the outer part of the join. If you move the limiting predicate from the ON clause to the WHERE clause, the query returns a different result. In the following query, the inner part of the join still contains all vendors who supply parts and are located in California. However, in the outer part of the join, only those vendors who do not supply parts and are in California are included. SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT JOIN PurchdB.Vendors v ON sp.VendorNumber = v.VendorNumber WHERE VendorState = 'CA' ORDER BY PartNumber DESC SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT... ----------------+------------------------------+-------------------- PARTNUMBER |VENDORNAME |VENDORCITY ----------------+------------------------------+-------------------- |SemiTech Systems |San Jose |Kinki Cable Co. |Bakersfield 1933-FD-01 |Latin Technology |San Jose 1933-FD-01 |Space Management Systems |Santa Clara : --------------------------------------------------------------------------- First 16 rows have been selected. U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e In the above example, the WHERE clause is applied to all the rows returned, regardless of whether they are in the inner or outer part of the join. Thus no rows are returned unless the vendor is located in California. If you want the inner part of the query to contain all vendors who do supply parts and are located in California while the outer part contains all vendors who do not supply parts, regardless of location, use the query shown below. SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT JOIN PurchdB.Vendors v ON sp.VendorNumber = v.VendorNumber WHERE VendorState = 'CA' OR VendorState <> 'CA' AND PartNumber IS NULL ORDER BY PartNumber DESC SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT... ----------------+------------------------------+-------------------- PARTNUMBER |VENDORNAME |VENDORCITY ----------------+------------------------------+-------------------- |SemiTech Systems |San Jose |Chocolate Chips |Lac du Choc |Kinki Cable Co. |Bakersfield 1933-FD-01 |Latin Technology |San Jose 1933-FD-01 |Space Management Systems |Santa Clara 1923-PA-01 |Seminational Co. |City of Industry 1833-PT-01 |Seminational Co. |City of Industry 1833-PT-01 |Seaside Microelectronics |Oceanside 1823-PT-01 |Seaside Microelectronics |Oceanside : --------------------------------------------------------------------------- First 16 rows have been selected. U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e If all common columns between the tables being joined are to be used for the join, the keyword NATURAL may be used so long as the specification of the ON clause join predicate is omitted. This technique may be used when joining more than two tables, as in the query shown below: SELECT PartName, DeliveryDays, VendorName FROM PurchDB.Parts NATURAL RIGHT JOIN PurchDB.SupplyPrice NATURAL RIGHT JOIN PurchDB.Vendors ORDER BY PartName DESC SELECT PartName, DeliveryDays, VendorName FROM PurchDB.Parts NATURAL RIGHT... ------------------------------+------------+------------------------------ PARTNAME |DELIVERYDAYS|VENDORNAME ------------------------------+------------+------------------------------ | |SemiTech Systems | |Kinki Cable Co. | |Chocolate Chips Winchester Drive | 20|Remington Disk Drives Winchester Drive | 30|Morgan Electronics Video Processor | 20|Latin Technology Video Processor | 30|Jujitsu Microelectronics Video Processor | 15|Eve Computers : --------------------------------------------------------------------------- First 16 rows have been selected. U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e Outer Joins Using the UNION Operator. An outer join can also be created by using the UNION operator. Suppose you want to create a list of vendors who either supply some part with a unit price less than $100 or else do not supply any parts at all. To do this, merge two separate queries with a UNION ALL statement, as in the following examples. The first query shown here selects the names of vendors who do not supply parts: SELECT v.VendorName FROM PurchDB.Vendors v WHERE NOT EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) Notice that a second query block is embedded within the first query expression. It creates a temporary table containing the names of all vendors who do supply parts. Then note the special predicate EXISTS, which is negated in this case. The outer SELECT statement allows us to identify the name of each vendor in the Vendors table. Each VendorName is compared against the list of vendors who do supply parts. If the VendorName from the outer SELECT statement is not found in the temporary table created by the subquery, the outer VendorName is returned to the query result, providing us a list of all the Vendors who do not supply parts. The second query shown here defines the vendors who supply at least one part with a unit price under $100: SELECT DISTINCT v.VendorName FROM PurchDB.Vendors v, PurchDB.SupplyPrice sp WHERE v.VendorNumber = sp.VendorNumber AND sp.UnitPrice < 100.00 The next example shows this query joined to the previous one by the UNION ALL statement. It also shows the use of character constants to indicate which rows result from which query block. SELECT DISTINCT v.VendorName, 'supplies parts under $100' FROM PurchDB.Vendors v, PurchDB.SupplyPrice sp WHERE v.VendorNumber = sp.VendorNumber AND sp.UnitPrice < 100.00 UNION ALL SELECT v.VendorName, 'none supplied' FROM PurchDB.Vendors v WHERE NOT EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) ------------------------------+-------------------------+ VENDORNAME |(CONST) | ------------------------------+-------------------------+ Dove Computers |supplies parts under $100| Educated Boards Inc. |supplies parts under $100| Jujitsu Microelectronics |supplies parts under $100| Proulx Systems Inc. |supplies parts under $100| Seaside Microelectronics |supplies parts under $100| Seminational Co. |supplies parts under $100| Underwood Inc. |supplies parts under $100| Covered Cable Co. |none supplied | SemiTech Systems |none supplied | Chocolate Chips |none supplied | Symmetric Outer Join Using the UNION Operator. Since the syntax does not support a symmetric outer join, you might try to simulate a symmetric outer join using the left outer join syntax in combination with the right outer join syntax. Intuitively, the following query might seem correct: SELECT PartName, PartNumber, VendorName, VendorCity FROM Purchdb.Parts NATURAL LEFT JOIN Purchdb.SupplyPrice NATURAL RIGHT JOIN Purchdb.Vendors ORDER BY PartName, VendorName This three table outer join does a left outer join between the Parts and the SupplyPrice tables. The result of that join is then used as the left hand table in a right outer join with the Vendors table. It would seem as though the result first displays all parts supplied by a vendor, then all parts for which there is no supplier, followed by all vendors who do not supply parts. But, the action of the query is subtle. The natural left join preserves the parts from the Parts table that is not supplied by any vendor. This supplies the left hand component for the simulated symmetric outer join. However, although the natural right join preserves the three vendors from the vendors table who do not supply parts (the right hand component for the simulated symmetric outer join), it eliminates the unmatched parts from the Parts table. This happens because the natural right join only preserves unmatched rows from the right hand table, eliminating the row from the Parts table.
NOTE If you test the next query on the sample database, you must first use the following ISQL INSERT statement to add a row with no vendor to the Parts table. INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES ('XXXX-D-LO', 'test part');
To preserve all the unmatched rows from both sides, thus generating a full symmetric outer join, you must use the following syntax: SELECT PartName, PartNumber, VendorName FROM PurchDB.Parts NATURAL LEFT JOIN PurchDB.SupplyPrice NATURAL LEFT JOIN PurchDB.Vendors UNION SELECT PartName, PartNumber, VendorName FROM PurchDB.Parts NATURAL RIGHT JOIN PurchDB.SupplyPrice NATURAL RIGHT JOIN PurchDB.Vendors UNION SELECT PartName, PartNumber, VendorName FROM PurchDB.Parts NATURAL RIGHT JOIN PurchDB.SupplyPrice NATURAL LEFT JOIN PurchDB.Vendors ORDER BY PartName DESC, PartNumber; The result from the natural left join...natural left join preserves the unmatched part from Parts. The natural right join...natural right join preserves the unmatched vendors from Vendors. The natural right join...natural left join would preserve all unmatched rows from SupplyPrice if there were any (in this example there are none). The union operation combines the three results, preserving the unmatched rows from all joins. There are three complete sets of rows that satisfy the inner join, but the union operation eliminates the duplicate rows unless UNION ALL is specified. The result of the above query follows: SELECT PartName, PartNumber, VendorName FROM PurchDB.Parts NATURAL LEFT... ------------------------------+----------------+------------------------------ PARTNAME |PARTNUMBER |VENDORNAME ------------------------------+----------------+------------------------------ | |Kinki Cable Co. | |SemiTech Systems | |Chocolate Chips test part |XXXX-D-LO | Winchester Drive |1343-D-01 |Remington Disk Drives Winchester Drive |1343-D-01 |Morgan Electronics Video Processor |1143-P-01 |Eve Computers Video Processor |1143-P-01 |Coupled Systems :
![[FFN6]](../../pic3k/M017022/FFN63c50.gif)
![[EQN00000]](../../pic3k/M017022/EQN00000525b.gif)