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 |