Combining Data from Different Tables

An important part of designing a relational database is deciding how to divide data into different tables. We imagined a CD collection that we originally organized on index cards, one card per CD, which we wanted to put in a database. To do this, we created one table to hold information that applied to the CD as a whole and another table for information about the songs. We called the process of finding relationships and creating tables to model the data with minimal redundancy normalization. We found that this process leads to multiple small tables. An important consequence of this is that, in order to use the data, create reports, or perform analyses or, in our example, to recreate the information that was on each CD's index card we need to combine the information from different tables.

Joins

Just as we can use a SELECT statement to retrieve subsets of data and to put the data in order, we can also use it to combine data from two tables. (We can combine any number of tables, but in practice, a join is usually between two tables.) A SELECT statement used in this way is called a join. Two clauses in the SELECT statement are used to create a join: the FROM clause identifies the tables that are to be combined, and the WHERE clause is used to specify the conditions that must be satisfied in order to match records in one table with those in the other.

SQL also provides an alternative to using the FROM and WHERE clauses to create a join the JOIN clause. Both syntaxes provide the same functionality. There is no compelling reason to choose one syntax over the other. Many database programmers are most comfortable with the FROM…WHERE syntax because it's been around longer on more platforms, but that's largely a matter of personal preference. We'll take a look at examples of both.

Simple Joins

The simplest kind of join is called a Cartesian join. This is rarely useful and usually occurs when we accidentally omit the conditions in the WHERE clause. This causes the database to join every row in the first table unconditionally with every row in the second table. Let's use these two tables for example:

 ANTIQUE_CARS MODEL        MAKE Terraplane   Hudson Firedome     DeSoto Bearcat      Stutz CAR_CLUB OWNER        MODEL Bob          Terraplane Sally        Corvette Lou          Bearcat Jane         Mustang 

We can create a Cartesian join of these two tables with the following SELECT statement:

 SQL> SELECT *   2  FROM ANTIQUE_CARS, CAR_CLUB; MODEL        MAKE         OWNER        MODEL ------------ ------------ ------------ ------------ Terraplane   Hudson       Bob          Terraplane Firedome     DeSoto       Bob          Terraplane Bearcat      Stutz        Bob          Terraplane Terraplane   Hudson       Sally        Corvette Firedome     DeSoto       Sally        Corvette Bearcat      Stutz        Sally        Corvette Terraplane   Hudson       Lou          Bearcat Firedome     DeSoto       Lou          Bearcat Bearcat      Stutz        Lou          Bearcat Terraplane   Hudson       Jane         Mustang Firedome     DeSoto       Jane         Mustang Bearcat      Stutz        Jane         Mustang 12 rows selected. 

Each of the four rows in the first table is matched with each of the three rows in the second table, resulting in twelve rows. If each table had 100 entries, the result would have 10,000 rows. While most of the rows are meaningless, there are a few rows that are interesting: where the entry in the MODEL column from one table matches the MODEL column in the other. In these rows, the OWNER column from one table is connected with the MAKE column in the other table by the MODEL column in each table. If we add a requirement for the MODEL columns to match as a condition of the join, we can, in effect, filter the Cartesian join to get a useful result:

 SQL> SELECT *   2  FROM ANTIQUE_CARS, CAR_CLUB   3  WHERE ANTIQUE_CARS.MODEL=CAR_CLUB.MODEL; MODEL        MAKE         OWNER        MODEL ------------ ------------ ------------ ------------ Bearcat      Stutz        Lou          Bearcat Terraplane   Hudson       Bob          Terraplane 

This tells which club members own antique cars, along with the make and model of their cars. We can eliminate the duplicate MODEL column by specifying a select list. But first notice that, because both tables have a MODEL column, we needed to distinguish between the two in our condition, so we had to specify the name of the table we meant for each one. A convenient feature in SQL is that we can create an alias for each of our tables in the FROM clause that we can use elsewhere in the SELECT statement. We do this by simply adding the table alias after the table name. Although the aliases can be any valid name, most commonly this is used to create an abbreviation.

 SQL> SELECT C.OWNER, C.MODEL, A.MAKE   2  FROM ANTIQUE_CARS A, CAR_CLUB C   3  WHERE A.MODEL=C.MODEL; OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz Bob          Terraplane   Hudson 

