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 |
|
salesrep |
Big Computers |
bigcomputers@example.com |
5 |
Fantastic Laptops |
fantasticlaptops@example.com |
3 |
PennyPack Systems |
pennypacksystems@example.com |
NULL |
Tall Manufacturing |
tallmanufacturing@example.com |
1 |
Vision Corporation |
visioncorporation@example.com |
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. |
customer.email |
customer. |
employee.id |
employee.entityid |
---|---|---|---|---|
Big Computers |
bigcomputers@ |
5 |
1 |
Allie Anderson |
Big Computers |
bigcomputers@ |
5 |
2 |
Billy Brown |
Big Computers |
bigcomputers@ |
5 |
3 |
Carol Connors |
Big Computers |
bigcomputers@ |
5 |
4 |
David Davis |
Big Computers |
bigcomputers@ |
5 |
5 |
Eugene Evans |
Fantastic Laptops |
fantasticlaptops@ |
3 |
1 |
Allie Anderson |
Fantastic Laptops |
fantasticlaptops@ |
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 |
bigcomputers@example.com |
Eugene Evans |
Fantastic Laptops |
fantasticlaptops@example.com |
Carol Connors |
Tall Manufacturing |
tallmanufacturing@example.com |
Allie Anderson |
Vision Corporation |
visioncorporation@example.com |
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
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 |
bigcomputers@example.com |
Eugene Evans |
Fantastic Laptops |
fantasticlaptops@example.com |
Carol Connors |
PennyPack Systems |
pennypacksystems@example.com |
NULL |
Tall Manufacturing |
tallmanufacturing@example.com |
Allie Anderson |
Vision Corporation |
visioncorporation@example.com |
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 |
tallmanufacturing@example.com |
Allie Anderson |
Vision Corporation |
visioncorporation@example.com |
Allie Anderson |
NULL |
NULL |
Billy Brown |
Fantastic Laptops |
fantasticlaptops@example.com |
Carol Connors |
NULL |
NULL |
David Davis |
Big Computers |
bigcomputers@example.com |
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 |
bigcomputers@example.com |
Eugene Evans |
Fantastic Laptops |
fantasticlaptops@example.com |
Carol Connors |
PennyPack Systems |
pennypacksystems@example.com |
NULL |
Tall Manufacturing |
tallmanufacturing@example.com |
Allie Anderson |
Vision Corporation |
visioncorporation@example.com |
Allie Anderson |
NULL |
NULL |
Billy Brown |
NULL |
NULL |
David Davis |
The following diagram illustrates a full outer join: