|
Running sqls
on various database and Standardizing the sql’s will be the
best option in Development of Application. We can make the sql’s are
standardizes using ANSI/ISO SQL:1999:
The various
sql’s joins available herein below:
1. SQL:1999 joins
2. CASE expressions
3. Explicit defaults
4. WITH clause
1.
SQL:1999 Joins
SQL:1999
join syntax differs from traditional Oracle join syntax in that the join type is
specified explicitly in the FROM clause.
Oracle9i
onwards the following SQL:1999-compliant joins are available:
1.1 NATURAL Join
1.2 CROSS Join
1.3 OUTER Join
1.3.1 LEFT OUTER Join
1.3.2 RIGHT OUTER Join
1.3.3 FULL OUTER Join
1.1 NATURAL Join
A
NATURAL join selects rows from the tables which have equal values in all matched
columns(same column names). If the columns having the same names have different
datatypes, an error is returned.
If
SELECT * syntax is used, the common columns appear only once in the result set.
Qualifiers such as table names or aliases may not be used for those columns
involved in the natural join.
Example:
For this example, assume tables empand departments have
one common
column, department_id.
SELECT employee_id, last_name, department_id
FROM emp
NATURAL JOIN departments;
This
is equivalent to the following:
SELECT employee_id, last_name, departments.department_id
FROM employees, departments
WHERE employees.department_id =
departments.department_id;
If
several columns have the same names but all of the datatypes do not match,
then the NATURAL JOIN can be modified to a join with a USING clause which
specifies which columns should be used for the equi-join.
The
columns in the USING clause should also not have a qualifier (table name or
alias) anywhere in the SQL statement.
The
key words NATURAL and USING are mutually exclusive.
Example:
Assume tables empand job_history have two columns in common,
employee_id and department_id. The following query joins the two tables
together using only the employee_id as a join condition.
SELECT employee_id, last_name, job_history.department_id
FROM emp
JOIN job_history
USING (employee_id);
This
is equivalent to the following:
SELECT employees.employee_id, last_name,
job_history.department_id
FROM employees, job_history
WHERE employees.employee_id=job_history.employee_id;
To
specify arbitrary conditions or specify columns to be used in the join, the
ON clause is used. This also separates the join condition from other
conditions. It can also be used to specify a join for which the columns have
different names. Another use for the ON clause is simply to
make the SQL easier to read
and
understand by explicitly stating the join condition.
Examples:
The following is equivalent to the example above for the
USING clause.
SELECT employees.employee_id, last_name,
job_history.department_id
FROM emp
JOIN job_history
ON (employees.employee_id=job_history.employee_id);
This
example adds an additional where clause to the query:
SELECT employees.employee_id, last_name,
job_history.department_id
FROM emp
JOIN job_history
ON (employees.employee_id=job_history.employee_id)
WHERE mgrid=3;
The
following illustrates a self-join of the emptable to itself based on the
employee_id and manager_id columns:
SELECT e.last_name employee, m.last_name manager
FROM empe
JOIN empm
ON (e.manager_id=m.employee_id);
1.1 CROSS Join
A CROSS join is the cross-product of two tables. It is
the equivalent of a Cartesian product.
Example:
SELECT last_name, department_name
FROM emp
CROSS JOIN departments;
This is equivalent to the following:
SELECT last_name, department_name
FROM employees, departments;
1.3 OUTER Join
The
join of two tables returning only matched rows is considered an INNER JOIN.
A
join between two tables which returns the result of the INNER join as well as
unmatched rows from the LEFT(or RIGHT) table is a LEFT(or RIGHT) OUTER join.
INNER
and OUTER JOINS were available in prior relaeses. The '(+)' symbol was used on
one of the tables to create outer joins.
1.3.1 LEFT OUTER Join
A
join between two tables which returns the result of the INNER join as well as
unmatched rows from the LEFT table is a LEFT OUTER join.
Example:
This query returns rows from the departments table even
if no empare assigned to it.
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN empe
ON (e.department_id= d.department_id);
This
is equivalent to the following pre-Oracle9i outer join notation:
SELECT employee_id, department_name
FROM departments d, empe
WHERE e.department_id(+) = d.department_id;
1.3.2 RIGHT OUTER Join
A
join between two tables which returns the result of the INNER join as well as
unmatched rows from the RIGHT table is a RIGHT OUTER join.
Example:
This query returns rows from the customers table even if
this customer has not placed any orders.
SELECT cust_last_name, order_id,order_date
FROM orders o
RIGHT OUTER JOIN customers c
ON (o.customer_id = c.customer_id);
This is equivalent to the following pre-Oracle9i outer
join notation:
SELECT cust_last_name, order_id, order_date
FROM orders o, customers c
WHERE c.customer_id = o.customer_id(+);
1.3.3 FULL OUTER Join
A
join between two tables which returns the result of the INNER join as well as
unmatched rows from the LEFT and RIGHT outer joins is a FULL OUTER join. This
is a new type of join in Oracle9i which did not exist previously.
Example:
This
query returns locations which have no corresponding country and
countries which have no locations assigned.
SELECT l.city, c.country_name
FROM locations l
FULL OUTER JOIN countries c
ON (l.country_id = c.country_id);
This
could have been accomplished in earlier versions using a
UNION:
SELECT l.city, c.country_name
FROM locations l, countries c
WHERE l.country_id = c.country_id(+)
UNION
SELECT l.city, c.country_name
FROM locations l, countries c
WHERE l.country_id(+) = c.country_id;
Restrictions
Use
of these new joins is allowed in the definition of materialized views. However,
query rewrite is only possible if the query could also be expressed using
traditional Oracle syntax. The same restrictions apply when determining whether
the materialized view can be fast refreshed.
For
example, the following materialized view can make use of query rewrite:
CREATE MATERIALIZED VIEW mv1 AS
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN empe
ON (e.department_id= d.department_id);
But,
this query cannot use query rewrite because the use of subqueries was not
permitted with outer joins:
CREATE MATERIALIZED VIEW mv2 AS
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN empe
ON (e.department_id= d.department_id)
WHERE e.department_id > 10;
The
columns that are referenced in the USING or ON clause cannot be a
collection or LOB type.
2.
CASE statements
There
are 4 types of CASE logic available in SQL:1999:
2.1 Simple CASE
2.2 Searched CASE
2.3 NULLIF
2.4 COALESCE
2.1
Simple CASE
The
simple CASE is similar to DECODE. It was available in Oracle 8.1.7. It can
be used to search and replace values within a given expression. You can
specify a return value for each searched value. No comparison operators can be
used in the simple CASE.
Example:
Assume the emptable has a salgrade column.
SELECT employee_id,
CASE salgrade WHEN 'A' THEN 'Low'
WHEN 'B' THEN 'Medium'
WHEN 'C' THEN 'High' END "Salary Grade"
FROM employees;
It is
also possible to have an ELSE clause for a default.
The
above query is equivalent to the following with DECODE:
SELECT employee_id,
DECODE(salgrade,'A','Low','B','Medium','C','High')
FROM employees;
2.2
Searched CASE
The
searched CASE is similar to IF..THEN..ELSE. Searched CASE was available
beginning in version 8.1.6.
Each
WHEN clause can be different and logical operators can be used to combine
multiple conditions. Comparison operators are also allowed.
Example:
SELECT employee_id,
CASE
WHEN salary >=75000 THEN 'High'
WHEN salary <=30000 THEN 'Low'
ELSE 'Medium'
END "Salary"
FROM employees;
2.3
NULLIF
NULLIF
returns NULL if the first argument is equal to the second. Otherwise,
the first value of the first argument is returned.
Example:
SELECT employee_id,
NULLIF(commission_pct,.1) "new commission"
FROM employees;
2.4
COALESCE
COALESCE returns the first argument (beginning on the left) that is NOT NULL.
It is
similar to the NVL function, but it can take multiple alternate values.
It
accepts any number of arguments.
Example:
SELECT employee_id,
COALESCE(to_char(commission_pct),'none') commission
FROM employees;
3.
Explicit Defaults
If a
column has a default value defined, the explicit default allows the DEFAULT
keyword to be used in an INSERT or UPDATE statement. It can also be
used in bind variables. The use of the explicit default eliminates any
ambiguity of which values will be placed in a column and avoids
hard-coding of literals in applications.
Benefits of EXPLICIT DEFAULTS:
* provides better data integrity
* avoids the use of hard-coded litterals in applications
* more user-friendly and provide a more flexible
interface
Examples:
Assume the employee table has a default defined for the
department_id
column.
INSERT INTO emp
(employee_id, first_name, last_name, department_id)
VALUES
(1, 'Tom', 'Smith', DEFAULT);
UPDATE emp
SET department_id = DEFAULT
WHERE department_id=20;
4.
WITH clause
The
WITH clause allows a query block to be assigned a name and used multiple times
in a query by referring to this name. This is very useful to reduce the cost of
a query block which will need to be evaluated more than once in a query by
materializing the query block.
Here
is an example of a query which benefits from a WITH clause:
SELECT department_name, SUM(salary) dept_total
FROM employees, departments
WHERE employees.department_id=departments.department_id
GROUP BY department_name HAVING
SUM(salary) > (
SELECT sum(salary) * 1/3
FROM employees, departments
WHERE employees.department_id=departments.department_id
)
ORDER BY dept_total;
This
query can be rewritten as follows:
WITH summary AS (
SELECT department_name, SUM(salary) dept_total
FROM employees, departments
WHERE employees.department_id=departments.department_id
GROUP BY department_name
)
SELECT department_name, dept_total
FROM summary
WHERE dept_total > (
SELECT SUM(dept_total) * 1/3
FROM summary
)
ORDER BY dept_total DESC;
By
using the WITH clause, summarizing the department total more than once is
avoided.
Another example:
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM empe, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;
| 