Notice that we specified the table for each column in the select list. This is necessary for the MODEL column, because it appears in both tables; otherwise, Oracle will complain about a column being ambiguously defined. It's a good idea to be explicit about the table for all columns, anyway. This not only makes things clear, but also makes the SQL statement more robust. It won't break if we later add a MAKE column to the CAR_CLUB table, for example.

This type of join is called an equijoin because the join condition specifies that the join columns in the two tables must be equal. In this example, we link only on a single column, as illustrated in Figure 4-1, but it is also possible to include multiple columns in the join condition. If each of these tables included a column for the model year, we might want to make that part of the join condition, too.

Figure 4-1. Join on a single column.

graphics/04fig01.gif

In addition to the join condition, we can add other criteria to the WHERE clause to select a subset of the rows that meet the join criteria. For example, we can find out who in the car club owns a Stutz, as follows:

 SQL> SELECT C.OWNER, C.MODEL, A.MAKE   2  FROM ANTIQUE_CARS A, CAR_CLUB C   3  WHERE A.MODEL=C.MODEL   4  AND A.MAKE='Stutz'; OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz 

The syntax for this kind of join statement, using FROM and WHERE, has the following general format:

 SELECT select_list FROM table_name_1 [alias_1], table_name_2 [alias_2] WHERE join_conditions [AND select_conditions]; 

Note that there is no formal distinction between the join conditions and any other conditions, and no requirement that they be joined by AND, but it's a useful distinction, especially when we compare it with the JOIN syntax that makes this distinction between the join conditions and select conditions formal.

To perform a join using the JOIN command, we use this syntax:

   SELECT select_list   FROM table_name_1 [alias_1] JOIN table_name_2 [alias_2]   ON join_conditions [WHERE select_conditions]; 

The previous example using the JOIN syntax is:

 SQL> SELECT C.OWNER, C.MODEL, A.MAKE   2  FROM ANTIQUE_CARS A JOIN CAR_CLUB C   3  ON A.MODEL=C.MODEL   4  WHERE A.MAKE='Stutz'; OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz 

The JOIN syntax has one variation that is particularly handy in a case like this where we are joining the tables based on columns that have the same name in both tables the NATURAL JOIN. It automatically finds rows that have equal values in the common column.

The syntax is:

 SELECT select_list FROM table_name_1 [alias_1] NATURAL JOIN table_name_2 [alias_2] [WHERE select_conditions]; 

Again, the previous example using the NATURAL JOIN syntax:

 SQL> SELECT OWNER, MODEL, MAKE   2  FROM ANTIQUE_CARS NATURAL JOIN CAR_CLUB   3  WHERE MAKE='Stutz'; OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz 

Notice that we don't need to use table names with columns because there can be no ambiguity. Columns with the same name in each table in this case, MODEL necessarily have the same value because they are used for the join condition. In fact, if we try to qualify the join column with a table name, we'll get an error.

The NATURAL JOIN will also work on tables that have more than one column with the same name. Joins of all kinds are frequently done using key columns as the link. Consider the first version of the CD_COLLECTION table in our CD collection database it had a natural primary key consisting of two columns, ARTIST and TITLE. We can link the songs in the first version of the SONGS table to this table based on these two columns, using a NATURAL JOIN, with the following statement.

 SELECT ALBUM_TITLE, ARTIST, SONG_TITLE FROM CD_COLLECTION NATURAL JOIN SONGS 

This is functionally identical to the following join, using the FROM… WHERE syntax:

 SELECT C.ALBUM_TITLE, C.ARTIST, S.SONG_TITLE FROM CD_COLLECTION C, SONGS S WHERE CD_COLLECTION.ALBUM_TITLE=SONGS.ALBUM_TITLE   AND CD_COLLECTION.ARTIST=SONGS.ALBUM_ARTIST; 

