Unit 4 - JOIN (Obtaining Data From More than One Table)

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

Sometimes we need to use data from more than one table. Rows in one table can be joined to rows in another table according to common values; i.e. usually primary and foreign key columns.

Types of Joins

Joins (8i and Prior)                           SQL:1999 (Complaint Joins)

Cartesian Products:

A Cartesian Product is formed when:

•       A join condition is omitted.
•      A join condition is invalid.
•      All rows in the  first table are joined to all rows  in the second table.

To avoid a Cartesian product, always have a valid join condition in a WHERE clause.

Example:

Conditional Expression in SQL

• Provide the use of IF-THEN-ELSE logic within a SQL statement.
• Use two methods:
• CASE expression.
• DECODE function.

The CASE Expression

• Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.

Syntax:

CASE expr WHEN comparison_expr1 THEN return_expr1

                    [ WHEN comparison _expr2 THEN return_expr2

                     WHEN comparison_exprn THEN return_exprn

                     ELSE else_expr ]

END

• In a simple CASE expression, Oracle searches for the first WHEN --- THEN pair for which expr is equal to comparison_expr and returns return_expr. 
• If none of the WHEN --- THEN pairs meet this condition, and ELSE clause exists, then Oracle returns else_expr.
• Otherwise, Oracle returns null.
• You can't specify the literal NULL for all the return_exprs and the else_expr.

Example:

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

Syntax:

DECODE(col|expression, search1, result1

                                        [,search2,result2,...]

                                        [,default])

NOTE:

• The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. 
• The DECODE function decodes expression after comparing it to each search value.
• If the expression is the same as search, result is returned.
• If the default value is omitted, a null value is returned where a search value does not match any of the result value.

Example: