SQL Joins

                A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

There are different kinds of joins.

Types of Joins

There are several types of joins to be aware of:

Cross joins

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.

Inner joins

Inner joins are the regular joins. An inner join returns the rows that satisfy the join condition. Each row returned by an inner join contains data from all the tables involved in the join.

Outer joins

Outer joins are an extension to inner joins. An outer join returns the rows that satisfy the join condition and also the rows from one table for which no corresponding rows (i.e., that satisfy the join condition) exist in the other table.

Equi- and non-equi-joins

An equi-join is a join where the join condition uses the equal to (=) operator to relate the rows of two tables. When a join condition uses any other operator to relate the rows of two tables, the join is called a non-equi-join.

Self joins

A self join is a join of a table to itself.

Partition outer joins

A new type of join introduced in Oracle Database 10g that is slated to be part of the next ANSI/ISO SQL standard after SQL:2003. A partition outer join divides your result set into groups, or partitions, and repeats the same outer join for each of these groups. Such joins are extremely handy for generating missing rows.

Cross Joins/Cartesian Products

If you don't specify a join condition when joining two tables, Oracle combines each row from the first table with each row from the second table. This type of result set is called a cross join or a Cartesian product; either term is acceptable. The number of rows in a cross join is the product of the number of rows in each table. Here's an example of a cross join:

SELECT e.lname, d.name

FROM employee e CROSS JOIN department d;



LNAME      NAME

---------- --------------

SMITH      ACCOUNTING

ALLEN      ACCOUNTING

WARD       ACCOUNTING

JONES      ACCOUNTING

MARTIN     ACCOUNTING

BLAKE      ACCOUNTING

 . . . 

 . . . 

 . . . 

SCOTT      OPERATIONS

KING       OPERATIONS

TURNER     OPERATIONS

ADAMS      OPERATIONS

JAMES      OPERATIONS

FORD       OPERATIONS

MILLER     OPERATIONS



56 rows selected.

Since the query didn't specify a join condition, each row from the employee table is combined with each row from the department table. Needless to say, this result set is of little use. More often than not, a cross join produces a result set containing misleading rows. Therefore, unless you are sure that you want a Cartesian product, don't use a cross join.

Notice the use of the keyword CROSS before the JOIN keyword in the previous example. If you omit the CROSS keyword, and don't specify a join condition, Oracle will throw an error, because it thinks that you are attempting a regular join and have inadvertently omitted the join condition. For example:

SELECT e.lname, d.name

FROM employee e JOIN department d;

FROM employee e JOIN department d

                                *

ERROR at line 2:

ORA-00905: missing keyword

What happens when you specify the CROSS keyword as well as a join condition through an ON or USING clause? Oracle rejects your query with an error, and rightly so, because cross joins are joins without join conditions. For example:

SELECT e.lname, d.name

FROM employee e CROSS JOIN department d

ON e.dept_id = d.dept_id;

ON e.dept_id = d.dept_id

*

ERROR at line 3:

ORA-00933: SQL command not properly ended

Be aware that it's easily possible to inadvertently specify a cross join when using the old join syntax described in the Appendix A. Using that syntax, a cross join occurs when you list two tables in the FROM clause separated by commas, and you forget to write a join condition into the query's WHERE clause.

3.3.2 Inner Joins

Inner joins are the most commonly used joins. When people refer simply to a "join," they most likely mean an "inner join." An inner join relates the rows from the source tables based on the join condition, and returns the rows that satisfy it. For example, to list the name and department for each employee, you would use the following SQL statement:

SELECT e.lname, d.name

FROM employee e JOIN department d

ON e.dept_id = d.dept_id;



LNAME                NAME

-------------------- --------------------

CLARK                ACCOUNTING

KING                 ACCOUNTING

MILLER               ACCOUNTING

SMITH                RESEARCH

ADAMS                RESEARCH

FORD                 RESEARCH

SCOTT                RESEARCH

JONES                RESEARCH

ALLEN                SALES

BLAKE                SALES

MARTIN               SALES

JAMES                SALES

TURNER               SALES

WARD                 SALES



14 rows selected.

In this example, each row of the employee table is combined with each row of the department table, and if the combination satisfies the join condition (dept_id in the employee table matches the dept_id in thedepartment table), then it is included in the result set.

The JOIN keyword, unless prefixed with another keyword, means an inner join. Optionally, you can use the INNER keyword before the JOIN keyword to explicitly indicate an inner join, as in the following example:

SELECT e.lname, d.name

FROM employee e INNER JOIN department d

ON e.dept_id = d.dept_id;

Let's look at another example to go a bit deeper in the concept behind an inner join:

SELECT * FROM department;



   DEPT_ID NAME                 LOCATION_ID

---------- -------------------- -----------

        10 ACCOUNTING                   122

        20 RESEARCH                     124

        30 SALES

        40 OPERATIONS                   167