It's most common to perform a join on two tables, but we can have any number of tables. (We'll see the special case of joining a table with itself, the self-join, below.) To prevent a Cartesian product, for any number of tables N, we need to provide at least N-1 join conditions. Suppose that we've noticed a number of old cars around town and create a table listing where we usually see them:

 CAR_SIGHTINGS MAKE        COMPANY Hudson      Acme Lumber Studebaker  Central Supermarket Edsel       Cup O' Java Stutz       Ace Insurance 

We might assume that if the car is usually parked in front of a place of business, the owner works there. This assumption may or may not be valid, but given this table, we can associate the owners of antique cars with places of business, as follows:

 SQL> SELECT C.OWNER, S.COMPANY   2  FROM CAR_CLUB C, ANTIQUE_CARS A, CAR_SIGHTINGS S   3  WHERE C.MODEL=A.MODEL AND A.MAKE=S.MAKE; OWNER        COMPANY ------------ -------------------- Lou          Ace Insurance Bob          Acme Lumber 

This is the most common use for multiple joins, navigating from data in one table that is linked via one or more intermediate tables to data in another table. In this case, to get from the COMPANY in the CAR_SIGHTINGS table to the OWNER in the CAR_CLUB table, we linked CAR_SIGHTING to the ANTIQUE_CARS table with the MAKE column, then we linked the ANTIQUE_CARS table to the CAR_CLUB table with the MODEL column.

Outer Joins

The simple joins that we've seen so far are called inner joins. They include the rows from both tables that meet the join condition and only those rows. Sometimes we want to include all the rows from one of the tables and use the other table to provide additional information, if it's available, for some rows. This is called an outer join.

There are three kinds of outer joins. When you are joining two tables in an SQL statement, the first one appears on the left and the second one on the right, if you list them on the same line in the join clause. Because of this obvious fact, we refer to the first table as the left table and the second table as the right table. If we specify that we want to include all rows for the first table, this is called a left outer join. If we specify that we want to include all rows for the second table, this is called a right outer join. We can also include all rows for both tables; this is called a full outer join.

For example, suppose we want to see what each member of the car club drives. The CAR_CLUB table lists the owner and the model of each car but not the make. We have the make information for antique cars in the ANTIQUE_CARS table, but not everyone drives an antique car. The following is a left outer join that will list all owners and models from the first table, unconditionally. If, for a given column, the make is available from the second table because the car is an antique, it will list that too; otherwise, it will leave the column for that row blank.

 SQL> SELECT C.OWNER, C.MODEL, A.MAKE   2  FROM CAR_CLUB C LEFT OUTER JOIN ANTIQUE_CARS A   3  ON C.MODEL=A.MODEL; OWNER        MODEL        MAKE ------------ ------------ ------------ Bob          Terraplane   Hudson Lou          Bearcat      Stutz Jane         Mustang Sally        Corvette 

This uses the newer ANSI JOIN syntax. The general format for a left outer join is:

 SELECT select_list FROM table_name_1 [alias_1] LEFT OUTER JOIN table_name_2 [alias_2] ON join_conditions [WHERE select_conditions]; 

The general format for a right outer join is:

 SELECT select_list FROM table_name_1 [alias_1] RIGHT OUTER JOIN table_name_2 [alias_2] ON join_conditions [WHERE select_conditions]; 

We can list all the cars in the ANTIQUE_CAR list plus any club members who own one, with a right outer join as follows:

 SQL> SELECT C.OWNER, A.MODEL, A.MAKE   2  FROM CAR_CLUB C RIGHT OUTER JOIN ANTIQUE_CARS A   3  ON C.MODEL=A.MODEL; OWNER        MODEL        MAKE ------------ ------------ ------------ Bob          Terraplane   Hudson Lou          Bearcat      Stutz              Firedome     DeSoto 

