Skip to main content

JOIN (SQL)

A SELECT subclause that creates a table based on the data in two tables.

Synopsis

Inner Join

SELECT ... FROM table1 INNER JOIN table2 ON condition
SELECT ... FROM table1 INNER JOIN table2 USING (column, column2, ...)
SELECT ... FROM table1 JOIN table2 ...

SELECT ... FROM table1 NATURAL INNER JOIN table2
SELECT ... FROM table1 NATURAL JOIN table2
Left Outer Join

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON condition
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING (column, column2, ...)
SELECT ... FROM table1 LEFT JOIN table2 ...

SELECT ... FROM table1 NATURAL LEFT OUTER JOIN table2
SELECT ... FROM table1 NATURAL LEFT JOIN table2
Right Outer Join

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON condition
SELECT ... FROM table1 RIGHT OUTER JOIN table2 USING (column, column2, ...)
SELECT ... FROM table1 RIGHT JOIN table2 ...

SELECT ... FROM table1 NATURAL RIGHT OUTER JOIN table2
SELECT ... FROM table1 NATURAL RIGHT JOIN table2
Full Outer Join

SELECT ... FROM table1 FULL OUTER JOIN table2 ON condition
SELECT ... FROM table1 FULL JOIN table2 ON condition
Cross Join

SELECT ... FROM table1 CROSS JOIN table2

Description

The JOIN operation combines matching rows from two tables into a single table. Rows across two tables are considered a match when they have identical values in one or more specified columns. To further limit the returned rows in the joined table, you can specify additional restrictions.

Use joins to generate reports and queries that link related data across tables. Specify JOIN operations in a SELECT query as part of the FROM clause. Within a query, you can specify multiple inner and outer joins in any order.

Inner Join

An INNER JOIN returns the matching rows from the first and second table. For example:

SELECT Table1.Letter, Table2.Number
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID

Left: Table1 - ID:1-2-3, Letter:A-B-C. (right) Right: Table2 - ID:3-4-5, Number:3-4-5. Center: INNER JOIN - Letter:C, Number:

  • SELECT ... FROM table1 INNER JOIN table2 ON condition returns the rows from table1 and table2 that match the condition expression specified in the ON clause. You can specify the ON clause anywhere within a join expression.

    This query returns the names of employees and their companies, joining data from the Sample.Employee table (aliased to E) and Sample.Company table (aliased to C). It returns E.Name and C.Name values only for rows in which the CompanyID column of both tables have matching values.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    INNER JOIN Sample.Company AS C
    ON E.CompanyID = C.CompanyID

    This query additionally restricts the data by returning only rows of employees who are older than 20.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    INNER JOIN Sample.Company AS C
    ON E.CompanyID = C.CompanyID AND E.Age > 20

    Example: Join Table Data Using Inner and Outer Joins

  • SELECT ... FROM table1 INNER JOIN table2 USING (column, column2, ...) returns the rows from table1 and table2 that have matching values in the specified columns. The columns specified in the USING clause must appear in both tables. Use this syntax to express equality conditions more succinctly that the ON syntax, provided that the columns being linked have the same names in both tables. In multi-join queries, you can specify a USING clause only for the first join.

    This query performs the same join as in the previous syntax, because both columns have a CompanyID column that they can join on.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    INNER JOIN Sample.Company AS C
    USING (CompanyID)

    Example: Join on Identically Named Columns Across Two Tables

  • SELECT ... FROM table1 JOIN table2 ... is equivalent to the previous INNER JOIN syntaxes.

  • SELECT ... FROM table1 NATURAL INNER JOIN table2 performs an INNER JOIN on all identically named columns across the two tables. In multi-join queries, you can specify only one NATURAL join and it must be the first join.

    This query performs the same operation as in the previous syntaxes, assuming that CompanyID is the only column that appears in both tables. If the tables include multiple identically named columns, then the query also joins on those columns before returning the matching results.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    NATURAL INNER JOIN Sample.Company AS C
    

    Example: Join on Identically Named Columns Across Two Tables

  • SELECT ... FROM table1 NATURAL JOIN table2 is equivalent to the NATURAL INNER JOIN syntax.