SELECT * FROM  location;



LOCATION_ID REGIONAL_GROUP

----------- --------------------

        122 NEW YORK

        124 DALLAS

        123 CHICAGO

        167 BOSTON

        144 SAN FRANCISCO



SELECT d.name, l.regional_group

FROM department d JOIN location l

ON d.location_id = l.location_id;



NAME                 REGIONAL_GROUP

-------------------- --------------------

ACCOUNTING           NEW YORK

RESEARCH             DALLAS

OPERATIONS           BOSTON

Outer Joins

Sometimes, while performing a join between two tables, you need to return all the rows from one table even when there are no corresponding rows in the other table. For example, you may want to see all the departments even if they are not related to any particular location. Oracle provides a special type of join to include rows from one table that don't have matching rows from the other table. This type of join is known as an outer join.

The syntax of an outer join is:
FROM table1 { LEFT | RIGHT | FULL } [OUTER] JOIN table2

The syntax elements are:



table1, table2



Specifies the tables between which you are performing the outer join.



LEFT



Specifies that the results be generated using all rows from table1. For those rows in table1 that don't have corresponding rows in table2, NULLs are returned in the result set for the table2 columns.



RIGHT



Specifies that the results be generated using all rows from table2. For those rows in table2 that don't have corresponding rows in table1, NULLs are returned in the result set for the table1 columns.



FULL



Specifies that the results be generated using all rows from table1 and table2. For those rows intable1 that don't have corresponding rows in table2, NULLs are returned in the result set for thetable2 columns. Additionally, for those rows in table2 that don't have corresponding rows in table1, NULLs are returned in the result set for the table1 columns.



OUTER



Specifies that you are performing an OUTER join. This keyword is optional. If you use LEFT, RIGHT, or FULL, Oracle automatically assumes an outer join. The OUTER keyword is for completeness' sake, and complements the INNER keyword.


3.3.3.1 Left outer joins

To list all departments even if they are not related to any particular location, you can perform a LEFT OUTER JOIN between the department and the location tables. For example:
SELECT d.dept_id, d.name, l.regional_group

FROM department d LEFT OUTER JOIN location l

ON d.location_id = l.location_id;



   DEPT_ID NAME                 REGIONAL_GROUP

---------- -------------------- --------------

        10 ACCOUNTING           NEW YORK

        20 RESEARCH             DALLAS

        30 SALES

        40 OPERATIONS           BOSTON

This query lists all the rows from the department table together with their corresponding locations from thelocation table. For the rows from department with no corresponding rows in location, NULLs are returned for the l.regional_group column in the result set.



Right outer joins

Likewise, to list all the locations even if they are not related to any particular department, you can perform a RIGHT OUTER JOIN between the location and the department tables. For example:
SELECT d.dept_id, d.name, l.regional_group

FROM department d RIGHT OUTER JOIN location l

ON d.location_id = l.location_id;



   DEPT_ID NAME                 REGIONAL_GROUP

---------- -------------------- ---------------

        10 ACCOUNTING           NEW YORK

                                CHICAGO

        20 RESEARCH             DALLAS

                                SAN FRANCISCO

        40 OPERATIONS           BOSTON

This query lists all the rows from the location table, and their corresponding departments from thedepartment table. For the rows from location that don't have corresponding rows in department, NULLs are returned for the d.dept_id and d.name columns in the result set.

The LEFT and RIGHT keywords in an outer join query are relative to the position of the tables in the FROM clause. The same result can be achieved using either a LEFT OUTER JOIN or a RIGHT OUTER JOIN, by switching the position of the tables. For example, the following two queries are equivalent:
SELECT d.dept_id, d.name, l.regional_group

FROM department d LEFT OUTER JOIN location l

ON d.location_id = l.location_id;



SELECT d.dept_id, d.name, l.regional_group

FROM location l RIGHT OUTER JOIN department d

ON d.location_id = l.location_id;

In each case, the directional word, either LEFT or RIGHT, points toward the anchor table, the table that is required. The other table is then the optional table in the join.



Full outer joins

Ocassionally, you may need the effect of an outer join in both directions, which you can think of as a combination of LEFT and RIGHT outer joins. For example, you may need to list all the departments (with or without a location), as well as all the locations (with or without a department). Use a FULL OUTER JOIN to generate such a result set:
SELECT d.dept_id, d.name, l.regional_group

FROM department d FULL OUTER JOIN location l

ON 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.

This query performs a FULL OUTER JOIN between the two tables, and lists:


All the rows that satisfy the join condition


The rows in the department table that don't have a corresponding location


The rows in the location table that don't have a corresponding department



A full outer join is bidirectional in the sense that the result is the same irrespective of the position of the tables in the FROM clause. In mathematical terms, you would consider the FULL OUTER JOIN operator to be "commutative."