Outer Joins

Similar to an inner join, an outer join combines rows in the first table with rows in the second table based on a common value. However, an outer join includes all of the rows in one or both tables in the results, depending on the type of outer join being performed.

SuiteQL supports the following outer join types:

• Left Outer Joins

• Right Outer Joins

• Full Outer Joins

Left Outer Joins

A left outer join combines all rows in the first (left) table with rows in the second (right) table based on a common value. The results of this join always contain every row in the first table, even if the join condition doesn't find any matching row in the second table.

This join is the default join type when you join record types in SuiteAnalytics Workbook. For more information, see Guidelines for Joining Record Types in SuiteAnalytics Workbook.

An explicit left outer join uses the LEFT OUTER JOIN and ON keywords. Here's an example of an explicit left outer join:

            SELECT customer.entityid, customer.email, employee.entityid
FROM customer
LEFT OUTER JOIN employee ON customer.salesrep = employee.id 

          

The OUTER keyword is optional, so this explicit join is equivalent to the following:

            SELECT customer.entityid, customer.email, employee.entityid
FROM customer
LEFT JOIN employee ON customer.salesrep = employee.id 

          

This join is also equivalent to the following implicit left outer join, which uses the (+) syntax:

            SELECT customer.entityid, customer.email, employee.entityid
FROM customer, employee
WHERE customer.salesrep = employee.id(+) 

          

Using the example database tables, these joins produce five result rows:

customer.entityid	customer.email	employee.entityid
Big Computers	[e-mail]	Eugene Evans
Fantastic Laptops	[e-mail]	Carol Connors
PennyPack Systems	[e-mail]	NULL
Tall Manufacturing	[e-mail]	Allie Anderson
Vision Corporation	[e-mail]	Allie Anderson

In this example left outer join, a row for PennyPack Systems is included in the results even though there's no matching value for that row in the employee table.

The following diagram illustrates a left outer join:

Right Outer Joins

A right outer join combines rows in the first (left) table with all rows in the second (right) table based on a common value. This join is similar to a left outer join, but the table order is reversed. The results of this join always contain every row in the second table, even if the join condition doesn't find any matching row in the first table.

An explicit right outer join uses the RIGHT OUTER JOIN and ON keywords. Here's an example of an explicit right outer join:

            SELECT customer.entityid, customer.email, employee.entityid
FROM customer
RIGHT OUTER JOIN employee ON customer.salesrep = employee.id 

          

The OUTER keyword is optional, so this explicit join is equivalent to the following:

            SELECT customer.entityid, customer.email, employee.entityid
FROM customer
RIGHT JOIN employee ON customer.salesrep = employee.id 

          

There's no implicit version of a right outer join in SuiteQL.

Using the example database tables, these joins produce six result rows:

customer.entityid	customer.email	employee.entityid
Tall Manufacturing	[e-mail]	Allie Anderson
Vision Corporation	[e-mail]	Allie Anderson
NULL	NULL	Billy Brown
Fantastic Laptops	[e-mail]	Carol Connors
NULL	NULL	David Davis
Big Computers	[e-mail]	Eugene Evans

In this example right outer join, rows for Billy Brown and David Davis are included in the results even though there are no matching values for those rows in the customer table.

The following diagram illustrates a right outer join:

Full Outer Joins

A full outer join combines the effects of a left outer join and a right outer join. A full outer join combines all rows in the first (left) table with all rows in the second (right) table based on a common value. The results of this join always contain every row in the first table and every row in the second table. Depending on the relationship between the tables, the results may include the same table row more than one time.

An explicit full outer join uses the FULL OUTER JOIN and ON keywords. Here's an example of an explicit full outer join:

            SELECT customer.entityid, customer.email, employee.entityid
FROM customer
FULL OUTER JOIN employee ON customer.salesrep = employee.id 

          

There's no implicit version of a full outer join in SuiteQL.

Using the example database tables, this join produces seven result rows:

customer.entityid	customer.email	employee.entityid
Big Computers	[e-mail]	Eugene Evans
Fantastic Laptops	[e-mail]	Carol Connors
PennyPack Systems	[e-mail]	NULL
Tall Manufacturing	[e-mail]	Allie Anderson
Vision Corporation	[e-mail]	Allie Anderson
NULL	NULL	Billy Brown
NULL	NULL	David Davis

The following diagram illustrates a full outer join:

Related Topics

• SuiteQL Join Types
• Cross Joins
• Inner Joins

General Notices