Left Outer Join

A LEFT OUTER JOIN returns all rows from the first table and any rows from the second table that match rows from the first table. In the joined table, non-matching rows of columns from the second table are populated with null values. For example:

SELECT Table1.Letter, Table2.Number
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.ID = Table2.ID

Left: Table1 - ID:1-2-3, Letter:A-B-C. Table2 - ID:3-4-5, Number:3-4-5. Center: Join - Letter:A-B-C, Number:null-null-3

  • SELECT ... FROM table1 LEFT OUTER JOIN table2 ON condition returns all rows from table1 and joins them with any rows from table2 that satisfy the condition expression specified in the ON clause.

    This query returns the names of employees and their companies, joining data from the Sample.Employee table (aliased to E) and Sample.Company table (aliased to C). It returns all E.Name values but only C.Name values in rows where the CompanyID column of both tables have matching values. In non-matching rows, C.Name values are set to NULL.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    LEFT OUTER JOIN Sample.Company AS C
    ON E.CompanyID = C.CompanyID
    Note:

    Alternatively, instead of using the explicit LEFT OUTER JOIN syntax, you can use the more succinct implicit join specified by the arrow syntax (–>) in the SELECT statement. For example, this query is equivalent to the previous query:

    SELECT Name, CompanyID->Name
    FROM Sample.Employee

    This syntax assumes that the CompanyID column from Sample.Employee references the IDs of rows in the Sample.Company table, which contains the Name column that is being joined. For more details on working with implicit joins, see Implicit Joins.

    Examples:

  • SELECT ... FROM table1 LEFT OUTER JOIN table2 USING (column, column2, ...) returns all rows from table1 and any rows from table2 that have matching values in the specified columns. The columns must appear in both tables.

    This query performs the same join as in the previous syntax, because both columns have a CompanyID column that they can join on.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    LEFT OUTER JOIN Sample.Company AS C
    USING (CompanyID)

    Example: Join on Identically Named Columns Across Two Tables

  • SELECT ... FROM table1 LEFT JOIN table2 ... is equivalent to the LEFT OUTER JOIN syntaxes.

  • SELECT ... FROM table1 NATURAL LEFT OUTER JOIN table2 performs a LEFT OUTER JOIN on all identically named columns across the two tables. If an expression contains multiple joins, specify the NATURAL join first. A NATURAL join does not merge columns that have the same name.

    This query performs the same operation as in the previous syntaxes, assuming that CompanyID is the only column that appears in both tables. If the tables include multiple identically named columns, then the query performs an additional join per column.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    NATURAL LEFT OUTER JOIN Sample.Company AS C
    

    Example: Join on Identically Named Columns Across Two Tables

  • SELECT ... FROM table1 NATURAL LEFT JOIN table2 is equivalent to the NATURAL LEFT OUTER JOIN syntax.

Right Outer Join

A RIGHT OUTER JOIN returns all rows from the second table and any rows from the first table that match rows from the second table. In the joined table, non-matching rows of columns from the first table are populated with null values. For example:

SELECT Table1.Letter, Table2.Number
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.ID = Table2.ID

