SQL III  10
Home > Flashcards > Print Preview
The flashcards below were created by user
dau108
on
FreezingBlue Flashcards. What would you like to do?

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 equijoin. In the WHERE clause of an equijoin, 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
