SuiteQL Join Types

SuiteQL supports several SQL join types. You can use these join types to customize the results you receive from your SuiteQL queries. By default, when you join record types in SuiteAnalytics Workbook, the join performed is a left outer join. This join type is appropriate for many use cases, but in some situations, you may want to use a different join type to obtain a more customized result set.

For more information about joining record types in SuiteAnalytics Workbook, see Guidelines for Joining Record Types in SuiteAnalytics Workbook. For more information about SQL joins in general, see Join (SQL).

SuiteQL supports the following SQL join types:

• Cross Joins — A cross join is used to produce all row combinations between two tables. The result is known as the Cartesian product of the rows in both tables.

• Inner Joins — An inner join is used to produce row combinations between two tables based on a common value. The results include only those rows that share the common value.

• Outer Joins — An outer join is also used to produce row combinations between two tables based on a common value. The results include all rows from one or both tables, depending on the type of outer join.

To demonstrate each join type, the examples in this topic use the following simplified database tables. These tables include some of the fields that appear on customer and employee records in NetSuite. You can use the Records Catalog to determine the fields you can use in SuiteQL queries. For more information, see Records Catalog Overview.

customer table
entityid	email	salesrep
Big Computers	[e-mail]	5
Fantastic Laptops	[e-mail]	3
PennyPack Systems	[e-mail]	NULL
Tall Manufacturing	[e-mail]	1
Vision Corporation	[e-mail]	1

employee table
id	entityid
1	Allie Anderson
2	Billy Brown
3	Carol Connors
4	David Davis
5	Eugene Evans

Cross Joins

A cross join is the Cartesian product of the rows in two tables. It combines each row in the first table with each row in the second table. This type of join is computationally expensive, so you should use this type of join rarely. You can filter the results using the WHERE keyword, which produces an inner join.

An explicit cross join uses the CROSS JOIN keywords. Here is an example of an explicit cross join:

            SELECT *
FROM customer CROSS JOIN employee 

          

This join is equivalent to the following implicit cross join, which does not use the CROSS JOIN keywords:

            SELECT *
FROM customer, employee 

          

When using SuiteQL with SuiteAnalytics Connect, the CROSS JOIN keywords are not supported. To perform a cross join, you can use the implicit notation. You can also use a full outer join to simulate a cross join, as follows:

            SELECT *
FROM customer
FULL OUTER JOIN employee ON 1=1 

          

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

customer.entityid	customer.email	customer.salesrep	employee.id	employee.entityid
Big Computers	[e-mail]	5	1	Allie Anderson
Big Computers	[e-mail]	5	2	Billy Brown
Big Computers	[e-mail]	5	3	Carol Connors
Big Computers	[e-mail]	5	4	David Davis
Big Computers	[e-mail]	5	5	Eugene Evans
Fantastic Laptops	[e-mail]	3	1	Allie Anderson
Fantastic Laptops	[e-mail]	3	2	Billy Brown
...	...	...	...	...

The first row in the first table is combined with each row in the second table, the second row in the first table is combined with each row in the second table, and so on. Additional result rows have been omitted from this table, but the same pattern continues for the remaining result rows.

Inner Joins

An inner join combines rows in the first table with rows in the second table based on a common value. The results of an inner join include only those rows that share the common column (or field) value.

An explicit inner join uses the INNER JOIN and ON keywords. Here is an example of an explicit inner join:

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

          

This explicit join is equivalent to the following implicit inner join, which uses the WHERE keyword instead of the INNER JOIN and ON keywords:

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

          

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

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

In this example inner join, a result row is included only if the value of the salesrep field in the customer table matches the value of the id field in the employee table.

The following diagram illustrates an inner join:

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 does not 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 is 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 is 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 does not find any matching row in the first table.

An explicit right outer join uses the RIGHT OUTER JOIN and ON keywords. Here is 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 is 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 is 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 is 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
• Using SuiteQL
• SuiteQL Syntax and Examples
• SuiteQL Limitations and Exceptions
• SuiteQL Supported and Unsupported Functions
• SuiteQL Supported Built-in Functions

General Notices