Notice that we changed the select list as well. We include MODEL from the ANTIQUE_CARS table not the CAR_CLUB, like we did before because since no one in the car club owns a DeSoto, the MODEL column would otherwise be null.

A full outer join lists all rows of the joined tables, filling in the appropriate columns with null where a row in one table does not have a matching row in the other. Here is a full outer join of the CAR_CLUB and ANTIQUE_CARS table, listing all club members and all antique cars:

 SQL> SELECT C.OWNER, C.MODEL, A.MODEL, A.MAKE   2  FROM CAR_CLUB C FULL OUTER JOIN ANTIQUE_CARS A   3  ON C.MODEL=A.MODEL; OWNER        MODEL        MODEL        MAKE ------------ ------------ ------------ ------------ Bob          Terraplane   Terraplane   Hudson Lou          Bearcat      Bearcat      Stutz Jane         Mustang Sally        Corvette                           Firedome     DeSoto 

We can perform outer joins using a variation of FROM…WHERE, syntax as well. To do this, we mark the join columns of the table that need to be expanded with (+). This means that if we are performing a left outer join, we must mark the join columns of the right table with (+). This last example is a left outer join, using the FROM…WHERE syntax:

 SQL> SELECT C.OWNER, A.MODEL, A.MAKE   2  FROM CAR_CLUB C, ANTIQUE_CARS A   3  WHERE C.MODEL=A.MODEL(+); OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz Sally Jane Bob          Terraplane   Hudson 

This syntax for outer joins, using the (+) operator, is specific to Oracle, and it is subject to a number of restrictions that don't apply to the standard JOIN syntax. Two examples: If multiple join conditions are specified and you omit the (+) in any of them, a simple join will result, with no indication of the error. Join conditions cannot be combined with OR if you use the (+) operator. Oracle recommends that we use the ANSI standard RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN syntax, instead.

Self-Joins

Not only can we join a table with one or more other tables, we can also join a table with itself. This is useful when there are dependencies between the rows in a table.

Although it is generally best to avoid dependencies between rows in a table, sometimes this is the most convenient way to store the data. A common example is an employee table that lists employees and their managers; managers, in turn, are also employees. Another example is a product catalog that includes components as well as assemblies that may include components. The problem with these dependencies is that the self-joins are required to work with them, and self-joins are often complex and difficult to create and debug.

We'll use a table of the family relations between some early Egyptian gods for our examples:

 EGYPTIAN_GODS NAME      FATHER      MOTHER Shu       Atum Tefnut    Atum Nut       Shu         Tefnut Geb       Shu         Tefnut Osiris    Geb         Nut Isis      Geb         Nut Horus     Osiris      Isis Anubis    Osiris      Nephthys 

It's easiest to think of a self-join as a join of two separate copies of the table. Consider one copy to be a list of children and another copy to be a list of parents. We can join the two by linking the FATHER column in the children table with the NAME column in the parents table. This links each child to information about the child's father; that information includes the father's mother and father, which is to say, the child's grandparents. This query will list each child's paternal grandfather and grandmother:

 SQL> SELECT C.NAME, P.NAME AS FATHER,   2  P.FATHER AS GRANDFATHER,   3  P.MOTHER AS GRANDMOTHER   4  FROM EGYPTIAN_GODS C, EGYPTIAN_GODS P   5  WHERE C.FATHER=P.NAME; NAME       FATHER     GRANDFATHE GRANDMOTHE ---------- ---------- ---------- ---------- Osiris     Geb        Shu        Tefnut Isis       Geb        Shu        Tefnut Horus      Osiris     Geb        Nut Anubis     Osiris     Geb        Nut Nut        Shu        Atum Geb        Shu        Atum 6 rows selected. 

