Database Tutorial

ANSI SQL:89

The following syntax examples let you perform a LEFT JOIN of two tables in an Oracle database.

ANSI SQL:89 syntax doesn’t support a LEFT [OUTER] JOIN. Oracle Corporation noted this problem and implemented an extension to ANSI SQL:89, which is known as Oracle Proprietary SQL (e.g., dubbed as such by Alice Rischert in Oracle SQL by Example). This proprietary syntax doesn’t work in any other database management system.

You work with this Oracle Proprietary extension by putting the table names in the FROM clause as a comma delimited list. There’s no requirement to implicitly or explicitly order these table names. The join statement(s) occurs in the WHERE clause. You can compare the equality of values between one column from each table, or a set of column from each table. In the case of a set of columns, you use the AND logical operator to broaden the comparison of values. You may also use table aliases to effect a self-join.

Oracle’s Proprietary extension uses a (+) operator, which supports LEFT JOIN and RIGHT JOIN but not FULL JOIN. At least you can’t support a FULL JOIN without also using set operators.

The (+) following the ordinal_french_id column in the join indicates that you want the relative complement of that table, which are all non-matching values in the ordinal_english table. Basically, the relative complement of the table on the right of the join points to all values in the table on the left of the join, which is the mathematical way to view the problem. This moreover is a LEFT JOIN.

The following syntax lets you LEFT JOIN two tables:

1
2
3
4

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english, ordinal_french
WHERE    ordinal_english_id = ordinal_french_id(+);

This returns only rows with only the two selected columns, where they are the result of a join between to tables or a row exists in the ORDINAL_ENGLISH table that isn’t found in the ORDINAL_FRENCH table.

ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------- --------------------
One                  Un
Three                Trois
Four                 Quatre
Five                 Cinq
Six                  Six
Two

As mentioned, this is the older syntax and it is proptietary to Oracle SQL dialect because there wasn’t any way to capture the outer join values until the ANSI SQL:92 specification.

ANSI SQL:92

The following syntax lets you inner join two tables:

ANSI SQL:92 syntax effects an LEFT [OUTER] JOIN by using an ON subclause or a USING subclause in the FROM clause. It returns the intersection of rows that share matching column values. It also returns the rows from the table on the left of the join that aren’t found in the table on the right.

As mentioned, the OUTER keyword is optional and generally only used by tools or for added clarity by developers. The ON subclause tests the equality of values between columns where the column names may be the same or different. The USING clause tests the equality of two columns when they have the same name.

The ON subclause also lets you use an AND logical operator to support multi-column joins. You may also provide multiple column names as call parameters to the USING clause because it works more like a function than a clause.

The following syntax lets you LEFT JOIN between two tables with the ON subclause:

1
2
3
4

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english LEFT OUTER JOIN ordinal_french
ON       ordinal_english_id = ordinal_french_id;

This returns only rows with only the two selected columns, where they are the result of a join between to tables or a row exists in the ORDINAL_ENGLISH table that isn’t found in the ORDINAL_FRENCH table.

ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------- --------------------
One                  Un
Three                Trois
Four                 Quatre
Five                 Cinq
Six                  Six
Two

The ORDINAL_ENGLISH table’s primary key column name differs from the ORDINAL_FRENCH table’s primary key column name. The difference requires that you use the ON subclause.

As mentioned, you can use the USING subclause when the column names are equal. You can test this by changing the second table to the ORDINAL_OLD_ENGLISH table. They both enjoy the same primary key column name.

The following demonstrates a LEFT JOIN that uses the USING subclause

1
2
3

SELECT   ordinal_english_text
,        ordinal_old_english_text
FROM     ordinal_english LEFT JOIN ordinal_old_english USING(ordinal_english_id);

This returns the same type of values seen above, except for that you see old English words instead of French words (:-)).

As mentioned, you can put more than one column name in a USING clause. There may be an implementation limit but I’ve not found it. It appears that the parser treats the list recursively. The syntax for that approach is the following:

USING(column_name1, column_name2, ...)