Left: Table1 - ID:1-2-3, Letter:A-B-C. Table2 - ID:3-4-5, Number:3-4-5. Center: Join - Letter:null-null-C, Number:3-4-5

  • SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON condition returns all rows from table2 and joins them with any rows from table1 that satisfy the condition expression specified in the ON clause.

    This query returns the names of employees and their companies, joining data from the Sample.Employee table (aliased to E) and Sample.Company table (aliased to C). It returns all C.Name values but only E.Name values in rows where the CompanyID column of both tables have matching values. In non-matching rows, E.Name values are set to NULL.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    LEFT OUTER JOIN Sample.Company AS C
    ON E.CompanyID = C.CompanyID

    Examples:

  • SELECT ... FROM table1 RIGHT OUTER JOIN table2 USING (column, column2, ...) returns all rows from table2 and any rows from table1 that have matching values in the specified columns. The columns must appear in both tables.

    This query performs the same join as in the previous syntax, because both columns have a CompanyID column that they can join on.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    RIGHT OUTER JOIN Sample.Company AS C
    USING (CompanyID)

    Example: Join on Identically Named Columns Across Two Tables

  • SELECT ... FROM table1 RIGHT JOIN table2 ... is equivalent to the RIGHT OUTER JOIN syntaxes.

  • SELECT ... FROM table1 NATURAL RIGHT OUTER JOIN table2 performs a RIGHT OUTER JOIN on all identically named columns across the two tables. If an expression contains multiple joins, specify the NATURAL join first. A NATURAL join does not merge columns that have the same name.

    This query performs the same operation as in previous syntaxes, provided that CompanyID is the only column that appears in both tables. If the tables include multiple identically named columns, then the query performs one join per column.

    SELECT E.Name, C.Name
    FROM Sample.Employee AS E
    NATURAL RIGHT OUTER JOIN Sample.Company AS C
    

    Example: Join on Identically Named Columns Across Two Tables

  • SELECT ... FROM table1 NATURAL RIGHT JOIN table2 is equivalent to the NATURAL RIGHT OUTER JOIN syntax.

Full Outer Join

A FULL OUTER JOIN joins all rows from both tables. In the joined table, non-matching rows of columns from either table are populated with null values. For example:

SELECT Table1.Letter, Table2.Number
FROM Table1
FULL OUTER JOIN Table2
ON Table1.ID = Table2.ID

Left: Table1-ID:1-2-3, Letter:A-B-C. Table2-ID:3-4-5, Number:3-4-5. Center: Join-Letter:A-B-C-null-null, Number:null-null-3-4

  • SELECT ... FROM table1 FULL OUTER JOIN table2 ON condition returns all rows of table1 and table2 that match the specified condition.

    This query returns the names of people and the companies that they work for, joining data from Sample.Person and Sample.Company. For each row, if the person specified by PersonID is missing either Company or Person column data, that column value is NULL.

    SELECT P.Name, E.Company
    FROM Sample.Person AS P
    INNER JOIN Sample.Employee AS E
    ON P.PersonID = E.PersonID

    Example: Join Table Data Using Inner and Outer Joins

  • SELECT ... FROM table1 FULL JOIN table2 ON condition is equivalent to the FULL OUTER JOIN syntax.

Full outer joins do not support the USING or NATURAL syntaxes.

Cross Join

A CROSS JOIN crosses every row of the first table with every row of the second table. For example:

SELECT Table1.Letter, Table2.Number
FROM Table1
CROSS JOIN Table2

Left: Table1 - ID:1-2, Letter:A-B. Table2 - ID:1-2, Number:1-2. Center: Join -Letter:A-A-B-B, Number:1-2-1-2

  • SELECT ... FROM table1 CROSS JOIN table2 crosses every row of table1 with every row of table2, resulting in a large, logically comprehensive table with much data duplication. Usually this join is performed by providing a comma-separated list of tables in the FROM clause, then using the WHERE clause to specify restrictive conditions.

    This query returns a row for each combination of rows in Sample.LettersAtoZ and Sample.Numbers1to10.

    SELECT * FROM Sample.LettersAtoZ CROSS JOIN Sample.Numbers1to10
    

    This query is equivalent to the previous query.

    SELECT * FROM Sample.LettersAtoZ, Sample.Numbers1to10

Attempting to perform a cross join involving a local table and an external table linked through an ODBC or JDBC gateway connection (for example, FROM Sample.Person, Mylink.Person) results in an SQLCODE -161 error. To perform this cross join, you must specify the linked table as a subquery. For example: FROM Sample.Person,(SELECT * FROM Mylink.Person).

The explicit use of the JOIN keyword has higher precedence than specifying a cross join using comma syntax. InterSystems IRIS® thus interprets t1,t2 JOIN t3 as t1,(t2 JOIN t3).

Arguments

table1, table2