If we join yet another copy of the same table to itself, we can list both maternal and paternal grandparents for each child. Notice that we have three tables in the join statement: one for the children, one for the fathers, and one for the mothers. The join conditions ensure that the FATHER column in the child table matches the NAME column in the fathers table, as before. We've added an additional join condition, to match the MOTHER column in the children table to the NAME column in the mothers table. This way, the FATHER and MOTHER columns in the fathers table are a child's paternal grandparents as before and the FATHER and MOTHER columns in the mothers table are a child's maternal grandparents:

 SQL> SELECT C.NAME,   2  C.MOTHER AS M, M.MOTHER AS M_GM, M.FATHER AS M_GF,   3  C.FATHER AS F, F.MOTHER AS P_GM, F.FATHER AS P_GF   4  FROM EGYPTIAN_GODS C, EGYPTIAN_GODS F, EGYPTIAN_GODS M   5  WHERE F.NAME=C.FATHER AND M.NAME=C.MOTHER; NAME     M       M_GM     M_GF     F        P_GM      P_GF -------- ------- -------- -------- -------- --------- ----- Osiris   Nut     Tefnut   Shu      Geb      Tefnut    Shu Isis     Nut     Tefnut   Shu      Geb      Tefnut    Shu Horus    Isis    Nut      Geb      Osiris   Nut       Geb Nut      Tefnut           Atum     Shu                Atum Geb      Tefnut           Atum     Shu                Atum 

(Where M = Mother, F = Father, M_GM = Maternal Grandmother, P_GF = Paternal Grandfather, etc.)

Combining Data with Set Operations: Unions, Intersect, and Minus

SQL provides a number of operators that allow us to perform common set operations on tables. The UNION operator lets us combine tables. The INTERSECT operator lets us find the data that two tables have in common. The MINUS operator lets us use one table to specify which rows to remove from another table.

The syntax of these operators is very straightforward. They are conjunctions that join two or more SELECT statements together.

Unions

Unions and joins are both ways of combining tables, but they are markedly different. A join is a single SELECT statement that combines tables by linking them together, using one or more join columns. This establishes a relationship. The rows that are selected from one table are related in some way to the rows selected from the other table or tables. A union, in contrast, is the combined result of multiple, independent SELECT statements, and there isn't any explicit relationship whatsoever between the SELECT statements. The only requirement for SELECT statements that are joined by UNION is that the select lists must all return the same number of columns of compatible types, so that their results can be pasted together into a single result.

The basic syntax for a union is:

 select_statement UNION [ALL] select_statement [UNION [ALL] select statement [...]] [ORDER BY order_by_list] 

In the car example that we used to explore joins we had a list of antique cars:

 ANTIQUE_CARS MODEL        MAKE Terraplane   Hudson Firedome     DeSoto Bearcat      Stutz 

Supposing we also had a lists of modern cars too:

 MODERN_CARS MODEL        MAKE Mustang      Ford Corvette     Chevrolet Cherokee     Jeep 

We can combine the two lists with a UNION statement as follows:

 SQL> SELECT MODEL, MAKE FROM ANTIQUE_CARS   2  UNION   3  SELECT MODEL, MAKE FROM MODERN_CARS; MODEL        MAKE ------------ ------------ Bearcat      Stutz Cherokee     Jeep Corvette     Chevrolet Firedome     DeSoto Mustang      Ford Terraplane   Hudson 6 rows selected. 

If we had other lists, such as a list of sports cars, we could combine those as well:

 SPORTS_CARS MODEL        MAKE Bearcat      Stutz Corvette     Chevrolet Viper        Dodge 

We might expect that, by adding this table with three more rows to the union, we'll get a result with a total of nine rows, but we don't:

 SQL> SELECT MODEL, MAKE FROM ANTIQUE_CARS   2  UNION   3  SELECT MODEL, MAKE FROM MODERN_CARS   4  UNION   5  SELECT MODEL, MAKE FROM SPORTS_CARS; MODEL        MAKE ------------ ------------ Bearcat      Stutz Cherokee     Jeep Corvette     Chevrolet Firedome     DeSoto Mustang      Ford Terraplane   Hudson Viper        Dodge 7 rows selected. 

If we compare this result with the previous one, we'll see why. An important characteristic of the UNION statement is that it eliminates all duplicate rows. (Notice that one artifact of the process used to implement this is that the models are listed in alphabetical order.) This new table, SPORTS_CARS, contains two cars that were already listed in the previous two tables, so there is only one new car.

