Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Outer Join

Description

A join is a query that combines the rows of two or more tables or virtual tables (views). In a join query, a condition that compares the columns that are common in two or more tables is called a join condition. Rows are retrieved from each joined table, and are combined only when they satisfy the specified join condition.

A join query using an equality operator (=) is called an equi-join, and one without any join condition is called a cartesian product. Meanwhile, joining a single table is called a self join. In a self join, table ALIAS is used to distinguish columns, because the same table is used twice in the FROM clause.

A join that outputs only rows that satisfy the join condition from a joined table is called an inner or a simple join, whereas a join that outputs both rows that satisfy and do not satisfy the join condition from a joined table is called an outer join. An outer join is divided into a left outer join which outputs all rowss of the left table as the result, a right outer join which outputs all rowss of the right table as the result and a full outer join which outputs all rows of both tables. If there is no column value that corresponds to a table on one side in the result of an outer join query, all rowss are returned as NULL.

Syntax

FROM table_specification [{, table_specification | join_table_specification}...]

 

table_specification :

table_specification [ correlation ]

CLASS table_name [ correlation ]

subquery correlation

TABLE (expression) correlation

 

join_table_specification :

[ INNER | {LEFT | RIGHT} [ OUTER ] ] JOIN table_specification

join_condition

 

join_condition :

ON search_condition

  • oin_table_specification
    • LEFT | RIGHT } [ OUTER ] JOIN : LEFT is used for a left outer join query, and RIGHT is for a right outer join query.

CUBRID does not support full outer joins. Path expressions that include subqueries and sub-columns cannot be used in the join conditions of an outer join.

Join conditions of an outer join are specified in a different way from those of an inner join. In an inner join, join conditions are expressed in the WHERE clause; in an outer join, they appear after the ON keyword in the FROM clause. Other retrieval conditions can be used in the WHERE or ON clause, but the retrieval result depends on whether the condition is used in the WHERE or ON clause.

The table execution order is fixed according to the order specified in the FROM clause. Therefore, when using an outer join, you should create a query statement in consideration of the table order. It is recommended to use standard statements using { LEFT | RIGHT } [ OUTER ] JOIN, because using an Oracle-style join query statements by specifying an outer join operator (+) in the WHERE clause, even if possible, might lead the execution result or plan in an unwanted direction.

Example 1

The following example shows how to retrieve the years and host countries of the Olympic Games since 1950 where a world record has been set. The following query retrieves instances whose values of the host_year column in the history table are greater than 1950.

SELECT DISTINCT h.host_year, o.host_nation FROM history h, olympic o

WHERE h.host_year=o.host_year AND o.host_year>1950;

    host_year  host_nation

===================================

         1968  'Mexico'

         1980  'U.S.S.R.'

         1984  'United States of America'

         1988  'Korea'

         1992  'Spain'

         1996  'United States of America'

         2000  'Australia'

         2004  'Greece' 

Example 2

The following example shows how to retrieve the years and host countries of the Olympic Games since 1950 where a world record has been set, but including the Olympic Games where any world records haven't been set in the result. This example can be expressed in the following right outer join query. In this example, all instances whose values of the host_year column in the history table are not greater than 1950 are also retrieved. All instances of host_nation are included because this is a right outer join. host_year that does not have a value is represented as NULL.

SELECT DISTINCT h.host_year, o.host_nation

FROM history h RIGHT OUTER JOIN olympic o ON h.host_year=o.host_year WHERE o.host_year>1950;

    host_year  host_nation

===================================

         NULL  'Australia'

         NULL  'Canada'

         NULL  'Finland'

         NULL  'Germany'

         NULL  'Italy'

         NULL  'Japan'

         1968  'Mexico'

         1980  'U.S.S.R.'

         1984  'United States of America'

         1988  'Korea'

         1992  'Spain'

         1996  'United States of America'

         2000  'Australia'

         2004  'Greece'    

Example 3

A right outer join query can be converted to a left outer join query by switching the position of two tables in the FROM clause. The right outer join query in the previous example can be expressed as a left outer join query as follows:

SELECT DISTINCT h.host_year, o.host_nation

FROM olympic o LEFT OUTER JOIN history h ON h.host_year=o.host_year WHERE o.host_year>1950;

    host_year  host_nation

===================================

         NULL  'Australia'

         NULL  'Canada'

         NULL  'Finland'

         NULL  'Germany'

         NULL  'Italy'

         NULL  'Japan'

         1968  'Mexico'

         1980  'U.S.S.R.'

         1984  'United States of America'

         1988  'Korea'

         1992  'Spain'

         1996  'United States of America'

         2000  'Australia'

         2004  'Greece' 

14 rows selected.

In this example, h.host_year=o.host_year is an outer join condition, and o.host_year > 1950 is a search condition. If the search condition is used not in the WHERE clause but in the ON clause, the meaning and the result will be different. The following query also includes instances whose values of o.host_year are not greater than 1950.

SELECT DISTINCT h.host_year, o.host_nation

FROM olympic o LEFT OUTER JOIN history h ON h.host_year=o.host_year AND o.host_year>1950;    

 

=== <Result of SELECT Command in Line 3> ===

    host_year  host_nation

===================================

         NULL  'Australia'

         NULL  'Belgium'

         NULL  'Canada'

...

         1996  'United States of America'

         2000  'Australia'

         2004  'Greece'

Example 4

Outer joins can also be represented by using (+) in the WHERE clause. The above example is a query that has the same meaning as the example using the LEFT OUTER JOIN. The (+) syntax is not ISO/aNSI standard, so it can lead to ambiguous situations. It is recommended to use the standard syntax LEFT OUTER JOIN (or RIGHT OUTER JOIN) if possible.

SELECT DISTINCT h.host_year, o.host_nation FROM history h, olympic o

WHERE o.host_year=h.host_year(+) AND o.host_year>1950;

    host_year  host_nation

===================================

         NULL  'Australia'

         NULL  'Canada'

         NULL  'Finland'

         NULL  'Germany'

         NULL  'Italy'

         NULL  'Japan'

         1968  'Mexico'

         1980  'U.S.S.R.'

         1984  'United States of America'

         1988  'Korea'

         1992  'Spain'

         1996  'United States of America'

         2000  'Australia'

         2004  'Greece'