Database Reference
In-Depth Information
If so, every time a given value of Department occurs in the table, that value will be matched
with the same value of DeptPhone.
The following correlated subquery will find any rows that violate this assumption:
/* *** SQL-Query-CH08-04 *** */
SELECT
E1.EmployeeNumber, E1.Department, E1.DeptPhone
FROM
EMPLOYEE AS E1
WHERE
E1.Department IN
(SELECT E2.Department
FROM
EMPLOYEE AS E2
WHERE
E1.Department = E2.Department
AND E1.DeptPhone <> E2.DeptPhone);
The results of this query for the data in Figure 8-1 are:
A listing like this can readily be used to find and fix any rows that violate the functional
dependency.
EXISTS and NOT EXISTS
When we use the
SQL EXISTS keyword
or the
SQL NOT EXISTS keyword
in a query, we are
creating another form of correlated subquery. We can write the last correlated subquery using
the SQL EXISTS keyword, as follows:
/* *** SQL-Query-CH08-05 *** */
SELECT
E1.EmployeeNumber, E1.Department, E1.DeptPhone
FROM
EMPLOYEE AS E1
WHERE
EXISTS
(SELECT E2.Department
FROM
EMPLOYEE AS E2
WHERE
E1.Department = E2.Department
AND E1.DeptPhone <> E2.DeptPhone);
Because using EXISTS creates a form of a correlated subquery, the processing of the
SELECT statements is nested. The first row of E1 is input to the subquery. If the subquery finds
any row in E2 for which the department names are the same and the department phone num-
bers are different, then the EXISTS is true and the Department and DeptPhone for the first
row are selected. Next, the second row of E1 is input to the subquery, the SELECT is processed,
and the EXISTS is evaluated. If true, the Department and DeptPhone of the second row are
selected. This process is repeated for all of the rows in E1.
The results of SQL-Query-CH08-05 are identical to the previous results:
Search WWH ::
Custom Search