We can override this behavior so that all rows are included by adding the ALL option to the UNION command:

 SQL> SELECT MODEL, MAKE FROM ANTIQUE_CARS   2  UNION ALL   3  SELECT MODEL, MAKE FROM MODERN_CARS   4  UNION ALL   5  SELECT MODEL, MAKE FROM SPORTS_CARS; MODEL        MAKE ------------ ------------ Terraplane   Hudson Firedome     DeSoto Bearcat      Stutz Mustang      Ford Corvette     Chevrolet Cherokee     Jeep Bearcat      Stutz Corvette     Chevrolet Viper        Dodge 9 rows selected. 

As stated before, there is only one requirement for the SELECT statements: They must have compatible select lists. This means that each SELECT statement must have the same number of items in its select lists and the datatypes of each must be the same. So far, our tables have had the same structure and the same column names. If we combine tables that are not alike, we need either to select only compatible columns or to convert the incompatible columns to a compatible type.

The car companies sell lots of things besides cars. Let's suppose we have a table of automobile-related products:

 AUTO_PRODUCTS ITEM_NO   DESCRIPTION          VENDOR RB195     Bow tie workout bag  Chevrolet A1504122  Aladdin travel mug   Dodge ENA55     Radio flashlight     Jeep 

If we want to combine this with one of our other tables for example, the MODERN_CARS table we'll need to decide how we're going to map the columns. VENDOR in this table clearly maps to MAKER in the MODERN_CARS table. But if we want to include both ITEM_NO and DESCRIPTION from the AUTO_PRODUCTS table in the combined table, we'll either need to include a dummy column in the select list for the car table perhaps a literal, such as 'N/A' for "not available" or we could concatenate ITEM_NO and DESCRIPTION into a single column in the select list for the AUTO_PRODUCTS table.

Let's try the first approach, using a dummy column:

 SQL> SELECT 'N/A', MODEL, MAKE FROM MODERN_CARS   2     UNION   3     SELECT ITEM_NO, DESCRIPTION, VENDOR FROM AUTO_PRODUCTS; 'N/A'        MODEL                MAKE ------------ -------------------- ------------ A1504122     Aladdin travel mug   Dodge ENA55        Radio flashlight     Jeep N/A          Cherokee             Jeep N/A          Corvette             Chevrolet N/A          Mustang              Ford RB195        Bow tie workout bag  Chevrolet 6 rows selected. 

Take a look at the column headings. They are taken from the first SELECT statement and aren't entirely appropriate. We can improve that by using column aliases to supply headings that work for entries from either table. We need to do that only for the first SELECT statement, because that's the only one that matters as far as the column headings go.

Let's try the second approach. Instead of using a dummy column for the MODERN_CAR table, we'll combine the ITEM_NO and DESCRIPTION columns in the AUTO_PRODUCTS table.

 SQL> SELECT MODEL AS PRODUCT, MAKE AS COMPANY FROM MODERN_CARS   2  UNION   3  SELECT DESCRIPTION||' - '||ITEM_NO, VENDOR FROM AUTO_PRODUCTS; PRODUCT                             COMPANY ----------------------------------- ------------ Aladdin travel mug - A1504122       Dodge Bow tie workout bag - RB195         Chevrolet Cherokee                            Jeep Corvette                            Chevrolet Mustang                             Ford Radio flashlight - ENA55            Jeep 6 rows selected. 

We can now revisit a problem we saw in the previous chapter where we wanted to perform calculations on different groups of data. We had a fruit table and we wanted to give different discounts based on the quantity:

 FRUIT FRUIT       COLOR   QUANTITY  PRICE  PICKED ----------  ------  --------  -----  --------- Apple       Green         12     .5  12-SEP-02 Apple       Red           12     .5  15-SEP-02 Mango       Yellow        10    1.5  22-SEP-02 Mangosteen  Purple         5      2  25-SEP-02 Durian                     2     15 Orange      Orange        10      1  28-AUG-02 

