Databases Reference
In-Depth Information
The second difference is a bit more complex. Janice's goal was to find out if
the employee had any previous jobs and, if so, return the ending date for the last
job that employee had. Remember that you can have the SQL text (in parenthe-
ses) of a correlated subquery in the SELECT, FROM, or WHERE clause of the parent
query. In this case, the correlated subquery is as follows:
(select max(end_date)+1
from job_history
where employee_id = emp.employee_id)
For each row in the EMPLOYEE table, this subquery will find the last date that
the employee worked in any department and adds one day, resulting in the first
date that the employee started in their current position. But if the employee has
never switched departments, there will be no rows in the JOB_HISTORY table,
and therefore the subquery will return a NULL result. The solution is to wrap the
COALESCE function around the query.
The COALESCE function will return the first non-NULL argument in the argument
list. The HIRE_DATE column is specified as the second argument to COALESCE, so
if the employee has never switched departments, the original hire date will be
returned from this function:
coalesce(
(select max(end_date)+1
from job_history
where employee_id = emp.employee_id),
hire_date) strt_date,
To reiterate, the above section of SQL evaluates to either the first day
employees started in their current department or their hiring date, if they have
never switched departments. The column alias STRT_DATE is assigned to this
derived column.
The next morning, Janice realizes that she could have used UNION ALL
instead of UNION in this query. There will never be any duplicate records
between the two queries in this compound query, mainly because the data-
base does not store the employee's current job position and starting date in
the JOB_HISTORY table.
DBAs should be on the lookout for queries that use
UNION
when
UNION
ALL
would
produce the same desired results. Because
UNION
does a sort while removing dupli-
cates, many
UNION
queries will have a much more noticeable performance impact on
the system than the same queries that use
UNION ALL
. Oracle 10g's web-based
Enterprise Manager Database Control can easily identify SQL statements or sessions
with a high impact on the system using the Top SQL and Top Sessions functions.
