# SQL III - 10

 The flashcards below were created by user dau108 on FreezingBlue Flashcards. 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 spentsome 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. INTERSECT 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 EQUI-JOIN cross join 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, allcolumns in one table that have the same names, types, and lengths as corresponding columns in the second table are compared for equality. condition join column-name join 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 sameresult 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 union join This data arrangement is not particularly enlightening. The employee ID numbers appear three times, and the projects and skills are duplicated for each employee. The inner joins are not well suited to answering this type of question. You can put the union join to work here, along with some strategically chosen SELECT statements, to produce a more suitable result. You begin withthe basic union join: SELECT * FROM EMPLOYEE E UNION JOIN PROJECTS P UNION JOIN SKILLS S ; Notice that the union join has no ON clause. It doesn’t filter the data, so an ON clause isn’t needed. This statement produces the result shown in Table 10-8 Authordau108 ID145763 Card SetSQL III - 10 DescriptionSQL III - 10 Updated2012-04-05T10:22:37Z Show Answers