BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Advanced Oracle SQL: Equijoin and Outer Join

Oracle Tips by Laurent Schneider

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced Oracle SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Joins

A join is used to select data from more than one table.

     1.      Equijoin

An equijoin is a join where keys of both tables are matched using the equal comparison operator:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e,
   DEPT d
WHERE
   e.DEPTNO=d.DEPTNO;

ENAME          DEPTNO LOC
---------- ---------- -------------
SMITH              20 DALLAS
ALLEN              30 CHICAGO
WARD               30 CHICAGO
JONES              20 DALLAS
MARTIN             30 CHICAGO
BLAKE              30 CHICAGO
CLARK              10 NEW YORK
SCOTT              20 DALLAS
KING               10 NEW YORK
TURNER             30 CHICAGO
ADAMS              20 DALLAS
JAMES              30 CHICAGO
FORD               20 DALLAS
MILLER             10 NEW YORK

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   280 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   280 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------- 

   4 - access("E"."DEPTNO"="D"."DEPTNO")
      
filter("E"."DEPTNO"="D"."DEPTNO")

 
Both EMP and DEPT contain a reference to DEPTNO. Some columns from both tables are returned. An alternative syntax does exactly the same thing using the JOIN keyword. The first syntax is NATURAL JOIN.

SELECT
   e.ENAME,
   DEPTNO,
   d.LOC
FROM
   EMP e
NATURAL JOIN
DEPT d;

Note: When selecting from more than one table, the best practice is to prefix all columns. Not only does this improve readability, but it also helps Oracle to parse the query quicker.

The join columns are not defined. Natural join match all columns with the same name. In EMP and DEPT, there is only one column with the same name, and it is called DEPTNO.

The second syntax is JOIN with USING:

SELECT
   e.ENAME,
   DEPTNO,
   d.LOC
FROM
   EMP e
JOIN
   DEPT d
USING
(
   DEPTNO
);

The USING clause helps by specifying the join column.  The join column cannot be prefixed.

The third syntax is JOIN with ON. It is the most robust syntax as it allows prefixing:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e
JOIN
   DEPT d
ON
   e.DEPTNO=d.DEPTNO;

The join columns are defined in the ON clause.

     2.      Outer join

A left or right outer join is a join where rows in one table that do not have a matching row in the second table are selected with null values for the unknown columns:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e,
   DEPT d
WHERE
   e.DEPTNO(+)=d.DEPTNO;

ENAME          DEPTNO LOC
---------- ---------- -------------
SMITH              20 DALLAS
ALLEN              30 CHICAGO
WARD               30 CHICAGO
JONES              20 DALLAS
MARTIN             30 CHICAGO
BLAKE              30 CHICAGO
CLARK              10 NEW YORK
SCOTT              20 DALLAS
KING               10 NEW YORK
TURNER             30 CHICAGO
ADAMS              20 DALLAS
JAMES              30 CHICAGO
FORD               20 DALLAS
MILLER             10 NEW YORK
                   40 BOSTON
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   280 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |         |    14 |   280 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------- 

   4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
       filter("E"."DEPTNO"(+)="D"."DEPTNO")

Or:

SELECT
   e.ENAME,
   d.DEPTNO,
  
d.LOC
FROM
   EMP e
RIGHT JOIN
   DEPT d
ON
   e.DEPTNO=d.DEPTNO

Or:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   DEPT d
LEFT JOIN
   EMP e
ON
   e.DEPTNO=d.DEPTNO

The right/left outer join selects departments with or without employees. As there is no employee in department 40 yet, department 40 is returned with a NULL value for the ENAME column of EMP.
 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter