0248-0251
Page 248
| TIP |
Although alias names can be as short as one character, it is easier at maintenance time if you use three-character descriptive strings, as shown in this example. The association of columns to tables is made more easily. |
Avoiding Cartesian Joins
A Cartesian join happens when the WHERE clause is not properly constructed . A record is returned for every occurrence in table Z and table X:
SELECT X.name, Z.last_name, Z.first_name FROM emp Z, dept X ORDER BY X.name, Z.last_name;
If the emp table has 10 employees and the department table has 3 departments, this query returns 30 rows. For each department name, all employees are listed, because the tables are not joined properly (not at all in this example). If the join condition WHERE X.dept_no = Z.dept_no exists, only 10 rows are retrieved.
Outer Joins
When the columns of a table are outer-joined, this tells the database to retrieve rows even if data is not found. The plus symbol (+) is used to denote an outer-join condition, as shown in the following example:
SELECT d.name, a.city, e.last_name, e.first_name FROM emp e, dept d, addresses a WHERE d.dept_no(+) = e.dept_no AND a.adrs_id = d.adrs_id ORDER BY d.name,e.last_name,e.first_name;
If the president of the company is never assigned a department, his name is never retrieved in previous examples, because his department number is null. The outer join causes all rows to be retrieved even if there is not a match for dept_no.
Outer joins are effective but make the query perform more slowly. You might consider a query rewrite if performance is hampered.
Page 249
Subqueries
Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, it is called a correlated subquery.
A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced back in the parent query, however. The following is an example of a non-correlated subquery:
SELECT e.first_name, e.last_name, e.job_title FROM emp e WHERE e.dept_no in (SELECT dept_no FROM dept WHERE name = `ADMIN');
In this example, all employee names and job titles are retrieved for the department `ADMIN'. Notice the use of the operator in when referring to the subquery. The in operator is used when one or more rows might be returned by a subquery. If the equal operator (=) is used, it is assumed that only one row is returned. If the equal operator (=) is used and more than one row is returned, Oracle returns an error.
You can write this statement by directly joining the dept table with the emp table in the main or parent query. Subqueries are sometimes used for performance gain. If the parent query contains many tables, it might be advantageous to break the WHERE clause into subqueries:
SELECT d.name, e.first_name, e.last_name, e.job_title FROM emp e, dept d WHERE e.dept_no = d.dept_no AND d.adrs_id = (SELECT adrs_id FROM ADDRESSES WHERE adrs_id = d.adrs_id) ORDER BY d.name, e.job_title, e.last_name;
In this example, all employees with corresponding departments are retrieved only for departments that have a valid adrs_id in the addresses table. This is a correlated subquery because the subquery references a column in the parent query.
The following example returns all departments and employees except where departments are located in `ROCHESTER' and `NEW YORK':
SELECT d.name, e.first_name, e.last_name, e.job_title
Page 250
FROM emp e, dept d WHERE e.dept_no = d.dept_no AND not exists (SELECT `X' FROM ADDRESSES WHERE city in (`ROCHESTER','NEW YORK') AND adrs_id = d.adrs_id) ORDER BY d.name, e.job_title, e.last_name;
SELECT `X' returns a true or false answer that is evaluated by the not exists operator. Any constant can be used here; `X' is only one example.
The DECODE Statement
One of the most powerful and overlooked SQL statements is the DECODE statement. The DECODE statement has the following syntax:
DECODE(val, exp1, exp2, exp3, exp4, ..., def);
DECODE first evaluates the value or expression val and then compares expression exp1 to val. If val equals exp1, expression exp2 is returned. If val does not equal exp1, expression exp3 is evaluated, and the query returns expression exp4 if val equals exp3. This process continues until all expressions are evaluated. If there are no matches, the default def is returned.
In the following example, all manager names are retrieved with their salaries:
SELECT e.first_name, e.last_name, e.job_title, DECODE(e.job_title, `President', `******', e.salary) FROM emp e WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id) FROM emp z);
When the row identifying the president is displayed, it shows `******' instead of his salary. Also notice the NVL function used to evaluate a null manager ID. Only the president has a null manager ID, which is not retrieved without the NVL.
Also notice that DECODE is evaluating job_title and returning salary, which is usually a datatype mismatch because the job title and salary columns are different datatypes, but it is okay here.
In the next example, if the user is the president, all employees are returned with their corresponding salaries:
SELECT e.first_name, e.last_name, e.job_title, e.salary FROM emp e WHERE DECODE(USER,'PRES',e.emp_id, UPPER(e.last_name),e.emp_id, 0) = e.emp_id ;
Page 251
For all other users, only one row is retrieved, enabling the user to see his or her own salary only as follows :
SELECT e.first_name, e.last_name, e.job_title, DECODE(USER,'ADMIN',DECODE(e.job_title, `PRESIDENT', `*****', e.salary), `PRES', e.salary, `******') FROM emp e WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id) FROM emp z);
In this example, the DECODE statement is nested with another DECODE statement. If the Oracle user is `ADMIN', it shows the salaries except for the president's salary. If the Oracle user is `PRES', it shows all salaries, and if the user is anybody else, it returns `******'.
Another place where you can use the DECODE statement is in the ORDER BY clause. The next example sorts the output in such a way that the president is the first row returned, followed by the departments `SALES', `ADMIN', and then `IS', with their corresponding employees:
SELECT d.name, e.job_title, e.first_name, e.last_name FROM emp e, dept d WHERE d.dept_no = e.dept_no ORDER BY DECODE(e.job_title,'PRESIDENT', 0, DECODE(d.name,'SALES', 1, `ADMIN', 2, 3)), e.last_name;
This example does not use ORDER BY e.job_title but uses this column to search for the title `PRESIDENT' and returns a 0. For all other rows, another DECODE is used to evaluate the department name and return number 1, 2, or 3, depending on the department name. After the DECODEs are finished, the data is further sorted by employee last name e.last_name.
INSERTs, UPDATEs, and DELETEs
The INSERT statement is used to put new rows into the database. You can do this one row at a time using the VALUES expression or a whole set of records at a time using a subquery. The following is the syntax for an INSERT statement:
INSERT INTO schema.table column(s) VALUES subquery
schema is an optional parameter to identify which database schema to use for the insert. The default is your own schema.
table is mandatory and is the name of the table.
column is a list of columns that receive the inserted values.
VALUES is used when one row of data is inserted. Values are represented as constants.
EAN: 2147483647
Pages: 391