ALLBASE/SQL Reference Manual
> Chapter 3 SQL QueriesComplex Queries |
||||||||||||||||||||||||||
|
UNION QueriesA 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: (SelectListItems +1)*2 + (SumListLengths) <= 4000where:
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-01Note 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-01In the above example, rows are duplicated for the following:
SELECT PartNumber FROM PurchDB.Supplyprice WHERE DeliveryDays >= 30 OR VendorNumber = 9002 ORDER BY PartNumberThis 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 UNIONIf 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 9002The indicated duplicate rows would have been removed if the example contained the UNION statement instead of UNION ALL. SubqueriesA 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:
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 PredicatesThe three types of special predicate are listed here:
Quantified PredicateA 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 SOMEThe value list is of this form: (Val1, Val2, ..., Valn) Using the ANY or SOME Quantifier with a Value ListWith 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 | 9012The 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 SubqueryYou 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 9015The 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 QuantifierWith 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 | 15The 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 PredicateAn 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 ListIf 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 SubqueryIf 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 PredicateThe 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 SubqueryEXISTS 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 SubqueriesIn 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 JoinsAn 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 syntaxOuter 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... ----------------+----------------------------+-------------------------- 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 DiskDrives |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] > eWhen 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 (BOLD), 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... ---------------+------------------------------+-------------------- 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] > eIn 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... ----------------+------------------------------+-------------------- 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] > eIn 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... ----------------+------------------------------+-------------------- 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] > eIf 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 R... ------------------------------+------------+------------------------- 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 OperatorAn 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.00The 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 OperatorSince 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, VendorNameThis 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.
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 L... ------------------------------+---------------+--------------------- PARTNAME |PARTNUMBER |VENDORNAME ------------------------------+---------------+--------------- | |Kinki Cable Co. | |SemiTech Systems | |Chocolate Chips |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 . . .
|