UNION ALL

As mentioned previously, the UNION operation normally eliminates any duplicate rows that result from its operation, which is the desired result most of the time. Sometimes, however, you may want to preserve duplicate rows. On those occasions, use UNION ALL.

The following code shows you what UNION ALL produces when it’s used with the DEPOSIT and KINGSTON tables:

SELECT * FROM DEPOSIT

UNION ALL

SELECT * FROM KINGSTON ;

ProductName QuantityInStock

----------- ---------------

185_Express 12

505_Express 5

510_Express 6

520_Express 2

550_Express 3

185_Express 15

505_Express 7

510_Express 6

520_Express 2

550_Express 1

UNION CORRESPONDING

You can sometimes form the union of two tables even if they are not union-compatible. If the columns you want in your result set are present and compatible in both source tables, you can perform a UNION CORRESPONDING operation. Only the specified columns are considered, and they are the only columns displayed in the result set.

Suppose ACME Systems opens a third warehouse, in Jefferson, Maine. A new table named JEFFERSON is added to the database, which includes Product and QuantityInStock columns (as the DEPOSIT and KINGSTON tables do), but also has an additional column named QuantityOnHold. A UNION or UNION ALL of JEFFERSON with either DEPOSIT or KINGSTON would not return any rows because there is not a complete match between all the columns of JEFFERSON and all the columns of the other two tables. However, you can still add the JEFFERSON data to that of either DEPOSIT or KINGSTON by specifying only the columns in JEFFERSON that correspond with the columns in the other table. Here’s a sample query:

SELECT *

FROM JEFFERSON

UNION CORRESPONDING BY

(ProductName, QuantityInStock)

SELECT *

FROM KINGSTON ;

The result table holds the products and the quantities in stock at both warehouses. As with the simple UNION, duplicates are eliminated. Thus, if the Jefferson warehouse happens to have the same quantity of a particular product that the Kingston warehouse has, the UNION CORRESPONDING operation loses one of those rows. To avoid this problem, use UNION ALL CORRESPONDING.

Each column name in the list following the CORRESPONDING keyword must be a name that exists in both unioned tables. If you omit this list of names, an implicit list of all names that appear in both tables is used. But this implicit list of names may change when new columns are added to one or both tables. Therefore, explicitly listing the column names is better than omitting them.

Cartesian product or cross join

Any multitable query is a type of JOIN. The source tables are joined in the sense that the result table includes information taken from all the source tables. The simplest JOIN is a two-table SELECT that has no WHERE clause qualifiers. Every row of the first table is joined to every row of the second table. The result table is referred to as the Cartesian product of the two source tables — the direct product of the two sets. (The less fancy name for the same thing is cross join.) The number of rows in the result table is equal to the number of rows in the first source table multiplied by the number of rows in the second source table.

For example, imagine that you’re the personnel manager for a company, and that part of your job is to maintain employee records. Most employee data, such as home address and telephone number, is not particularly sensitive. But some data, such as current salary, should be available only to authorized personnel. To maintain security of the sensitive information, you’d probably keep it in a separate table that is password protected. Consider the following pair of tables:

EMPLOYEE COMPENSATION

-------- ------------

EmpID Employ

FName Salary

LName Bonus

City

Phone

Fill the tables with some sample data:

EmpID FName LName City Phone

----- ----- ----- ---- -----

1 Jenny Smith Orange 555-1001

2 Bill Jones Newark 555-3221

3 Val Brown Nutley 555-6905

4 Justin Time Passaic 555-8908

Employ Salary Bonus

------ ------ -----

1 63000 10000

2 48000 2000

3 54000 5000

4 52000 7000

Create a virtual result table with the following query:

SELECT *

FROM EMPLOYEE, COMPENSATION ;

which can also be written

SELECT *

FROM EMPLOYEE CROSS JOIN COMPENSATION ;

Both of the above formulations do exactly the same thing. This query produces

EmpID FName LName City Phone Employ Salary Bonus

----- ----- ----- ---- ----- ------ ------ -----

1 Jenny Smith Orange 555-1001 1 63000 10000

1 Jenny Smith Orange 555-1001 2 48000 2000

1 Jenny Smith Orange 555-1001 3 54000 5000

1 Jenny Smith Orange 555-1001 4 52000 7000

2 Bill Jones Newark 555-3221 1 63000 10000

2 Bill Jones Newark 555-3221 2 48000 2000

2 Bill Jones Newark 555-3221 3 54000 5000

2 Bill Jones Newark 555-3221 4 52000 7000

3 Val Brown Nutley 555-6905 1 63000 10000

3 Val Brown Nutley 555-6905 2 48000 2000

3 Val Brown Nutley 555-6905 3 54000 5000

3 Val Brown Nutley 555-6905 4 52000 7000

4 Justin Time Passaic 555-8908 1 63000 10000

4 Justin Time Passaic 555-8908 2 48000 2000