Names of the tables being joined. Specify the first table, table1, after the FROM keyword. Specify the second table, table2, after the JOIN keyword.

  • In a join with an ON clause, you can specify tables, views, or subqueries for either operand of the join.

  • In a NATURAL or USING join, you can specify only simple base table references (not views or subqueries) for either operand of the join.

Both table1 and table2 support table aliases.

condition

One or more condition expression predicates, specified in the ON clause to restrict the rows being joined. JOIN supports most of the predicates supported by InterSystems SQL. However, you cannot use the FOR SOME %ELEMENT collection predicate to limit a join operation.

You can associate multiple condition expressions using AND, OR, and NOT logical operators. AND takes precedence over OR. To nest and group condition expressions, use parentheses. For example:

SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
ON Patient.DocID = Doctor.DocID AND
   NOT (Doctor.State = 'NH' OR Doctor.State = 'MA')

condition has the following restrictions:

  • condition can reference only tables explicitly specified in the ANSI keyword JOIN operation. Referencing tables specified in the FROM clause results in an SQLCODE -23 error.

  • condition can reference only columns that are in the operands of the JOIN. Syntax precedence in multiple joins can cause the ON clause to fail. For example, this query fails because t1 and t3 are not operands of a join. t1 joins with the result set of t2 JOIN t3.

    SELECT * FROM t1,t2 JOIN t3 ON t1.p1=t3.p3

    Either of the following changes in syntax result in the successful execution of this query:

    SELECT * FROM t1 CROSS JOIN t2 JOIN t3 ON t1.p1=t3.p3
    SELECT * FROM t2,t1 JOIN t3 ON t1.p1=t3.p3
  • In OUTER JOIN clauses, if all the conditions affecting a table use comparisons that can pass null values, and that table is itself a target of an outer join, this can result in an SQLCODE -94 error. For example, this LEFT OUTER JOIN query is invalid:

    SELECT * FROM Table1
    LEFT OUTER JOIN Table2 ON Table1.k = Table2.k
    LEFT OUTER JOIN Table3 ON COALESCE(Table1.k,Table2.k) = Table3.k

    Similar examples using FULL OUTER JOIN or RIGHT OUTER JOIN also have this restriction.

column

A column name, or comma-separated list of columns names, specified in the USING clause to join columns with the same names in both tables. Enclose the column list in parentheses. Only explicit column names are permitted. You cannot specify the %ID row that references the auto-generated RowID column. Duplicate column names are ignored. Columns with the same name are not merged.

Examples

Join Table Data Using Inner and Outer Joins

In this example, you create two sample tables, combine the data into one table using different INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN syntaxes, and compare the different joined results.

Create Tables

This examples uses two tables:

  • Sample.HighestPeaks — Elevation (in feet) of mountains with the highest peaks, worldwide.

  • Sample.Himalayas — Names of mountains in the Himalayas.

Although not specified in this example, assume that the MountainID and PeakID columns of both tables are foreign key references to a larger mountain database. Therefore, rows with the same ID column value in both tables refer to the same mountain.

Create the Sample.HighestPeaks table and insert three rows of data. Display the table.

CREATE TABLE Sample.HighestPeaks (
  PeakID INTEGER UNIQUE NOT NULL,
  Elevation INTEGER NOT NULL)
INSERT INTO Sample.HighestPeaks VALUES (1, 29032)
INSERT INTO Sample.HighestPeaks VALUES (2, 28251)
INSERT INTO Sample.HighestPeaks VALUES (3, 28169)
SELECT * FROM Sample.HighestPeaks
PeakID Elevation
1 29032
2 28251
3 28169

Create the Sample.Himalayas table and insert three rows of data. The omitted MountainID of 2 is intentional. Assume that the mountain with an ID of 2 is not in the Himalayas. Display the table.

CREATE TABLE Sample.Himalayas (
  MountainID INTEGER UNIQUE NOT NULL,
  Name VARCHAR(30) UNIQUE NOT NULL)
INSERT INTO Sample.Himalayas VALUES (1, 'Everest')
INSERT INTO Sample.Himalayas VALUES (3, 'Kangchenjunga')
INSERT INTO Sample.Himalayas VALUES (4, 'Lhotse')
SELECT * FROM Sample.Himalayas
MountainID Name
1 Everest
3 Kangchenjunga
4 Lhotse

