SQL III - 10

  1. USING RELATIONAL OPERATOR
    • ? Combining tables with similar structures
    • ? Combining tables with different structures
    • ? Deriving meaningful data from multiple tables
  2. 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.
    • Image Upload 2
    • 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.
  3. 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.
  4. INTERSECT
    Image Upload 4

    Image Upload 6
  5. 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.
  6. BASIC JOIN
    • The result table is the Cartesian product of the two source tables
    • Image Upload 8

    Image Upload 10
  7. EQUI-JOIN
    Image Upload 12

    Image Upload 14

    Image Upload 16
  8. cross join
    Image Upload 18
  9. 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.
    • Image Upload 20
  10. condition join
    Image Upload 22
  11. column-name join
    Image Upload 24

    Image Upload 26
  12. outer join:
    left outer join
    right outer joim
    full outer join
    • left join
    • Image Upload 28
    • Image Upload 30

    Image Upload 32

    • Image Upload 34
    • Image Upload 36

    • 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
  13. union join
    Image Upload 38

    Image Upload 40

    Image Upload 42


    Image Upload 44

    • 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 with
    • the 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
Author
dau108
ID
145763
Card Set
SQL III - 10
Description
SQL III - 10
Updated