Previous | Table of Contents | Next |
Outer Joins
An outer join instructs the database to return a row from one table, even if no corresponding row is found in another table. Listing 2.9 uses an outer join to get the names and grade point averages of students who have not declared a degree plan.
Listing 2.9 A query using an outer join.
SELECT S.last_name, S.first_name, S.gpa, DP.degree_plan_description, SFA.total_financial_aid FROM STUDENTS S, DEGREE_PLANS DP, STUDENT_FINANCIAL_AID SFA WHERE S.financing_num = SFA.financing_num AND S.degree_plan = DP.degree_plan (+) AND S.gpa > 3.0;
This query will return the names of students who have not declared a degree plan as well as those students who have declared a degree plan.
When joining two or more tables in a query, columns that exist in both tables must be referenced by the name of the source table for the column. Oracle allows developers to assign an alias to a table to make column name references less of a chore to use. The following example illustrates this type of reference ”first using full table names and then using table aliases.
SELECT STUDENTS.ssn, STUDENT_FINANCIAL_AID.total_aid FROM STUDENTS, STUDENT_FINANCIAL_AID WHERE STUDENTS.financing_num = STUDENT_FINANCIAL_AID.financing_num; SELECT S.ssn, SFA.total_aid FROM STUDENTS S, STUDENT_FINANCIAL_AID SFA WHERE S.financing_num = SFA.financing_num;
The second query is functionally identical to the first, but is much less tiresome to write.
TIP: Creating Meaningful Table AliasesA simple method of creating a meaningful alias for a table name is to use the initial of each word in the table name. For instance, in the previous example the STUDENT_FINANCIAL_AID table was given the alias SFA .
Oracle uses a sophisticated lock mechanism to prevent multiple users from altering the same data at the same time. This mechanism is typically invisible to database users. It s not uncommon, especially during system development or after system hang-ups, for a deadlock to exist.
Consider this situation: user jallen has the STUDENTS table locked and needs to obtain a lock on the STUDENT_FINANCIAL_AID table. User msmith has the STUDENT_FINANCIAL_AID table locked and needs to obtain a lock on the STUDENTS table. This is a deadlock, because each user is preventing the other from completing a transaction.
Locks are cleared when a user issues a COMMIT or ROLLBACK statement. Locks can also be explicitly obtained, but this is a rare event. It s best to let Oracle determine which objects that you need to lock.
A NULL value is a column that does not have a defined value. A NULL value is never equal to any other value, including zero and NULL . The expression
NULL = NULL
returns a FALSE result. Any column in a table that is not constrained by a NOT NULL or primary key constraint can contain a NULL value.
You can test for NULL values in a column using the IS NULL operator:
WHERE <column> IS NULL WHERE <column> IS NOT NULL
In reality, unexpected NULL values can cause all sorts of heartache for developers. The best way to handle this situation is to be aware that columns can contain NULL values while you re coding and to be on the lookout for situations in which NULL values are likely to be present.
SQL incorporates operators that are quite similar to the operators in other languages, as shown in Table 2.2.
| |
---|---|
Operator | Usage |
** | The exponentiation operator ( 2**2 IS 4 ) |
NOT | Negates a condition ( IS NOT NULL , NOT IN , NOT BETWEEN ) |
+ | The addition operator ( 2 + 2 IS 4 ) as well as the unary indicator of a positive number (+2) |
- | The subtraction operator ( 4 - 2 IS 2 ) as well as the unary indicator of a negative number (-2) |
* | The multiplication operator ( 2 * 2 IS 4 ) |
/ | The division operator ( 4 / 2 IS 2 ) |
The concatenation operator ( ˜A ˜B IS ˜AB ) | |
= | The equation operator ( 2 = 2 ) |
!= | The non-equation operator ( 3 != 2 ) |
< | The less than operator ( 2 < 4 ) |
> | The greater than operator ( 4 > 2 ) |
<= | The less than or equal to operator ( 2 <= 4, 2 <= 2 ) |
>= | The greater than or equal to operator ( 4 >= 2, 2 >= 2 ) |
IS NULL | Tests a variable or condition for a NULL value ( first_name IS NULL ) |
LIKE | Allows wildcard searches (last_name LIKE ˜SM% ) |
BETWEEN | An inclusive range test ( 1, 2, AND 3 ARE BETWEEN 1 AND 3 ) |
IN | A set operator ( 2 IS IN {1, 2, 3} ) |
AND | A logical and ( x < y AND y < z ) |
OR | A logical or ( x < y OR y < z ) |
|
Any of these operators may be used anywhere in a DML statement with the exception of the FROM clause, as shown by these examples:
SELECT 2**nRealValue FROM CHECK_VALUES WHERE nRealValueProcessed = 'F'; DELETE FROM STUDENTS WHERE overall_gpa < 0.2; UPDATE STUDENTS SET overall_gpa = 1.02 * most_recent_gpa WHERE ssn = 999999999; INSERT INTO CHECK_VALUES (nRealValueProcessed, nRealValue) VALUES ('F', (2.031 ** 3) / 9);
A subquery is a query within the WHERE clause of a DML statement, as shown in this example:
SELECT ssn FROM STUDENTS WHERE overall_gpa = (SELECT max (overall_gpa) FROM STUDENTS);
Subqueries can be used in any type of DML statement.
Previous | Table of Contents | Next |