Perform INNER JOIN

Combine the mountain name and elevation data from the two tables by using an INNER JOIN, joining them on the MountainID and PeakID columns. The joined table includes data only for the mountains with IDs of 1 and 3, because these IDs appear in both tables.

SELECT H.Name, P.Elevation
FROM Sample.Himalayas AS H
INNER JOIN Sample.HighestPeaks as P
ON H.MountainID = P.PeakID
Name Elevation
Everest 29032
Kangchenjunga 28169

Perform LEFT OUTER JOIN

Combine the name and elevation data by using a LEFT OUTER JOIN, joining them on the MountainID and PeakID columns. The joined table includes all rows from the first table (Sample.Himalayas) but only the rows from the second table (Sample.HighestPeaks) with PeakID values of 1 and 3, which also appear in the MountainID column of the first table. The missing elevation of the mountain Lhotse takes a NULL value.

SELECT H.Name, P.Elevation
FROM Sample.Himalayas AS H
LEFT OUTER JOIN Sample.HighestPeaks as P
ON H.MountainID = P.PeakID
Name Elevation
Everest 29032
Kangchenjunga 28169
Lhotse  

Perform RIGHT OUTER JOIN

Combine the name and elevation data by using a RIGHT OUTER JOIN, joining them on the MountainID and PeakID columns. The joined table includes all rows from the second table (Sample.HighestPeaks) but only the rows from the first table (Sample.Himalayas) with MountainID values of 1 and 3, which also appear in the PeakID column of the second table. The missing name of the mountain with an elevation of 28,251 feet akes a NULL value.

SELECT H.Name, P.Elevation
FROM Sample.Himalayas AS H
RIGHT OUTER JOIN Sample.HighestPeaks as P
ON H.MountainID = P.PeakID
Name Elevation
Everest 29032
  28251
Kangchenjunga 28169

Perform FULL OUTER JOIN

Combine the name and elevation data by using a FULL OUTER JOIN, joining them on the MountainID and PeakID columns. The joined table includes all rows from both tables. The missing mountain names and elevations take NULL values.

SELECT H.Name, P.Elevation
FROM Sample.Himalayas AS H
FULL OUTER JOIN Sample.HighestPeaks as P
ON H.MountainID = P.PeakID
Name Elevation
Everest 29032
Kangchenjunga 28169
Lhotse  
  28251

Delete Tables

Delete the sample tables when you are done.

DROP TABLE Sample.Himalayas
DROP TABLE Sample.HighestPeaks

Join on Identically Named Columns Across Two Tables

This example shows the different syntaxes you can use when joining columns that have identical names across the two tables.

Consider two tables:

  • Patient — Contains information about patients, including an ID code for the patient’s primary doctor, DocID.

  • Doctor — Contains information about doctors, including their ID code, DocID.

This INNER JOIN returns the patient and doctor names.

SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
ON Patient.DocID = Doctor.DocID

Because the joining columns have the same name in both tables (DocID), you can replace the ON clause with a USING clause. With this syntax, you specify only the column, in parentheses, and omit the table names.

SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
USING (DocID)

You can also specify the USING clause with a LEFT OUTER JOIN or RIGHT OUTER JOIN, but the FULL OUTER JOIN is not supported.

SELECT Patient.PName, Doctor.DName
FROM Patient
RIGHT OUTER JOIN Doctor
USING (DocID)
SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
USING (DocID)

If DocID is the only identically named column across the two tables, then you can simplify further and use the NATURAL JOIN syntax.

SELECT Patient.PName, Doctor.DName
FROM Patient
NATURAL INNER JOIN Doctor
SELECT Patient.PName, Doctor.DName
FROM Patient
NATURAL LEFT OUTER JOIN Doctor
SELECT Patient.PName, Doctor.DName
FROM Patient
NATURAL RIGHT OUTER JOIN Doctor

If the two tables contain other identical columns, then NATURAL JOIN also links those columns in the join operation. For greater specificity over the columns being joined, use the USING or ON clauses. Full outer joins do not support NATURAL JOINs.

