Oracle traditional join与ANSI join的区别
一、简介
traditional join：The join syntax was defined in the SQL86 standard,for example (+).
ANSI:American National Standards Institute
ANSI join：The join syntax (involving the JOIN, INNER, OUTER, CROSS, LEFT, RIGHT, FULL, ON, and USING keywords) was introduced in Oracle9i Database TO
make Oracle's join functionality compliant with the ANSI/ISO SQL92 standard known as SQL92,and the latest version is SQL-2003.
二、具体语法的区别
2.1 join(也等同于inner join)
The following example illustrates the older inner join syntax:
SELECT d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id;
The corresponding query with the new syntax is:
SELECT d.name, l.regional_group
FROM department d JOIN location l
ON d.location_id = l.location_id;
Following are the two differences between the old and the new inner join syntax:
The old syntax separates tables in the FROM clause using a comma.
The old syntax specifies the join condition in the WHERE clause.
Since the old syntax uses the WHERE clause to specify the join condition as well as filter conditions,
it may take awhile for you to figure out which component of the WHERE clause is a join condition, and which component is a filter condition.
2.2 left/right join
The following example illustrates the older left join syntax:
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id (+);
DEPT_ID NAME REGIONAL_GROUP
---------- -------------------- ------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES
40 OPERATIONS BOSTON
A corresponding query using the new join syntax is:
SELECT d.dept_id, d.name, l.regional_group
FROM department d LEFT OUTER JOIN location l
ON d.location_id = l.location_id;
In the new outer join syntax, the LEFT (or RIGHT) keyword corresponds to the table from which you want all the rows. This example uses LEFT to point to department, because we want all the rows from the department table irrespective of whether there are coresponding rows in the location table.
Restrictions on Old Outer Join Syntax
There are some rules and restrictions on how you can use the outer join operator in a query. When you use the (+) operator in a query, Oracle doesn't allow you to perform certain other operations in the same query. We discuss these restrictions and some of the workarounds in the following list:
2.2.1 The outer join operator can appear on only one side of an expression in the join condition. You get an ORA-01468 error if you attempt to use it on both sides. For example:
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id (+) = l.location_id(+);
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined TABLE
What this means is that the outer join operation using the (+) operator is unidirectional. You can't perform a bidirectional outer join (known as a full outer join) using the (+) operator.
2.2.2 If a join involves more than two tables, then one table can't be outer joined with more than one other table in the query. Consider the following tables:
DESC employee
Name Null? TYPE
----------------------------------------- -------- --------------
EMP_ID NOT NULL NUMBER(5)
FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
DEPT_ID NOT NULL NUMBER(5)
MANAGER_EMP_ID NUMBER(5)
SALARY NUMBER(5)
HIRE_DATE DATE
JOB_ID NUMBER(3)
DESC job
Name Null? TYPE
------------------------------- -------- ----
JOB_ID NOT NULL NUMBER(3)
FUNCTION VARCHAR2(30)
DESC department
Name Null? TYPE
------------------------------- -------- ----
DEPT_ID NOT NULL NUMBER(5)
NAME VARCHAR2(20)
LOCATION_ID NUMBER(3)
If you want to list the job function and department name of all the employees, and you want to include all the departments and jobs that don't have any corresponding employees, you would probably attempt to join the employee table with the job and department tables, and make both the joins outer joins. However, since one table can't be outer-joined with more than one table you get the following error:
SELECT e.lname, j.function, d.name
FROM employee e, job j, department d
WHERE e.job_id (+) = j.job_id
AND e.dept_id (+) = d.dept_id;
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table
As a workaround, you can create a view with an outer join between two tables, and then outer join the view to the third table:
CREATE VIEW v_emp_job AS
SELECT e.dept_id, e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) = j.job_id;
SELECT v.lname, v.function, d.name
FROM v_emp_job v, department d
WHERE v.dept_id (+) = d.dept_id;
Instead of creating a view, you can use an inline view to achieve the same result:
SELECT v.lname, v.function, d.name
FROM (SELECT e.dept_id, e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) = j.job_id) v, department d
WHERE v.dept_id (+) = d.dept_id;
2.2.3 A condition containing the (+) operator may not use the IN operator. For example:
SELECT e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) IN (668, 670, 667);
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
2.2.4 An outer join condition containing the (+) operator may not be combined with another condition using the OR operator. For example:
SELECT e.lname, d.name
FROM employee e, department d
WHERE e.dept_id = d.dept_id (+)OR d.dept_id = 10;
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
2.2.5 A condition containing the (+) operator may not involve a subquery. For example:
SELECT e.lname
FROM employee e
WHERE e.dept_id (+) =
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING');
ERROR at line 4:
ORA-01799: a column may not be outer-joined to a subquery
As a workaround, you can use an inline view to achieve the desired effect:
SELECT e.lname
FROM employee e,
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING') V
WHERE e.dept_id (+) = v.dept_id;
2.3 full JOIN
The following example illustrates the older full join syntax:
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id (+) = l.location_id
UNION
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id (+) ;
DEPT_ID NAME REGIONAL_GROUP
---------- -------------------- ----------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES
40 OPERATIONS BOSTON
CHICAGO
SAN FRANCISCO
6 rows selected.
A corresponding query using the new join syntax is:
SELECT d.dept_id, d.name, l.regional_group
FROM department d full join location l
WHERE d.location_id = l.location_id
2.4 Cross Joins/Cartesian Products Cross joins are joins without a join condition. Each row of one table is combined with each row of another table. The result is referred to as a Cartesian product.
The following example illustrates the older cross join syntax:
SELECT e.lname, d.name
FROM employee e,department d;
The corresponding query with the new syntax is:
SELECT e.lname, d.name
FROM employee e CROSS JOIN department d;
cross joins are joins without join conditions
2.5 Partition Outer Joins Partition outer joins are an interesting new feature in Oracle Database 10g. They're useful for data densification, which is a fancy way of saying that they are useful for filling in rows that do not exist. This is a common requirement for data warehousing queries.
SELECT * FROM employee_expense;
EMP_ID YEAR MONTH EXPENSE_CLAIM APPROVED_AMT PAID_DATE
---------- ---------- ---------- ------------- ------------ ---------
7369 2002 2 3072.43 3072.43 03-MAR-02
7369 2002 4 30 30 01-JUN-02
7369 2002 5 235.03 35.03 01-JUN-02
7369 2002 9 5095.98 5095.08 31-OCT-02
7369 2002 12 1001.01 1001.01 01-FEB-03
7782 2002 1 111.09 111.09 01-FEB-02
7782 2002 3 9.85 9.85 01-APR-02
7782 2002 7 3987.32 3987.32 01-AUG-02
7782 2002 9 1200 1200 01-OCT-02
SELECT * FROM MONTHS WHERE year = 2002;
YEAR MONTH
---------- ----------
2002 1
2002 2
2002 3
2002 4
2002 5
2002 6
2002 7
2002 8
2002 9
2002 10
2002 11
2002 12
Management wants to review expenses, and you've been asked to generate a data extract of employee expense claims for the year 2002. Furthermore, to facilitate some analysis that management wishes to do using Excel, you've been asked to ensure that your extract contains one row per month per employee, but only for those employees who actively submit expense reports. Most do not.
You can use this months table in an outer join to generate 12 rows for each employee. For example, to generate one row per month for employee 7782, you can write the following query:
SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN (SELECT *
FROM employee_expense
WHERE emp_id = 7782) ee
ON m.year = ee.year AND m.month = ee.month
ORDER BY m.month;
NVL(EE.EMP_ID,7782) YEAR MONTH NVL(EE.EXPENSE_CLAIM,0)
------------------- ---------- ---------- -----------------------
7782 2002 1 111.09
7782 2002 2 0
7782 2002 3 9.85
7782 2002 4 0
7782 2002 5 0
7782 2002 6 0
7782 2002 7 3987.32
7782 2002 8 0
7782 2002 9 1200
7782 2002 10 0
7782 2002 11 0
7782 2002 12 0
The previous query is all well and good, but to generate your report you'd need to execute the preceding query many times, once for each employee who has submitted at least one expense report in the year 2002. This is where partition outer joins come into play. They make it trivial to do the equivalent of executing the preceding query once per employee. Here's how:
SELECT ee.emp_id, m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN employee_expense ee
PARTITION BY (ee.emp_id)
ON m.year = ee.year AND m.month = ee.month
ORDER BY ee.emp_id, m.month;
EMP_ID YEAR MONTH NVL(EE.EXPENSE_CLAIM,0)
---------- ---------- ---------- -----------------------
7369 2002 1 0
7369 2002 2 3072.43
7369 2002 3 0
7369 2002 4 30
7369 2002 5 235.03
7369 2002 6 0
7369 2002 7 0
7369 2002 8 0
7369 2002 9 5095.98
7369 2002 10 0
7369 2002 11 0
7369 2002 12 1001.01
7782 2002 1 111.09
7782 2002 2 0
7782 2002 3 9.85
7782 2002 4 0
7782 2002 5 0
7782 2002 6 0
7782 2002 7 3987.32
7782 2002 8 0
7782 2002 9 1200
7782 2002 10 0
7782 2002 11 0
7782 2002 12 0
Notice the PARTITION BY clause in this query. That clause is new in Oracle Database 10g, and in this example it causes the database engine to conceptually perform the following steps:
1.Divide the rows from employee_expense into groups based on their emp_id values, one group per value.
2.Outer join each group to the months table as a separate operation.
Because they make it easy to fill in gaps in your data, partition outer joins are particularly helpful when writing lag and lead queries, which are a type of query particularly sensitive to gaps in data.
三、The USING Clause If these two conditions are satisfied, you can apply the USING clause to specify the join condition. Earlier, you saw the following example of a join query:
1.The join depends on an equality condition between two columns, or between sets of two columns, to relate the rows from the two tables.
2.The names of the join columns are identical in both the tables.
SELECT department.location_id, department.name, location.regional_group
FROM department JOIN location
ON department.location_id = location.location_id;
The column involved in the join condition (location_id) is named identically in both the tables, and its value must be the same in both tables. Therefore, this join query can be rewritten as:
SELECT location_id, department.name, location.regional_group
FROM department JOIN location
USING (location_id);
The USING clause affects the semantics of the SELECT clause. The USING clause tells Oracle that the tables in the join have identical names for the column in the
USING clause. Oracle then merges those two columns, and recognizes only one such column with the given name. If you include a join column in the SELECT list, Oracle doesn't allow you to qualify that column with a table name (or table alias). If you attempt to qualify a join column name in the SELECT list using either an alias or a table name, you will get an error:
SELECT department.location_id, department.name, location.regional_group
FROM department JOIN location
USING (location_id);
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
This is why our USING query did not alias the location_id column in the SELECT list.
If the column names are identical in the two tables, you can use the USING clause and specify all the columns in one USING clause, separated by commas. The
previous join condition can be rewritten as:
SELECT . . . FROM A JOIN B USING (c1, c2, c3....);
四、The Natural Join Clause
A natural join between two tables relates the rows from the two tables based on all pairs of columns, one column from each table, with matching names. You don't specify a join condition. The following example illustrates a natural join:
SELECT department.name, location.regional_group
FROM department NATURAL JOIN location;
NAME REGIONAL_GROUP
-------------------- ---------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
OPERATIONS BOSTON
In this example, the two tables—department and location—have the same name for the column location_id. Therefore, the join takes place by equating the location_id from the department table to the location_id from the location table. The preceding query is equivalent to the following queries:
SELECT department.name, location.regional_group
FROM department JOIN location
ON department.location_id = location.location_id;
SELECT department.name, location.regional_group
FROM department JOIN location
USING (location_id);
While using a natural join, you are not allowed to qualify the common columns with table names or aliases (similar to the effect of the USING clause). For example, if you want to include the location_id column in the SELECT list, and you specify department.location_id, you will get an error:
SELECT department.location_id, department.name, location.regional_group
FROM department NATURAL JOIN location;
SELECT department.location_id, department.name, location.regional_group
ORA-25155: column used in NATURAL join cannot have qualifier
You need to remove the department qualifier so the location_id column can include it in the SELECT list:
SELECT location_id, department.name, location.regional_group
FROM department NATURAL JOIN location;
LOCATION_ID NAME REGIONAL_GROUP
----------- -------------------- ---------------
122 ACCOUNTING NEW YORK
124 RESEARCH DALLAS
167 OPERATIONS BOSTON
|
转播