4 Justin Time Passaic 555-8908 3 54000 5000

4 Justin Time Passaic 555-8908 4 52000 7000

The result table, which is the Cartesian product of the EMPLOYEE and COMPENSATION tables, contains considerable redundancy. Furthermore, it doesn’t make much sense. It combines every row of EMPLOYEE with every row of COMPENSATION. The only rows that convey meaningful information are those in which the EmpID number that came from EMPLOYEE matches the Employ number that came from COMPENSATION. In those rows, an employee’s name and address are associated with that same employee’s compensation.

When you’re trying to get useful information out of a multitable database, the Cartesian product produced by a cross join is almost never what you want, but it’s almost always the first step toward what you want. By applying constraints to the JOIN with a WHERE clause, you can filter out the unwanted rows. The most common JOIN that uses the WHERE clause filter is the equi-join.

Equi-join

An equi-join is a cross join with the addition of a WHERE clause containing a condition specifying that the value in one column in the first table must be equal to the value of a corresponding column in the second table. Applying an equi-join to the example tables from the previous section brings a more meaningful result:

SELECT *

FROM EMPLOYEE, COMPENSATION

WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ;

This produces the following:

EmpID FName LName City Phone Employ Salary Bonus

----- ------ ----- ---- ----- ------ ------ -----

1 Jenny Smith Orange 555-1001 1 63000 10000

2 Bill Jones Newark 555-3221 2 48000 2000

3 Val Brown Nutley 555-6905 3 54000 5000

4 Justin Time Passaic 555-8908 4 52000 7000

In this result table, the salaries and bonuses on the right apply to the employees named on the left. The table still has some redundancy because the EmpID column duplicates the Employ column. You can fix this problem by specifying in your query which columns you want selected from the COMPENSATION table:

SELECT EMPLOYEE.*,COMPENSATION.Salary,COMPENSATION.Bonus

FROM EMPLOYEE, COMPENSATION

WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ;

This produces the following result:

EmpID FName LName City Phone Salary Bonus

----- ----- ----- ---- ----- ------ -----

1 Jenny Smith Orange 555-1001 63000 10000

2 Bill Jones Newark 555-3221 48000 2000

3 Val Brown Nutley 555-6905 54000 5000

4 Justin Time Passaic 555-8908 52000 7000

This table tells you what you want to know, but doesn’t burden you with any extraneous data. The query is somewhat tedious to write, however. To avoid ambiguity, it makes good sense to qualify the column names with the names of the tables they came from. However, writing those table names repeatedly can be tiresome.

You can cut down on the amount of typing by using aliases (or correlation names). An alias is a short name that stands for a table name. If you use aliases in recasting the preceding query, it comes out like this:

SELECT E.*, C.Salary, C.Bonus

FROM EMPLOYEE E, COMPENSATION C

WHERE E.EmpID = C.Employ ;

In this example, E is the alias for EMPLOYEE, and C is the alias for COMPENSATION. The alias is local to the statement it’s in. After you declare an alias (in the FROM clause), you must use it throughout the statement. You can’t use both the alias and the long form of the table name.

Mixing the long form of table names with aliases creates confusion. Consider the following example, which is confusing:

SELECT T1.C, T2.C

FROM T1 T2, T2 T1

WHERE T1.C > T2.C ;

In this example, the alias for T1 is T2, and the alias for T2 is T1. Admittedly, this isn’t a smart selection of aliases, but it isn’t forbidden by the rules. If you mix aliases with long-form table names, you can’t tell which table is which.

The preceding example with aliases is equivalent to the following SELECT with no aliases:

SELECT T2.C, T1.C

FROM T1, T2

WHERE T2.C > T1.C ;

SQL enables you to join more than two tables. The maximum number varies from one implementation to another. The syntax is analogous to the two-table case:

SELECT E.*, C.Salary, C.Bonus, Y.TotalSales

FROM EMPLOYEE E, COMPENSATION C, YTD_SALES Y

WHERE E.EmpID = C.Employ

AND C.Employ = Y.EmpNo ;

This statement performs an equi-join on three tables, pulling data from corresponding rows of each one to produce a result table that shows the salespeople’s names, the amount of sales they are responsible for, and their compensation. The sales manager can quickly see whether compensation is in line with production.

Storing a salesperson’s year-to-date sales in a separate YTD_SALES table ensures better performance and reliability than keeping that data in the EMPLOYEE table. The data in the EMPLOYEE table is relatively static. A person’s name, address, and telephone number don’t change very often. In contrast, the year-to-date sales change frequently. (You hope.) Because the YTD_SALES table has fewer columns than EMPLOYEE, you may be able to update it more quickly. If, in the course of updating sales totals, you don’t touch the EMPLOYEE table, you decrease the risk of accidentally modifying EMPLOYEE information that should stay the same.

Natural join