Set Additional Restrictions on Joined Data

This example shows how the setting of additional restrictions can affect the returned data from various joins.

Consider two tables:

  • Patient — Contains information about patients, including an ID code for the patient’s primary doctor, DocID.

  • Doctor — Contains information about doctors, including their ID code, DocID.

This INNER JOIN returns the patient and doctor names of doctors who are over 45 years old.

SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
ON Patient.DocID = Doctor.DocID AND Doctors.Age > 45

Performing a LEFT OUTER JOIN of the same query does not eliminate NULL values in the non-matching rows of the table being joined. For example, this LEFT OUTER JOIN still returns NULL values in the Doctor.DName column.

SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
ON Patient.DocID = Doctor.DocID AND Doctors.Age > 45

You can eliminate NULL values by moving the age condition into the WHERE clause, which processes after the join operation. However, this effectively converts the query into an INNER JOIN. For example, this query is equivalent to the first query in this example:

SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
ON Patient.DocID = Doctor.DocID
WHERE Doctors.Age > 45

Adding an IS NULL clause preserves the original LEFT OUTER JOIN behavior but is more verbose than the original LEFT OUTER JOIN query.

SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
ON Patient.DocID = Doctor.DocID
WHERE Doctors.Age > 45 AND Doctors.Age IS NULL

This behavior is similar for RIGHT OUTER JOIN operations. In a FULL OUTER JOIN, specifying conditions does not affect which rows are returned, because the operation returns all rows from both tables, regardless of matches.

Performance

Query Optimizer Effect on Joins

To maximize performance of join operations, the SQL optimizer might not join tables in the order in which they are specified. Instead, the optimizer determines the table join order based on statistics it gathers on the table, such as Tune Table.

In most cases, the SQL optimizer strategy provides optimal results. However, to override the default optimization strategy for a specific query, you can specify Query Optimization Options immediately after the FROM keyword.

  • %INORDER, %FIRSTTABLE, and %STARTTABLE — For complex queries containing multiple joins, these options explicitly set the order in which to join tables. You cannot use these keywords with a cross join or a right outer join Attempting to do so results in an SQLCODE -34 error.

  • %NOFLATTEN — This option disables subquery flattening, which converts certain subqueries to explicit joins. When the number of subqueries is small, subquery flattening can substantially improve join performance. As the number of subqueries increases, however, subquery flattening might start to degrade performance and might require disabling using this keyword.

ON Clause Indexing

Specifying indexes on columns referenced in the ON clause of a join can substantially improve query performance. An ON clause can use an existing index that satisfies only some of the join conditions. An ON clause specifying conditions on multiple columns can use an index containing only a subset of those columns as subscripts to partially satisfy the join. InterSystems IRIS tests the join condition on the remaining columns directly from the table.

The collation type of a field referenced in an ON clause should match the collation type that it has in the corresponding index. A collation type mismatch can cause an index to not be used. However, if a join condition is on a column with %EXACT collation, but only an index on the collated column value is available, InterSystems IRIS can use that index to limit the rows to be checked for the exact value. For more details on collation type matching, see Index Collation.

To disable an index for an ON clause condition, preface it with the %NOINDEX keyword. For more details on indexes and performance, see Using Indexes in Query Processing and Index Optimization Options.

Alternatives

InterSystems IRIS supports two formats for representing outer joins:

  1. (Recommended) The ANSI standard syntax: LEFT OUTER JOIN and RIGHT OUTER JOIN. SQL Standard syntax puts the outer join in the FROM clause of the SELECT statement, rather than the WHERE clause, as shown in the following example:

    SELECT table1.columnA, table2.columnB
    FROM table1
    LEFT OUTER JOIN table2
    ON (table1.columnX = table2.columnY)
    
  2. The ODBC Specification outer join extension syntax, using the escape-syntax {oj joinExpression }, where joinExpression is any ANSI standard join syntax.

A join with an ON clause can use only the ANSI join keyword syntax.

See Also

FeedbackOpens in a new tab