Suppose these are the discounts:

 No discount: QUANTITY < 5 10%:         QUANTITY >= 5 AND QUANTITY < 10 20%:         QUANTITY >= 10 AND QUANTITY < 50 30%:         QUANTITY >= 50 

We can apply these discounts by using a SELECT statement for each range and combining the results using a union:

 SQL> SELECT FRUIT, QUANTITY, PRICE,   2      PRICE*QUANTITY AS SUBTOTAL, 'No discount' AS DISCOUNT   3    FROM FRUITS WHERE QUANTITY<5   4  UNION   5  SELECT FRUIT, QUANTITY, PRICE,   6      PRICE*QUANTITY*.9, '10 Percent'   7    FROM FRUITS WHERE QUANTITY >= 5 AND QUANTITY < 10   8  UNION   9  SELECT FRUIT, QUANTITY, PRICE,  10      PRICE*QUANTITY*.8, '20 Percent'  11    FROM FRUITS WHERE QUANTITY >= 10 AND QUANTITY < 50  12  UNION  13  SELECT FRUIT, QUANTITY, PRICE,  14      PRICE*QUANTITY*.7, '30 Percent'  15    FROM FRUITS WHERE QUANTITY >= 50  16  ORDER BY QUANTITY; FRUIT        QUANTITY      PRICE   SUBTOTAL DISCOUNT ---------- ---------- ---------- ---------- ----------- Durian              2         15         30 No discount Mangosteen          5          2          9 10 Percent Mango              10        1.5         12 20 Percent Orange             10          1          8 20 Percent1 Apple              12         .5        4.8 20 Percent 

Notice that at the end we've added an ORDER BY clause at the end to make the results a little more readable.

Intersect and Minus

The INTERSECT and MINUS operators are not used very often but, together with UNION, they make for a complete set of set operations. INTERSECT is roughly complementary to the UNION operation. Where UNION allowed us to combine the results of two queries, eliminate duplicates, and return the rows that remained, INTERSECT allows us to compare the rows in each table and return any duplicated rows.

graphics/note_icon.gif

INTERSECT is not exactly complementary to UNION because UNION will remove duplicates even if they appear in the same table, but INTERSECT will remove duplicates only if they appear in separate tables.

For example, we saw that our SPORTS_CAR table included some cars that were included in the ANTIQUE_CARS tables. We can find out which cars are in both with this query:

 SQL> SELECT MODEL, MAKE FROM ANTIQUE_CARS   2     INTERSECT   3     SELECT MODEL, MAKE FROM SPORTS_CARS; MODEL        MAKE ------------ ------------ Bearcat      Stutz 

The MINUS operator returns the results of the first SELECT statement minus any rows that also appear in the results of the second SELECT statement. We know from the previous intersect query that the Stutz Bearcat is the only car that appears in both ANTIQUE_CARS and SPORTS_CARS, so a MINUS query with these two tables will return all rows in the ANTIQUE_CARS except for the Stutz Bearcat:

 SQL> SELECT MODEL, MAKE FROM ANTIQUE_CARS   2  MINUS   3  SELECT MODEL, MAKE FROM SPORTS_CARS; MODEL        MAKE ------------ ------------ Firedome     DeSoto Terraplane   Hudson 

Notice that UNION and INTERSECT are commutative; we can change the order of the tables without changing the results. MINUS is not commutative. We get different results, depending on which table we put first:

 SQL> SELECT MODEL, MAKE FROM SPORTS_CARS   2  MINUS   3  SELECT MODEL, MAKE FROM ANTIQUE_CARS; MODEL        MAKE ------------ ------------ Corvette     Chevrolet Viper        Dodge SQL> 

These ask different questions. In English, the first MINUS example asks, "Which antique cars are not sports cars?" The second asks, "Which sports cars are not antiques?"



Java Oracle Database Development
Java Oracle Database Development
ISBN: 0130462187
EAN: 2147483647
Year: 2002
Pages: 71

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net