JOIN
A join is a SQLquery that extracts information from two or more tables or views.
When you specify multiple tables or views in the FROM clause of a query, Oracle
performs a join, linking rows from multiple tables together. There are several types of
JOINS IN ORACLE-different joins in oracle with examples
1. The purpose of a join is to combine the data across tables.
2. A join is actually performed by the where clause which combines the specified rows of tables.
3. If a join involves in more than two tables then oracle joins first two tables based on the joins
condition and then compares the result with the next table and so on.
TYPES
1 Equi join
2 Non-equi join
3 Self join
4 Natural join
5 Cross join
6 Outer join
Left outer
Right outer
Full outer
7 Inner join
8 Using clause
9 On clause
Cartesian Product
If you don’t specify the join condition while joining two tables, Oracle combines each
row from the first table with each row of the second table. This type of result set is
called as a Cartesian product. The number of rows in a Cartesian product is the product
of the number of rows in each table. Here’s an
example of a Cartesian product
SQL> SELECT Ename, Sal, Job, Dname, Loc
FROM Emp, Dept;
FORD 3000 ANALYST SALES CHICAGO
SMITH 800 CLERK SALES CHICAGO
SCOTT 3000 ANALYST SALES CHICAGO
ADAMS 1100 CLERK SALES CHICAGO
MILLER 1300 CLERK SALES CHICAGO
KING 5000 PRESIDENT OPERATIONS BOSTON
BLAKE 2850 MANAGER OPERATIONS BOSTON
ENAME SAL JOB DNAME LOC
CLARK 2450 MANAGER OPERATIONS BOSTON
JONES 2975 MANAGER OPERATIONS BOSTON
MARTIN 1250 SALESMAN OPERATIONS BOSTON
ALLEN 1600 SALESMAN OPERATIONS BOSTON
TURNER 1500 SALESMAN OPERATIONS BOSTON
JAMES 950 CLERK OPERATIONS BOSTON
WARD 1250 SALESMAN OPERATIONS BOSTON
FORD 3000 ANALYST OPERATIONS BOSTON
SMITH 800 CLERK OPERATIONS BOSTON
SCOTT 3000 ANALYST OPERATIONS BOSTON
ADAMS 1100 CLERK OPERATIONS BOSTON
ENAME SAL JOB DNAME LOC
MILLER 1300 CLERK OPERATIONS BOSTON
56 rows selected.
SQL> ED
Wrote file afiedt.buf
SQL> SELECT Dname, Loc
FROM Emp, Dept
SQL> /
DNAME LOC
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
DNAME LOC
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
DNAME LOC
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
DNAME LOC
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS BOSTON
DNAME LOC
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
DNAME LOC
OPERATIONS BOSTON
56 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Deptno, Dname, Loc 2* FROM
Emp, Dept
SQL> /
SELECT Ename, Job, Deptno, Dname, Loc
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Emp.Deptno, Dname, Loc 2* FROM
Emp, Dept
SQL> /
ENAME JOB
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
WARD SALESMAN
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
FORD ANALYST
SMITH CLERK
ENAME JOB
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
ENAME JOB
WARD SALESMAN
FORD ANALYST
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ENAME JOB
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
WARD SALESMAN
FORD ANALYST
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
KING PRESIDENT
BLAKE MANAGER
ENAME JOB
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
WARD SALESMAN
FORD ANALYST
SMITH CLERK
20 ACCOUNTING NEW YORK
20 ACCOUNTING NEW YORK
DEPTNO DNAME LOC
20 ACCOUNTING NEW YORK
20 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 RESEARCH DALLAS
30 RESEARCH DALLAS
10 RESEARCH DALLAS
20 RESEARCH DALLAS
30 RESEARCH DALLAS
30 RESEARCH DALLAS
30 RESEARCH DALLAS
30 RESEARCH DALLAS
DEPTNO DNAME LOC
30 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
10 RESEARCH DALLAS
10 SALES CHICAGO
30 SALES CHICAGO
10 SALES CHICAGO
20 SALES CHICAGO
30 SALES CHICAGO
DEPTNO DNAME LOC
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
20 SALES CHICAGO
20 SALES CHICAGO
20 SALES CHICAGO
20 SALES CHICAGO
10 SALES CHICAGO
10 OPERATIONS BOSTON
30 OPERATIONS BOSTON
DEPTNO DNAME LOC
10 OPERATIONS BOSTON
20 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
20 OPERATIONS BOSTON
20 OPERATIONS BOSTON
SCOTT ANALYST
ADAMS CLERK
ENAME JOB
MILLER CLERK
56 rows selected.
Inner Joins OR UQUE JOIN
An inner join returns the rows that satisfy the join condition. Let’s take an example
to understand the concept of a join. Say you want to list the name and department
name for each employee. To do this, you would use the following SQL statement:
SQL> SELECT Ename, Emp.Deptno, Dname, Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
BLAKE 30 SALES CHICAGO
CLARK 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
FORD 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
ENAME DEPTNO DNAME LOC
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
MILLER 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> cl scr
SQL> Select
2 Empno,
3 Ename,
4 Sal,
5 Sal * 12 AnnSal,
6 Emp.Deptno,
7 Loc
8 FROM Emp, Dept
9 WHERE Emp.Deptno = Dept.Deptno;
EMPNO ENAME SAL ANNSAL DEPTNO LOC
7839 KING 5000 60000 10 NEW YORK
7782 CLARK 2450 29400 10 NEW YORK
7934 MILLER 1300 15600 10 NEW YORK
7566 JONES 2975 35700 20 DALLAS
7788 SCOTT 3000 36000 20 DALLAS
7876 ADAMS 1100 13200 20 DALLAS
7369 SMITH 800 9600 20 DALLAS
7902 FORD 3000 36000 20 DALLAS
7698 BLAKE 2850 34200 30 CHICAGO
7654 MARTIN 1250 15000 30 CHICAGO
7499 ALLEN 1600 19200 30 CHICAGO
EMPNO ENAME SAL ANNSAL DEPTNO LOC
7844 TURNER 1500 18000 30 CHICAGO
7900 JAMES 950 11400 30 CHICAGO
7521 WARD 1250 15000 30 CHICAGO
14 rows selected.
SQL> cl scr
SQL> Select
Dept.Deptno,
Dname,
Loc,
SUM(Sal)
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno
GROUP BY Dept.Deptno, Dname, Loc;
DEPTNO DNAME LOC SUM(SAL)
10 ACCOUNTING NEW YORK 8750
20 RESEARCH DALLAS 10875
30 SALES CHICAGO 9400
SQL> SELECT Ename
FROM Emp E1, Emp E2
SQL> /
SELECT Ename
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> ED
Wrote file afiedt.buf
SELECT E1.Ename
FROM Emp E1, Emp E2
SQL> /
SQL> cl scr
SQL> SELECT
E.Empno,
E.Ename,
D.Deptno,
D.Dname
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno;
EMPNO ENAME DEPTNO DNAME
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7902 FORD 20 RESEARCH
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7499 ALLEN 30 SALES
EMPNO ENAME DEPTNO DNAME
7844 TURNER 30 SALES
7900 JAMES 30 SALES
7521 WARD 30 SALES
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
E.Empno,
E.Ename,
Dept.Deptno,
D.Dname
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno
SQL> /
Dept.Deptno,
*
ERROR at line 4:
ORA-00904: "DEPT"."DEPTNO": invalid identifier
SQL> SELECT
E.Ename,
E.Job,
D.Deptno,
D.Dname,
D.Loc
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno AND
E.Job IN('ANALYST', 'MANAGER' );
ENAME JOB DEPTNO DNAME LOC
CLARK MANAGER 10 ACCOUNTING NEW YORK
JONES MANAGER 20 RESEARCH DALLAS
FORD ANALYST 20 RESEARCH DALLAS
SCOTT ANALYST 20 RESEARCH DALLAS
BLAKE MANAGER 30 SALES CHICAGO
SQL> cl scr
SQL> SELECT
Ename,
DName,
Loc
FROM Emp Employees, Dept Departments
WHERE Employees.Deptno = Departments.Deptno;
ENAME DNAME LOC
KING ACCOUNTING NEW YORK
CLARK ACCOUNTING NEW YORK
MILLER ACCOUNTING NEW YORK
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
ADAMS RESEARCH DALLAS
SMITH RESEARCH DALLAS
FORD RESEARCH DALLAS
BLAKE SALES CHICAGO
MARTIN SALES CHICAGO
ALLEN SALES CHICAGO
ENAME DNAME LOC
TURNER SALES CHICAGO
JAMES SALES CHICAGO
WARD SALES CHICAGO
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
Ename,
DName,
Loc
FROM Emp E, Dept D
* WHERE E.Deptno = D.Deptno
SQL> /
ENAME DNAME LOC
KING ACCOUNTING NEW YORK
CLARK ACCOUNTING NEW YORK
MILLER ACCOUNTING NEW YORK
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
ADAMS RESEARCH DALLAS
SMITH RESEARCH DALLAS
FORD RESEARCH DALLAS
BLAKE SALES CHICAGO
MARTIN SALES CHICAGO
ALLEN SALES CHICAGO
ENAME DNAME LOC
TURNER SALES CHICAGO
JAMES SALES CHICAGO
WARD SALES CHICAGO
14 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, MGR
FROM Emp;
EMPNO ENAME MGR
7839 KING
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7654 MARTIN 7698
7499 ALLEN 7698
7844 TURNER 7698
7900 JAMES 7698
7521 WARD 7698
7902 FORD 7566
7369 SMITH 7902
EMPNO ENAME MGR
7788 SCOTT 7566
7876 ADAMS 7788
7934 MILLER 7782
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Empno, Ename, Sal, MGR
FROM Emp
SQL> /
EMPNO ENAME SAL MGR
7839 KING 5000
7698 BLAKE 2850 7839
7782 CLARK 2450 7839
7566 JONES 2975 7839
7654 MARTIN 1250 7698
7499 ALLEN 1600 7698
7844 TURNER 1500 7698
7900 JAMES 950 7698
7521 WARD 1250 7698
7902 FORD 3000 7566
7369 SMITH 800 7902
EMPNO ENAME SAL MGR
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
7934 MILLER 1300 7782
14 rows selected.
SQL> cl scr
A SELF JOIN is a query in which a table is joined (compared) to itself. Self-joins are useful to compare values in a specific
column with other values in the same column in the same table (related data that is stored in a single table). The self join can be
done by using table aliases to treat one table as though it were a different table and then joining them together. Another common
use for self-joins is obtaining running counts and totals in a query.
The general rule for writing self-joins is:
Write the query doing the selects from the same table listed twice with different aliases
Set up the comparison pedicates or tests
Remove any cases where a given value would be equal to itself.
SQL> SELECT
E1.Ename "Employees",
E2. Ename "Managers"
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno;
Employees Managers
FORD JONES
SCOTT JONES
MARTIN BLAKE
ALLEN BLAKE
JAMES BLAKE
TURNER BLAKE
WARD BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
CLARK KING
Employees Managers
JONES KING
SMITH FORD
13 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
E1.Ename,
E2. Ename
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno
SQL> /
ENAME ENAME
FORD JONES
SCOTT JONES
MARTIN BLAKE
ALLEN BLAKE
JAMES BLAKE
TURNER BLAKE
WARD BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
CLARK KING
ENAME ENAME
JONES KING
SMITH FORD
13 rows selected.
SQL> cl scr
SQL> SELECT
Employees.Ename "Employees",
Managers.Ename "Managers"
FROM Emp Employees, Emp Managers
WHERE Employees.Mgr = Managers.Empno;
Employees Managers
FORD JONES
SCOTT JONES
MARTIN BLAKE
ALLEN BLAKE
JAMES BLAKE
TURNER BLAKE
WARD BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
CLARK KING
Employees Managers
JONES KING
SMITH FORD
13 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
Employees.Ename "Employees",
Managers.Ename "Managers"
FROM Emp Employees, Emp Managers
WHERE Managers.Mgr = Employees.Empno SQL>
/
Employees Managers
JONES FORD
JONES SCOTT
BLAKE MARTIN
BLAKE ALLEN
BLAKE JAMES
BLAKE TURNER
BLAKE WARD
CLARK MILLER
SCOTT ADAMS
KING BLAKE
KING CLARK
Employees Managers
KING JONES
FORD SMITH
13 rows selected.
SQL> cl scr
SQL> SELECT
E1.Ename||'''s Manager is '||
E2.Ename "Employees And Managers"
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno;
Employees And Managers
FORD's Manager is JONES
SCOTT's Manager is JONES
MARTIN's Manager is BLAKE
ALLEN's Manager is BLAKE
JAMES's Manager is BLAKE
TURNER's Manager is BLAKE
WARD's Manager is BLAKE
MILLER's Manager is CLARK
ADAMS's Manager is SCOTT
BLAKE's Manager is KING
CLARK's Manager is KING
Employees And Managers
JONES's Manager is KING
SMITH's Manager is FORD
13 rows selected.
SQL> SPOOL OFF
SQL> cl scr
Equi-Join Versus Non-Equi-Join
The join condition determines whether the join is an equi-join or a non-equi-join.
When a join condition relates two tables by equating the columns from the tables, it is
an equi-join.When a join condition relates two tables by an operator other than equality,
it is a non-equi-join. A query may contain equi-joins as well as
non-equi-joins
SQL> SELECT Empno, Ename, Sal
FROM Emp;
EMPNO ENAME SAL
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
7499 ALLEN 1600
7844 TURNER 1500
7900 JAMES 950
7521 WARD 1250
7902 FORD 3000
7369 SMITH 800
EMPNO ENAME SAL
7788 SCOTT 3000
7876 ADAMS 1100
7934 MILLER 1300
14 rows selected.
SQL> SELECT LoSal, HiSal, Grade
FROM DalGrade;
FROM DalGrade
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> Ed
Wrote file afiedt.buf
SELECT LoSal, HiSal, Grade
FROM SalGrade
SQL> /
LOSAL HISAL GRADE
700 1200 1
1201 1400 2
1401 2000 3
2001 3000 4
3001 9999 5
SQL> SELECT Empno, Ename, Sal
FROM Emp;
EMPNO ENAME SAL
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
7499 ALLEN 1600
7844 TURNER 1500
7900 JAMES 950
7521 WARD 1250
7902 FORD 3000
7369 SMITH 800
EMPNO ENAME SAL
7788 SCOTT 3000
7876 ADAMS 1100
7934 MILLER 1300
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Emp.Deptno, Dname, Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
BLAKE 30 SALES CHICAGO
CLARK 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
FORD 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
ENAME DEPTNO DNAME LOC
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
MILLER 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Emp.Deptno, Dname, Loc 2 FROM
Emp, Dept
WHERE Emp.Deptno > Dept.Deptno
SQL> /
ENAME DEPTNO DNAME LOC
JONES 20 ACCOUNTING NEW YORK
SCOTT 20 ACCOUNTING NEW YORK
ADAMS 20 ACCOUNTING NEW YORK
SMITH 20 ACCOUNTING NEW YORK
FORD 20 ACCOUNTING NEW YORK
BLAKE 30 ACCOUNTING NEW YORK
MARTIN 30 ACCOUNTING NEW YORK
ALLEN 30 ACCOUNTING NEW YORK
TURNER 30 ACCOUNTING NEW YORK
JAMES 30 ACCOUNTING NEW YORK
WARD 30 ACCOUNTING NEW YORK
ENAME DEPTNO DNAME LOC
BLAKE 30 RESEARCH DALLAS
MARTIN 30 RESEARCH DALLAS
ALLEN 30 RESEARCH DALLAS
TURNER 30 RESEARCH DALLAS
JAMES 30 RESEARCH DALLAS
WARD 30 RESEARCH DALLAS
17 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Sal, Emp.Deptno Deptno, Dname, Loc 2 FROM
Emp, Dept
WHERE Emp.Deptno(+) = Dept.Deptno
SQL> /
ENAME SAL DEPTNO DNAME LOC
KING 5000 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
MILLER 1300 10 ACCOUNTING NEW YORK
JONES 2975 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
BLAKE 2850 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
ENAME SAL DEPTNO DNAME LOC
TURNER 1500 30 SALES CHICAGO
JAMES 950 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
OPERATIONS BOSTON
SHIPPING CHENNAI
CARGO MUMBAI
COURIER DELHI
18 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Sal, Dept.Deptno Deptno, Dname, Loc 2 FROM
Emp, Dept
WHERE Emp.Deptno = Dept.Deptno(+)
SQL> /
ENAME SAL DEPTNO DNAME LOC
MILLER 1300 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
KING 5000 10 ACCOUNTING NEW YORK
ADAMS 1100 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
WARD 1250 30 SALES CHICAGO
JAMES 950 30 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
ENAME SAL DEPTNO DNAME LOC
ALLEN 1600 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
14 rows selected.
ANSI Join Syntax
Starting with Oracle 9i, Oracle implemented a number of additions to SQL to conform to many aspects of the ANSI
SQL/92 and SQL:1999 standards. The advantage of the ANSI join syntax over the traditional comma-separated tables FROM
clause is that SQL queries can run unmodified against other non-Oracle, ANSI-compliant databases
SQL JOIN Implementations:
A CROSS JOIN implements a Cartesian Product, which returns rows combined from both tables. It matches every row in
one table against every row in another table. For example, if you have 3 rows in one table and 2 rows in another you’ll
have 2 times 3, or 6, rows in the result set.
An INNER JOIN implements an equijoin, which returns rows combined from both tables when the values in the join
condition match. Join conditions can include one or more columns.
A NATURAL JOIN implements an equijoin, which returns rows combined from both tables when the values in the join
condition match. While the join conditions can include one or more columns, you have no choice in selecting the join
columns. A NATURAL JOIN checks the definition of both tables (in the data catalog or metadata) and looks for like named
columns. It then joins the table based on the values of all like named columns from both tables. One might say it’s an
unnatural behavior because a developer has no way to override it.
A LEFT JOIN implements an equijoin and relative complement, which returns rows combined from both tables when the
values in the join condition match and the rows in the left table that aren’t found in the right table. This makes a LEFT
JOIN a combination of an INNER JOIN and a relative complement of the table on the right of the join. This nuance
becomes important when we examine the (+) semantic implemented by Oracle.
A RIGHT JOIN implements an equijoin and relative complement, which returns rows combined from both tables when the
values in the join condition match and the rows in the right table that aren’t found in the left table. This makes a RIGHT
JOIN the opposite of a LEFT JOIN. Naturally, it is also a combination of an INNER JOIN and a relative complement of the
left table. Like the LEFT JOIN, this nuance becomes important when we examine the (+) semantic implemented by
Oracle.
A FULL JOIN implements an equijoin and both relative complements, which returns rows combined from both tables
when the values in the join condition match and the rows in both the left and right tables that aren’t found in their
respective other tables. This makes a FULL JOIN a combination of an INNER JOIN and relative complements of the both
tables. This type of join can’t be done with the (+) alone in the older syntax. It requires left and right outer queries glued
together by a UNION set operator. The UNION set operator eliminates duplicate from the intersection of the two tables,
which are created by gluing together two outer joins.
An Antijoin implements an outer join minus an equijoin, which returns a relative complement when the first query is a
LEFT JOIN or RIGHT JOIN and a symmetric difference when the first query is a FULL JOIN.
EX---SQL> select * from t1;
ID
----------
1
2
3
4
SQL> select * from t2;
ID
----------
3
4
5
6
-- LEFT OUTER JOIN
SQL> select t1.id, t2.id
2 from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);
ID ID
---------- ----------
3 3
4 4
1
2
-- RIGHT OUTER JOIN
SQL> select t1.id, t2.id
2 from t1 RIGHT OUTER JOIN t2 ON (t1.id = t2.id);
ID ID
---------- ----------
3 3
4 4
6
5
-- LEFT JOIN (SAME AS LEFT OUTER JOIN)
SQL> ed
1 select t1.id, t2.id
2* from t1 LEFT JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
1
2
-- RIGHT JOIN (SAME AS RIGHT OUTER JOIN)
SQL> ed
Wrote file afiedt.buf
1 select t1.id, t2.id
2* from t1 RIGHT JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
6
5
-- INNER JOIN (REGULAR JOIN)
SQL> ed
Wrote file afiedt.buf
select t1.id, t2.id
from t1 INNER JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
-- FULL OUTER JOIN
SQL> ed
Wrote file afiedt.buf
select t1.id, t2.id
from t1 FULL OUTER JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
1
2
6
5
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Dept.Deptno, Dname, Loc
FROM Emp CROSS JOIN Dept;
/
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
BLAKE 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
JONES 10 ACCOUNTING NEW YORK
MARTIN 10 ACCOUNTING NEW YORK
ALLEN 10
TURNER 10
JAMES 10
WARD 10
FORD 10
SMITH 10
ENAME DEPTNO
SCOTT 10
ADAMS 10
MILLER 10
KING 20
BLAKE 20
CLARK 20
JONES 20
MARTIN 20
ALLEN 20
TURNER 20
JAMES 20
ENAME DEPTNO
WARD 20
FORD 20
SMITH 20
SCOTT 20
ADAMS 20
MILLER 20
KING 30
BLAKE 30
CLARK 30
JONES 30
MARTIN 30
ENAME DEPTNO
ALLEN 30
TURNER 30
JAMES 30
WARD 30
FORD 30
SMITH 30
SCOTT 30
ADAMS 30
MILLER 30
KING 40
BLAKE 40
ENAME DEPTNO
CLARK 40
JONES 40
MARTIN 40
ALLEN 40
TURNER 40
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
DNAME LOC
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
DNAME LOC
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
DNAME LOC
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS BOSTON
DNAME LOC
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
JAMES 40 OPERATIONS BOSTON
WARD 40 OPERATIONS BOSTON
FORD 40 OPERATIONS BOSTON
SMITH 40 OPERATIONS BOSTON
SCOTT 40 OPERATIONS BOSTON
ADAMS 40 OPERATIONS BOSTON
ENAME DEPTNO DNAME LOC
MILLER 40 OPERATIONS BOSTON
56 rows selected.
SELECT Ename, Deptno, Dname, Loc
FROM Emp INNER JOIN Dept
USING(Deptno)
SQL> /
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Dept.Deptno, Dname, Loc
FROM Emp JOIN Dept
ON Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM
Emp INNER JOIN Dept
ON Emp.Deptno = Dept.Deptno
SQL> /
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Sal, Grade, Dept.Deptno, Dname 2 FROM
Emp NATURAL JOIN Dept
JOIN SalGrade
ON Emp.Sal BETWEEN LoSal AND HiSal
SQL> /
SELECT Ename, Sal, Grade, Dept.Deptno, Dname
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> Ed
Wrote file afiedt.buf
1 SELECT Ename, Sal, Grade, Deptno, Dname 2
from Emp NATURAL JOIN Dept
3 JOIN SalGrade
4* ON Emp.Sal BETWEEN LoSal AND HiSal
SQL> /
ENAME SAL GRADE DEPTNO DNAME
SMITH 800 1 20 RESEARCH
JAMES 950 1 30 SALES
ADAMS 1100 1 20 RESEARCH
MARTIN 1250 2 30 SALES
WARD 1250 2 30 SALES
MILLER 1300 2 10 ACCOUNTING
TURNER 1500 3 30 SALES
ALLEN 1600 3 30 SALES
CLARK 2450 4 10 ACCOUNTING
BLAKE 2850 4 30 SALES
JONES 2975 4 20 RESEARCH
ENAME SAL GRADE DEPTNO DNAME
FORD 3000 4 20 RESEARCH
SCOTT 3000 4 20 RESEARCH
KING 5000 5 10 ACCOUNTING
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Dept.Deptno, Dname, Loc
FROM Emp RIGHT JOIN Dept
ON Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM
Emp LEFT JOIN Dept
ON Emp.Deptno = Dept.Deptno
SQL> /
ENAME DEPTNO DNAME LOC
MILLER 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
ADAMS 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
WARD 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
ALLEN 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
BLAKE 30 SALES CHICAGO
14 rows selected.
A join is a SQLquery that extracts information from two or more tables or views.
When you specify multiple tables or views in the FROM clause of a query, Oracle
performs a join, linking rows from multiple tables together. There are several types of
JOINS IN ORACLE-different joins in oracle with examples
1. The purpose of a join is to combine the data across tables.
2. A join is actually performed by the where clause which combines the specified rows of tables.
3. If a join involves in more than two tables then oracle joins first two tables based on the joins
condition and then compares the result with the next table and so on.
TYPES
1 Equi join
2 Non-equi join
3 Self join
4 Natural join
5 Cross join
6 Outer join
Left outer
Right outer
Full outer
7 Inner join
8 Using clause
9 On clause
Cartesian Product
If you don’t specify the join condition while joining two tables, Oracle combines each
row from the first table with each row of the second table. This type of result set is
called as a Cartesian product. The number of rows in a Cartesian product is the product
of the number of rows in each table. Here’s an
example of a Cartesian product
SQL> SELECT Ename, Sal, Job, Dname, Loc
FROM Emp, Dept;
FORD 3000 ANALYST SALES CHICAGO
SMITH 800 CLERK SALES CHICAGO
SCOTT 3000 ANALYST SALES CHICAGO
ADAMS 1100 CLERK SALES CHICAGO
MILLER 1300 CLERK SALES CHICAGO
KING 5000 PRESIDENT OPERATIONS BOSTON
BLAKE 2850 MANAGER OPERATIONS BOSTON
ENAME SAL JOB DNAME LOC
CLARK 2450 MANAGER OPERATIONS BOSTON
JONES 2975 MANAGER OPERATIONS BOSTON
MARTIN 1250 SALESMAN OPERATIONS BOSTON
ALLEN 1600 SALESMAN OPERATIONS BOSTON
TURNER 1500 SALESMAN OPERATIONS BOSTON
JAMES 950 CLERK OPERATIONS BOSTON
WARD 1250 SALESMAN OPERATIONS BOSTON
FORD 3000 ANALYST OPERATIONS BOSTON
SMITH 800 CLERK OPERATIONS BOSTON
SCOTT 3000 ANALYST OPERATIONS BOSTON
ADAMS 1100 CLERK OPERATIONS BOSTON
ENAME SAL JOB DNAME LOC
MILLER 1300 CLERK OPERATIONS BOSTON
56 rows selected.
SQL> ED
Wrote file afiedt.buf
SQL> SELECT Dname, Loc
FROM Emp, Dept
SQL> /
DNAME LOC
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
DNAME LOC
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
DNAME LOC
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
DNAME LOC
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS BOSTON
DNAME LOC
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
DNAME LOC
OPERATIONS BOSTON
56 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Deptno, Dname, Loc 2* FROM
Emp, Dept
SQL> /
SELECT Ename, Job, Deptno, Dname, Loc
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Emp.Deptno, Dname, Loc 2* FROM
Emp, Dept
SQL> /
ENAME JOB
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
WARD SALESMAN
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
30 ACCOUNTING NEW YORK
FORD ANALYST
SMITH CLERK
ENAME JOB
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
ENAME JOB
WARD SALESMAN
FORD ANALYST
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ENAME JOB
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
WARD SALESMAN
FORD ANALYST
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
KING PRESIDENT
BLAKE MANAGER
ENAME JOB
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
JAMES CLERK
WARD SALESMAN
FORD ANALYST
SMITH CLERK
20 ACCOUNTING NEW YORK
20 ACCOUNTING NEW YORK
DEPTNO DNAME LOC
20 ACCOUNTING NEW YORK
20 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 RESEARCH DALLAS
30 RESEARCH DALLAS
10 RESEARCH DALLAS
20 RESEARCH DALLAS
30 RESEARCH DALLAS
30 RESEARCH DALLAS
30 RESEARCH DALLAS
30 RESEARCH DALLAS
DEPTNO DNAME LOC
30 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
10 RESEARCH DALLAS
10 SALES CHICAGO
30 SALES CHICAGO
10 SALES CHICAGO
20 SALES CHICAGO
30 SALES CHICAGO
DEPTNO DNAME LOC
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
20 SALES CHICAGO
20 SALES CHICAGO
20 SALES CHICAGO
20 SALES CHICAGO
10 SALES CHICAGO
10 OPERATIONS BOSTON
30 OPERATIONS BOSTON
DEPTNO DNAME LOC
10 OPERATIONS BOSTON
20 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
30 OPERATIONS BOSTON
20 OPERATIONS BOSTON
20 OPERATIONS BOSTON
SCOTT ANALYST
ADAMS CLERK
ENAME JOB
MILLER CLERK
56 rows selected.
Inner Joins OR UQUE JOIN
An inner join returns the rows that satisfy the join condition. Let’s take an example
to understand the concept of a join. Say you want to list the name and department
name for each employee. To do this, you would use the following SQL statement:
SQL> SELECT Ename, Emp.Deptno, Dname, Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
BLAKE 30 SALES CHICAGO
CLARK 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
FORD 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
ENAME DEPTNO DNAME LOC
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
MILLER 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> cl scr
SQL> Select
2 Empno,
3 Ename,
4 Sal,
5 Sal * 12 AnnSal,
6 Emp.Deptno,
7 Loc
8 FROM Emp, Dept
9 WHERE Emp.Deptno = Dept.Deptno;
EMPNO ENAME SAL ANNSAL DEPTNO LOC
7839 KING 5000 60000 10 NEW YORK
7782 CLARK 2450 29400 10 NEW YORK
7934 MILLER 1300 15600 10 NEW YORK
7566 JONES 2975 35700 20 DALLAS
7788 SCOTT 3000 36000 20 DALLAS
7876 ADAMS 1100 13200 20 DALLAS
7369 SMITH 800 9600 20 DALLAS
7902 FORD 3000 36000 20 DALLAS
7698 BLAKE 2850 34200 30 CHICAGO
7654 MARTIN 1250 15000 30 CHICAGO
7499 ALLEN 1600 19200 30 CHICAGO
EMPNO ENAME SAL ANNSAL DEPTNO LOC
7844 TURNER 1500 18000 30 CHICAGO
7900 JAMES 950 11400 30 CHICAGO
7521 WARD 1250 15000 30 CHICAGO
14 rows selected.
SQL> cl scr
SQL> Select
Dept.Deptno,
Dname,
Loc,
SUM(Sal)
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno
GROUP BY Dept.Deptno, Dname, Loc;
DEPTNO DNAME LOC SUM(SAL)
10 ACCOUNTING NEW YORK 8750
20 RESEARCH DALLAS 10875
30 SALES CHICAGO 9400
SQL> SELECT Ename
FROM Emp E1, Emp E2
SQL> /
SELECT Ename
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> ED
Wrote file afiedt.buf
SELECT E1.Ename
FROM Emp E1, Emp E2
SQL> /
SQL> cl scr
SQL> SELECT
E.Empno,
E.Ename,
D.Deptno,
D.Dname
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno;
EMPNO ENAME DEPTNO DNAME
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7902 FORD 20 RESEARCH
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7499 ALLEN 30 SALES
EMPNO ENAME DEPTNO DNAME
7844 TURNER 30 SALES
7900 JAMES 30 SALES
7521 WARD 30 SALES
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
E.Empno,
E.Ename,
Dept.Deptno,
D.Dname
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno
SQL> /
Dept.Deptno,
*
ERROR at line 4:
ORA-00904: "DEPT"."DEPTNO": invalid identifier
SQL> SELECT
E.Ename,
E.Job,
D.Deptno,
D.Dname,
D.Loc
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno AND
E.Job IN('ANALYST', 'MANAGER' );
ENAME JOB DEPTNO DNAME LOC
CLARK MANAGER 10 ACCOUNTING NEW YORK
JONES MANAGER 20 RESEARCH DALLAS
FORD ANALYST 20 RESEARCH DALLAS
SCOTT ANALYST 20 RESEARCH DALLAS
BLAKE MANAGER 30 SALES CHICAGO
SQL> cl scr
SQL> SELECT
Ename,
DName,
Loc
FROM Emp Employees, Dept Departments
WHERE Employees.Deptno = Departments.Deptno;
ENAME DNAME LOC
KING ACCOUNTING NEW YORK
CLARK ACCOUNTING NEW YORK
MILLER ACCOUNTING NEW YORK
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
ADAMS RESEARCH DALLAS
SMITH RESEARCH DALLAS
FORD RESEARCH DALLAS
BLAKE SALES CHICAGO
MARTIN SALES CHICAGO
ALLEN SALES CHICAGO
ENAME DNAME LOC
TURNER SALES CHICAGO
JAMES SALES CHICAGO
WARD SALES CHICAGO
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
Ename,
DName,
Loc
FROM Emp E, Dept D
* WHERE E.Deptno = D.Deptno
SQL> /
ENAME DNAME LOC
KING ACCOUNTING NEW YORK
CLARK ACCOUNTING NEW YORK
MILLER ACCOUNTING NEW YORK
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
ADAMS RESEARCH DALLAS
SMITH RESEARCH DALLAS
FORD RESEARCH DALLAS
BLAKE SALES CHICAGO
MARTIN SALES CHICAGO
ALLEN SALES CHICAGO
ENAME DNAME LOC
TURNER SALES CHICAGO
JAMES SALES CHICAGO
WARD SALES CHICAGO
14 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, MGR
FROM Emp;
EMPNO ENAME MGR
7839 KING
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7654 MARTIN 7698
7499 ALLEN 7698
7844 TURNER 7698
7900 JAMES 7698
7521 WARD 7698
7902 FORD 7566
7369 SMITH 7902
EMPNO ENAME MGR
7788 SCOTT 7566
7876 ADAMS 7788
7934 MILLER 7782
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Empno, Ename, Sal, MGR
FROM Emp
SQL> /
EMPNO ENAME SAL MGR
7839 KING 5000
7698 BLAKE 2850 7839
7782 CLARK 2450 7839
7566 JONES 2975 7839
7654 MARTIN 1250 7698
7499 ALLEN 1600 7698
7844 TURNER 1500 7698
7900 JAMES 950 7698
7521 WARD 1250 7698
7902 FORD 3000 7566
7369 SMITH 800 7902
EMPNO ENAME SAL MGR
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
7934 MILLER 1300 7782
14 rows selected.
SQL> cl scr
A SELF JOIN is a query in which a table is joined (compared) to itself. Self-joins are useful to compare values in a specific
column with other values in the same column in the same table (related data that is stored in a single table). The self join can be
done by using table aliases to treat one table as though it were a different table and then joining them together. Another common
use for self-joins is obtaining running counts and totals in a query.
The general rule for writing self-joins is:
Write the query doing the selects from the same table listed twice with different aliases
Set up the comparison pedicates or tests
Remove any cases where a given value would be equal to itself.
SQL> SELECT
E1.Ename "Employees",
E2. Ename "Managers"
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno;
Employees Managers
FORD JONES
SCOTT JONES
MARTIN BLAKE
ALLEN BLAKE
JAMES BLAKE
TURNER BLAKE
WARD BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
CLARK KING
Employees Managers
JONES KING
SMITH FORD
13 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
E1.Ename,
E2. Ename
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno
SQL> /
ENAME ENAME
FORD JONES
SCOTT JONES
MARTIN BLAKE
ALLEN BLAKE
JAMES BLAKE
TURNER BLAKE
WARD BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
CLARK KING
ENAME ENAME
JONES KING
SMITH FORD
13 rows selected.
SQL> cl scr
SQL> SELECT
Employees.Ename "Employees",
Managers.Ename "Managers"
FROM Emp Employees, Emp Managers
WHERE Employees.Mgr = Managers.Empno;
Employees Managers
FORD JONES
SCOTT JONES
MARTIN BLAKE
ALLEN BLAKE
JAMES BLAKE
TURNER BLAKE
WARD BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
CLARK KING
Employees Managers
JONES KING
SMITH FORD
13 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
Employees.Ename "Employees",
Managers.Ename "Managers"
FROM Emp Employees, Emp Managers
WHERE Managers.Mgr = Employees.Empno SQL>
/
Employees Managers
JONES FORD
JONES SCOTT
BLAKE MARTIN
BLAKE ALLEN
BLAKE JAMES
BLAKE TURNER
BLAKE WARD
CLARK MILLER
SCOTT ADAMS
KING BLAKE
KING CLARK
Employees Managers
KING JONES
FORD SMITH
13 rows selected.
SQL> cl scr
SQL> SELECT
E1.Ename||'''s Manager is '||
E2.Ename "Employees And Managers"
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno;
Employees And Managers
FORD's Manager is JONES
SCOTT's Manager is JONES
MARTIN's Manager is BLAKE
ALLEN's Manager is BLAKE
JAMES's Manager is BLAKE
TURNER's Manager is BLAKE
WARD's Manager is BLAKE
MILLER's Manager is CLARK
ADAMS's Manager is SCOTT
BLAKE's Manager is KING
CLARK's Manager is KING
Employees And Managers
JONES's Manager is KING
SMITH's Manager is FORD
13 rows selected.
SQL> SPOOL OFF
SQL> cl scr
Equi-Join Versus Non-Equi-Join
The join condition determines whether the join is an equi-join or a non-equi-join.
When a join condition relates two tables by equating the columns from the tables, it is
an equi-join.When a join condition relates two tables by an operator other than equality,
it is a non-equi-join. A query may contain equi-joins as well as
non-equi-joins
SQL> SELECT Empno, Ename, Sal
FROM Emp;
EMPNO ENAME SAL
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
7499 ALLEN 1600
7844 TURNER 1500
7900 JAMES 950
7521 WARD 1250
7902 FORD 3000
7369 SMITH 800
EMPNO ENAME SAL
7788 SCOTT 3000
7876 ADAMS 1100
7934 MILLER 1300
14 rows selected.
SQL> SELECT LoSal, HiSal, Grade
FROM DalGrade;
FROM DalGrade
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> Ed
Wrote file afiedt.buf
SELECT LoSal, HiSal, Grade
FROM SalGrade
SQL> /
LOSAL HISAL GRADE
700 1200 1
1201 1400 2
1401 2000 3
2001 3000 4
3001 9999 5
SQL> SELECT Empno, Ename, Sal
FROM Emp;
EMPNO ENAME SAL
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
7499 ALLEN 1600
7844 TURNER 1500
7900 JAMES 950
7521 WARD 1250
7902 FORD 3000
7369 SMITH 800
EMPNO ENAME SAL
7788 SCOTT 3000
7876 ADAMS 1100
7934 MILLER 1300
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Emp.Deptno, Dname, Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
BLAKE 30 SALES CHICAGO
CLARK 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
FORD 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
ENAME DEPTNO DNAME LOC
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
MILLER 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Emp.Deptno, Dname, Loc 2 FROM
Emp, Dept
WHERE Emp.Deptno > Dept.Deptno
SQL> /
ENAME DEPTNO DNAME LOC
JONES 20 ACCOUNTING NEW YORK
SCOTT 20 ACCOUNTING NEW YORK
ADAMS 20 ACCOUNTING NEW YORK
SMITH 20 ACCOUNTING NEW YORK
FORD 20 ACCOUNTING NEW YORK
BLAKE 30 ACCOUNTING NEW YORK
MARTIN 30 ACCOUNTING NEW YORK
ALLEN 30 ACCOUNTING NEW YORK
TURNER 30 ACCOUNTING NEW YORK
JAMES 30 ACCOUNTING NEW YORK
WARD 30 ACCOUNTING NEW YORK
ENAME DEPTNO DNAME LOC
BLAKE 30 RESEARCH DALLAS
MARTIN 30 RESEARCH DALLAS
ALLEN 30 RESEARCH DALLAS
TURNER 30 RESEARCH DALLAS
JAMES 30 RESEARCH DALLAS
WARD 30 RESEARCH DALLAS
17 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Sal, Emp.Deptno Deptno, Dname, Loc 2 FROM
Emp, Dept
WHERE Emp.Deptno(+) = Dept.Deptno
SQL> /
ENAME SAL DEPTNO DNAME LOC
KING 5000 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
MILLER 1300 10 ACCOUNTING NEW YORK
JONES 2975 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
BLAKE 2850 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
ENAME SAL DEPTNO DNAME LOC
TURNER 1500 30 SALES CHICAGO
JAMES 950 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
OPERATIONS BOSTON
SHIPPING CHENNAI
CARGO MUMBAI
COURIER DELHI
18 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Sal, Dept.Deptno Deptno, Dname, Loc 2 FROM
Emp, Dept
WHERE Emp.Deptno = Dept.Deptno(+)
SQL> /
ENAME SAL DEPTNO DNAME LOC
MILLER 1300 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
KING 5000 10 ACCOUNTING NEW YORK
ADAMS 1100 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
WARD 1250 30 SALES CHICAGO
JAMES 950 30 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
ENAME SAL DEPTNO DNAME LOC
ALLEN 1600 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
14 rows selected.
ANSI Join Syntax
Starting with Oracle 9i, Oracle implemented a number of additions to SQL to conform to many aspects of the ANSI
SQL/92 and SQL:1999 standards. The advantage of the ANSI join syntax over the traditional comma-separated tables FROM
clause is that SQL queries can run unmodified against other non-Oracle, ANSI-compliant databases
SQL JOIN Implementations:
A CROSS JOIN implements a Cartesian Product, which returns rows combined from both tables. It matches every row in
one table against every row in another table. For example, if you have 3 rows in one table and 2 rows in another you’ll
have 2 times 3, or 6, rows in the result set.
An INNER JOIN implements an equijoin, which returns rows combined from both tables when the values in the join
condition match. Join conditions can include one or more columns.
A NATURAL JOIN implements an equijoin, which returns rows combined from both tables when the values in the join
condition match. While the join conditions can include one or more columns, you have no choice in selecting the join
columns. A NATURAL JOIN checks the definition of both tables (in the data catalog or metadata) and looks for like named
columns. It then joins the table based on the values of all like named columns from both tables. One might say it’s an
unnatural behavior because a developer has no way to override it.
A LEFT JOIN implements an equijoin and relative complement, which returns rows combined from both tables when the
values in the join condition match and the rows in the left table that aren’t found in the right table. This makes a LEFT
JOIN a combination of an INNER JOIN and a relative complement of the table on the right of the join. This nuance
becomes important when we examine the (+) semantic implemented by Oracle.
A RIGHT JOIN implements an equijoin and relative complement, which returns rows combined from both tables when the
values in the join condition match and the rows in the right table that aren’t found in the left table. This makes a RIGHT
JOIN the opposite of a LEFT JOIN. Naturally, it is also a combination of an INNER JOIN and a relative complement of the
left table. Like the LEFT JOIN, this nuance becomes important when we examine the (+) semantic implemented by
Oracle.
A FULL JOIN implements an equijoin and both relative complements, which returns rows combined from both tables
when the values in the join condition match and the rows in both the left and right tables that aren’t found in their
respective other tables. This makes a FULL JOIN a combination of an INNER JOIN and relative complements of the both
tables. This type of join can’t be done with the (+) alone in the older syntax. It requires left and right outer queries glued
together by a UNION set operator. The UNION set operator eliminates duplicate from the intersection of the two tables,
which are created by gluing together two outer joins.
An Antijoin implements an outer join minus an equijoin, which returns a relative complement when the first query is a
LEFT JOIN or RIGHT JOIN and a symmetric difference when the first query is a FULL JOIN.
EX---SQL> select * from t1;
ID
----------
1
2
3
4
SQL> select * from t2;
ID
----------
3
4
5
6
-- LEFT OUTER JOIN
SQL> select t1.id, t2.id
2 from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);
ID ID
---------- ----------
3 3
4 4
1
2
-- RIGHT OUTER JOIN
SQL> select t1.id, t2.id
2 from t1 RIGHT OUTER JOIN t2 ON (t1.id = t2.id);
ID ID
---------- ----------
3 3
4 4
6
5
-- LEFT JOIN (SAME AS LEFT OUTER JOIN)
SQL> ed
1 select t1.id, t2.id
2* from t1 LEFT JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
1
2
-- RIGHT JOIN (SAME AS RIGHT OUTER JOIN)
SQL> ed
Wrote file afiedt.buf
1 select t1.id, t2.id
2* from t1 RIGHT JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
6
5
-- INNER JOIN (REGULAR JOIN)
SQL> ed
Wrote file afiedt.buf
select t1.id, t2.id
from t1 INNER JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
-- FULL OUTER JOIN
SQL> ed
Wrote file afiedt.buf
select t1.id, t2.id
from t1 FULL OUTER JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
1
2
6
5
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Dept.Deptno, Dname, Loc
FROM Emp CROSS JOIN Dept;
/
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
BLAKE 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
JONES 10 ACCOUNTING NEW YORK
MARTIN 10 ACCOUNTING NEW YORK
ALLEN 10
TURNER 10
JAMES 10
WARD 10
FORD 10
SMITH 10
ENAME DEPTNO
SCOTT 10
ADAMS 10
MILLER 10
KING 20
BLAKE 20
CLARK 20
JONES 20
MARTIN 20
ALLEN 20
TURNER 20
JAMES 20
ENAME DEPTNO
WARD 20
FORD 20
SMITH 20
SCOTT 20
ADAMS 20
MILLER 20
KING 30
BLAKE 30
CLARK 30
JONES 30
MARTIN 30
ENAME DEPTNO
ALLEN 30
TURNER 30
JAMES 30
WARD 30
FORD 30
SMITH 30
SCOTT 30
ADAMS 30
MILLER 30
KING 40
BLAKE 40
ENAME DEPTNO
CLARK 40
JONES 40
MARTIN 40
ALLEN 40
TURNER 40
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
DNAME LOC
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
ACCOUNTING NEW YORK
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
DNAME LOC
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
RESEARCH DALLAS
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
DNAME LOC
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS BOSTON
DNAME LOC
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
OPERATIONS BOSTON
JAMES 40 OPERATIONS BOSTON
WARD 40 OPERATIONS BOSTON
FORD 40 OPERATIONS BOSTON
SMITH 40 OPERATIONS BOSTON
SCOTT 40 OPERATIONS BOSTON
ADAMS 40 OPERATIONS BOSTON
ENAME DEPTNO DNAME LOC
MILLER 40 OPERATIONS BOSTON
56 rows selected.
SELECT Ename, Deptno, Dname, Loc
FROM Emp INNER JOIN Dept
USING(Deptno)
SQL> /
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Dept.Deptno, Dname, Loc
FROM Emp JOIN Dept
ON Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM
Emp INNER JOIN Dept
ON Emp.Deptno = Dept.Deptno
SQL> /
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Sal, Grade, Dept.Deptno, Dname 2 FROM
Emp NATURAL JOIN Dept
JOIN SalGrade
ON Emp.Sal BETWEEN LoSal AND HiSal
SQL> /
SELECT Ename, Sal, Grade, Dept.Deptno, Dname
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> Ed
Wrote file afiedt.buf
1 SELECT Ename, Sal, Grade, Deptno, Dname 2
from Emp NATURAL JOIN Dept
3 JOIN SalGrade
4* ON Emp.Sal BETWEEN LoSal AND HiSal
SQL> /
ENAME SAL GRADE DEPTNO DNAME
SMITH 800 1 20 RESEARCH
JAMES 950 1 30 SALES
ADAMS 1100 1 20 RESEARCH
MARTIN 1250 2 30 SALES
WARD 1250 2 30 SALES
MILLER 1300 2 10 ACCOUNTING
TURNER 1500 3 30 SALES
ALLEN 1600 3 30 SALES
CLARK 2450 4 10 ACCOUNTING
BLAKE 2850 4 30 SALES
JONES 2975 4 20 RESEARCH
ENAME SAL GRADE DEPTNO DNAME
FORD 3000 4 20 RESEARCH
SCOTT 3000 4 20 RESEARCH
KING 5000 5 10 ACCOUNTING
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Dept.Deptno, Dname, Loc
FROM Emp RIGHT JOIN Dept
ON Emp.Deptno = Dept.Deptno;
ENAME DEPTNO DNAME LOC
KING 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
WARD 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM
Emp LEFT JOIN Dept
ON Emp.Deptno = Dept.Deptno
SQL> /
ENAME DEPTNO DNAME LOC
MILLER 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
ADAMS 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
WARD 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC
ALLEN 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
BLAKE 30 SALES CHICAGO
14 rows selected.
No comments:
Post a Comment