The natural join is a special case of an equi-join. In the WHERE clause of an equi-join, a column from one source table is compared with a column of a second source table for equality. The two columns must be the same type and length and must have the same name. In fact, in a natural join, all columns in one table that have the same names, types, and lengths as corresponding columns in the second table are compared for equality.

Imagine that the COMPENSATION table from the preceding example has columns EmpID, Salary, and Bonus rather than Employ, Salary, and Bonus. In that case, you can perform a natural join of the COMPENSATION table with the EMPLOYEE table. The traditional JOIN syntax looks like this:

SELECT E.*, C.Salary, C.Bonus

FROM EMPLOYEE E, COMPENSATION C

WHERE E.EmpID = C.EmpID ;

This query is a natural join. An alternate syntax for the same operation is the following:

SELECT E.*, C.Salary, C.Bonus

FROM EMPLOYEE E NATURAL JOIN COMPENSATION C ;

Condition join

A condition join is like an equi-join, except the condition being tested doesn’t have to be equality (although it can be). It can be any well-formed predicate. If the condition is satisfied, the corresponding row becomes part of the result table. The syntax is a little different from what you have seen so far, in that the condition is contained in an ON clause rather than a WHERE clause.

Suppose Acme Systems wants to know which products the Fort Deposit warehouse has in larger numbers than does the East Kingston warehouse. This question is a job for a condition join:

SELECT *

FROM DEPOSIT JOIN KINGSTON

ON DEPOSIT.QuantityInStock > KINGSTON.QuantityInStock ;

Within the predicate of a condition join, ON syntax is used in place of WHERE syntax.

Column-name join

The column-name join is like a natural join, but it’s more flexible. In a natural join, all the source table columns that have the same name are compared with each other for equality. With the column-name join, you select which same-name columns to compare. You can choose them all if you want, making the column-name join effectively a natural join. Or you may choose fewer than all same-name columns. In this way, you have a great degree of control over which cross product rows qualify to be placed into your result table.

Suppose you are Acme Systems, and you have shipped the exact same number of products to the East Kingston warehouse that you have shipped to the Fort Deposit warehouse. So far, nothing has been sold, so the number of products in inventory in East Kingston should match the number in Fort Deposit. If there are mismatches, it means that something is wrong. Either some products were never delivered to the warehouse, or they were misplaced or stolen after they arrived. With a simple query, you can retrieve the inventory levels at the two warehouses.

SELECT * FROM DEPOSIT ;

ProductName QuantityInStock

----------- ---------------

185_Express 12

505_Express 5

510_Express 6

520_Express 2

550_Express 3

SELECT * FROM KINGSTON ;

ProductName QuantityInStock

----------- ---------------

185_Express 15

505_Express 7

510_Express 6

520_Express 2

550_Express 1

For such small tables, it is fairly easy to see which rows don’t match. However, for a table with thousands of rows, it’s not so easy. You can use a column-name join to see whether any discrepancies exist. I show only two columns of the DEPOSIT and KINGSTON tables, to make it easy to see how the various relational operators work on them. In any real application, such tables would have additional columns, and the contents of those additional columns would not necessarily match. With a column-name join, the join operation considers only the columns specified.

SELECT *

FROM DEPOSIT JOIN KINGSTON

USING (ProductName, QuantityInStock) ;

Note the USING keyword, which tells the DBMS which columns to use.

The result table shows only the rows for which the number of products in stock at Fort Deposit equals the number of products in stock at East Kingston:

ProductName QuantityInStock ProductName QuantityInStock

----------- --------------- ----------- ---------------

510_Express 6 510_Express 6

520_Express 2 520_Express 2

Wow! Only two products match. There is a definite “shrinkage” problem at one or both warehouses. Acme needs to get a handle on security.

Inner join

By now, you’re probably getting the idea that joins are pretty esoteric and that it takes an uncommon level of spiritual discernment to deal with them adequately. You may have even heard of the mysterious inner join and speculated that it probably represents the core or essence of relational operations. Well, ha! The joke is on you: There’s nothing mysterious about inner joins. In fact, all the joins covered so far in this chapter are inner joins. I could have formulated the column-name join in the last example as an inner join by using the following syntax:

SELECT *

FROM DEPOSIT INNER JOIN KINGSTON

USING (ProductName, QuantityInStock) ;

The result is the same.

The inner join is so named to distinguish it from the outer join. An inner join discards all rows from the result table that don’t have corresponding rows in both source tables. An outer join preserves unmatched rows. That’s the difference. Nothing metaphysical about it.

Outer join

When you’re joining two tables, the first one (call it the one on the left) may have rows that don’t have matching counterparts in the second table (the one on the right). Conversely, the table on the right may have rows that don’t have matching counterparts in the table on the left. If you perform an inner join on those tables, all the unmatched rows are excluded from the output. Outer joins, however, don’t exclude the unmatched rows. Outer joins come in three types: the left outer join, the right outer join, and the full outer join.