-
USING RELATIONAL OPERATOR
- ? Combining tables with similar structures
- ? Combining tables with different structures
- ? Deriving meaningful data from multiple tables
-
UNION
The UNION operator is the SQL implementation of relational algebra’s union operator. The UNION operator enables you to draw information from two or more tables that have the same structure. Same structure means
- ? The tables must all have the same number of columns.
- ? Corresponding columns must all have identical data types and lengths.
- When these criteria are met, the tables are union compatible. The union of two tables returns all the rows that appear in either table and eliminates duplicates.
- The UNION DISTINCT operator functions identically to the UNION operator without the DISTINCT keyword. In both cases, duplicate rows are eliminated from the result set.
-
CORRESPONDING operation
- SELECT *
- FROM OUTFIELDER
- UNION CORRESPONDING (FirstName, LastName, Putouts, Errors, FieldPct)
- SELECT *
FROM PITCHER ;
- The result table holds the first and last names of all the outfielders and pitchers, along with the putouts, errors, and fielding percentage of each player. As with the simple UNION, duplicates are eliminated. Thus, if a player spent
- some time in the outfield and also pitched in one or more games, the UNION CORRESPONDING operation loses some of his statistics. To avoid this problem, use UNION ALL CORRESPONDING.
-
-
EXCEPT
- SELECT *
- FROM OUT
- EXCEPT CORRESPONDING (PagerID)
- SELECT *
- FROM PAGERS;
This query returns all the rows in the OUT table whose PagerID is not also present in the PAGERS table.
-
BASIC JOIN
- The result table is the Cartesian product of the two source tables
-
-
-
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.
-
-
-
outer join:
left outer join
right outer joim
full outer join
- left join
- rigt outer join:
- I bet you figured out how the right outer join behaves. Right! The right outer join preserves unmatched rows from the right table but discards unmatched rows from the left table. You can use it on the same tables and get the same
- result by reversing the order in which you present tables to the join:
full outer join:
The full outer join combines the functions of the left outer join and the right outer join. It retains the unmatched rows from both the left and the right tables. Consider the most general case of the company database used in the preceding examples. It could have
-
|
|