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:

General Functions in SQL

These functions work with any data type and pertain to using nulls.

• NVL (expr1,expr2): Converts a null value to an actual value
• NVL2 (expr1,expr2,expr3): If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
• NULLIF (expr1,expr2): Compares two expressions and returns null if they are equal, or the first expression if they are not equal.
• COALESCE ( expr1,expr2,....., expr n): Returns the first non-null expression in the expression list.

Using NVL Function

• Data types that can be used are date, character, and number.
• Data types must match:
• NVL ( comm,0 )
• NVL (hiredate,'01-JAN-13')
• NVL(job,'No Job Yet')

Example:

Using the NVL2 function

• The NVL2 function examines the first expression. 
• If the first expression is not null, then the NVL2 function returns the second expression. 
• If the first expression is null, then the third expression is returned.

Syntax:

NVL2(expr1,expr2,expr3)

In the syntax:

expr1 : is the source value or expression that may contain null.

expr2: is the value returned if exp1 is null.

expr3: is the value returned if expr2 is null.

Example:

Using the NULLIF Function

• The NULLIF function compares two expressions. 
• If they are equal, the function returns null. 
• If they are not equal the function returns the first expression. 
• You cannot specify the literal NULL for first expression.

Syntax: 

 NULLIF(expr1,expr2)

In the syntax:

expr1  is the source value compared to expr2.

expr2  is the source value compared with expr1. (if it is not equal to expr1, expr1 is returned.)

Example:

In the above example, no employee having salary=their annual commision. 

So everytime in the result tab, first value is returned. 

If in the case any employee having sal= his annual commission (12*comm) then we should have noticed null in RESULT tab.

Using the COALESCE Function

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
• If the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
• This function finds the first non null expression and returns. Thats it. 

Syntax:

              COALESCE(expr1,expr2,......exprn)

In the Syntax:

expr1  returns this expression if it is not null.

expr2  returns this expression if the first expression is null and this expression is not null.

exprn  returns this expression if the preceding expression are null.

Example:

Conditional Expression  covers in Next post.

• CASE expression
• DECODE function

 

Conversion Function in SQL

In some cases, Oracle server uses data of one data type where it expects data of a different data type. When this happens, Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by Oracle server, or explicitly by the user.

Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert the following:

                    From                                           To

        VARCHAR2 OR CHAR                   NUMBER

        VARCHAR2 OR CHAR                   DATE

        NUMBER                                         VARCHAR2

        DATE                                                VARCHAR2

The assignment succeed if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.

Explicit data type conversion covers in Next post.