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 query outputs a result based on the values in the common columns of two tables to be joined. If the joined result of the two tables meets all join conditions, it is called an inner join or simply a join.

The query result of an outer join can include all instances from the left table (a left outer join), all instances of the right table (a right outer join), or both (a full outer join). If the table has no instance that meets the join conditions, the corresponding column in the result will have a NULL value.

Syntax

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

table_specification
:
table_specification [ correlation ]
CLASS table_name [ correlation ]
subquery correlation
TABLE (expression) correlation

qualified_join_table_specification
:
[ INNER | {LEFT | RIGHT} [ OUTER ] ] JOIN table_specification join_condition

join_condition
:
ON search_condition

  • qualified_join_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 can differ depending on whether the condition is used in the WHERE or ON clause.

Example
  • Example 1
  • The following is an example of retrieving 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;  
=== <Result of SELECT Command in Line 2> ===
    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'    
8 rows selected.

  • Example 2
  • The following is an example of retrieving 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;
=== <Result of SELECT Command in Line 3> ===
    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.

  • 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;
=== <Result of SELECT Command in Line 3> ===
    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'
21 rows selected.

  • 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;
=== <Result of SELECT Command in Line 2> ===
    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.