Joining datasets in NetSuite Analytics Warehouse (NSAW) allows you to combine information from multiple sources into a single dataset for reporting and dashboards. When you add multiple record types, NSAW uses SQL-style joins to determine how the data relates.
Inner Join
- Returns only the records that have matching values in both datasets.
- Best when you want to see only transactions that exist in both sources.
- Example: Joining Invoice and sales order dataset with inner join of the location, shows the data only when locations are same for invoices and sales orders.
Outer Join (Full Outer Join)
- Definition: Returns all records from both datasets, whether they match or not.
- Use Case: Useful when you want a complete view of all records, even if some don’t match.
- Example: Joining Sales Orders with Return Authorizations will show all transactions, even if some don’t have a corresponding record.
Left Join (Left Outer Join)
- Definition: Returns all records from the left (primary) dataset, plus matching records from the right dataset. If no match exists, null values are shown.
- Use Case: Helpful when you want to see all customers, even those without invoices.
- Example: Joining Customers (left) with Invoices (right) will list all customers, with invoice details where available.
Right Join (Right Outer Join)
- Definition: Returns all records from the right (secondary) dataset, plus matching records from the left dataset.
- Use Case: Less common, but useful when the secondary dataset is the focus.
- Example: Joining Invoices (right) with Customers (left) ensures all invoices are listed, even if some customers are missing from the left dataset.