Joins

Learn how to combine data from multiple tables using SQL JOINs in SuiteQL.

---

Understanding Joins

Joins combine rows from two or more tables based on a related column between them.

---

Join Types Overview

Join Type	Description	Returns
INNER JOIN	Matching rows only	Only rows that match in both tables
LEFT JOIN	All from left + matching from right	All left rows, NULL if no match
RIGHT JOIN	All from right + matching from left	All right rows, NULL if no match
FULL OUTER JOIN	All from both	All rows, NULL where no match
CROSS JOIN	Cartesian product	Every combination of rows
SELF JOIN	Table joined to itself	Hierarchical relationships

---

INNER JOIN

Returns only rows that have matching values in both tables.

Syntax

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column

Test Case: Transactions with Customer Names

-- Get invoices with customer information
SELECT
    t.id,
    t.tranid,
    t.trandate,
    t.amount,
    c.companyname,
    c.email
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'CustInvc'
ORDER BY t.trandate DESC
LIMIT 20

Sample Result:

id	tranid	trandate	amount	companyname	email
501	INV-501	2024-12-15	5000.00	Acme Corp	acme@test.com
502	INV-502	2024-12-14	3500.00	Beta Inc	beta@test.com
503	INV-503	2024-12-13	8200.00	Acme Corp	acme@test.com

Test Case: Transaction Lines with Items

-- Get sales order lines with item details
SELECT
    t.tranid AS order_number,
    tl.linesequencenumber AS line,
    i.itemid AS item_code,
    i.displayname AS item_name,
    tl.quantity,
    tl.rate,
    tl.amount
FROM transaction t
INNER JOIN transactionline tl ON t.id = tl.transaction
INNER JOIN item i ON tl.item = i.id
WHERE t.type = 'SalesOrd'
  AND t.id = 12345  -- Specific order
ORDER BY tl.linesequencenumber

Sample Result:

order_number	line	item_code	item_name	quantity	rate	amount
SO-001	1	WIDGET-A	Widget Type A	10	25.00	250.00
SO-001	2	WIDGET-B	Widget Type B	5	50.00	250.00
SO-001	3	SERVICE-1	Installation	1	100.00	100.00

---

LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from the left table, and matching rows from the right table. NULL if no match.

Illustration

Test Case: All Customers with Optional Orders

-- All customers, with their order count (including those with no orders)
SELECT
    c.id,
    c.companyname,
    COUNT(t.id) AS order_count,
    NVL(SUM(t.amount), 0) AS total_orders
FROM customer c
LEFT JOIN transaction t ON c.id = t.entity AND t.type = 'SalesOrd'
WHERE c.isinactive = 'F'
GROUP BY c.id, c.companyname
ORDER BY order_count DESC
LIMIT 20

Sample Result:

id	companyname	order_count	total_orders
100	Acme Corp	45	125000.00
101	Beta Inc	32	87500.00
102	New Customer	0	0

Test Case: Find Customers WITHOUT Orders

-- Customers who have never placed an order
SELECT
    c.id,
    c.companyname,
    c.datecreated
FROM customer c
LEFT JOIN transaction t ON c.id = t.entity AND t.type = 'SalesOrd'
WHERE c.isinactive = 'F'
  AND t.id IS NULL  -- No matching transaction
ORDER BY c.datecreated DESC

Key Point: t.id IS NULL finds rows where the JOIN found no match.

---

RIGHT JOIN

Returns ALL rows from the right table, and matching rows from the left table.

note

RIGHT JOIN is less common. You can usually rewrite as LEFT JOIN by swapping table order.

Test Case: All Items with Optional Sales

-- All items and their sales (including unsold items)
SELECT
    i.id,
    i.itemid,
    i.displayname,
    COUNT(tl.id) AS times_sold,
    NVL(SUM(tl.quantity), 0) AS total_qty_sold
FROM transactionline tl
RIGHT JOIN item i ON tl.item = i.id
WHERE i.isinactive = 'F'
GROUP BY i.id, i.itemid, i.displayname
ORDER BY times_sold DESC
LIMIT 20

---

FULL OUTER JOIN

Returns ALL rows from both tables. NULL where there's no match on either side.

Test Case: Complete Customer-Order Analysis

-- All customers and all orders (showing orphans on both sides)
SELECT
    c.id AS customer_id,
    c.companyname,
    t.id AS transaction_id,
    t.tranid
FROM customer c
FULL OUTER JOIN transaction t ON c.id = t.entity AND t.type = 'SalesOrd'
WHERE c.companyname IS NULL  -- Orders without valid customer
   OR t.id IS NULL           -- Customers without orders
LIMIT 50

---

Multiple Joins

Combine three or more tables in a single query.

Test Case: Complete Order Report

-- Full order details: order + customer + lines + items
SELECT
    t.tranid AS order_number,
    t.trandate,
    c.companyname AS customer,
    c.email,
    i.itemid AS item_code,
    i.displayname AS item_name,
    tl.quantity,
    tl.rate,
    tl.amount,
    e.entityid AS sales_rep
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
INNER JOIN transactionline tl ON t.id = tl.transaction
INNER JOIN item i ON tl.item = i.id
LEFT JOIN employee e ON t.salesrep = e.id
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
ORDER BY t.trandate DESC, t.tranid, tl.linesequencenumber
LIMIT 100

Explanation:

• INNER JOIN customer - Only orders with valid customers
• INNER JOIN transactionline - Only orders with lines
• INNER JOIN item - Only lines with valid items
• LEFT JOIN employee - Include orders even if no sales rep assigned

---

Self Join

A table joined to itself. Useful for hierarchical data.

Test Case: Employee Hierarchy

-- Employees with their managers
SELECT
    e.id,
    e.entityid AS employee_name,
    e.title AS employee_title,
    m.entityid AS manager_name,
    m.title AS manager_title
FROM employee e
LEFT JOIN employee m ON e.supervisor = m.id
WHERE e.isinactive = 'F'
ORDER BY m.entityid, e.entityid

Sample Result:

id	employee_name	employee_title	manager_name	manager_title
10	John Smith	Developer	Jane Doe	IT Manager
11	Bob Johnson	Developer	Jane Doe	IT Manager
12	Jane Doe	IT Manager	NULL	NULL

Test Case: Item Parent-Child

-- Items with their parent items
SELECT
    child.id,
    child.itemid AS item,
    child.displayname,
    parent.itemid AS parent_item
FROM item child
LEFT JOIN item parent ON child.parent = parent.id
WHERE child.isinactive = 'F'
ORDER BY parent.itemid, child.itemid

---

Cross Join

Creates a Cartesian product - every combination of rows.

warning

Cross joins can produce very large result sets. Use with caution!

Test Case: Generate Date-Category Matrix

-- Create a matrix of months and categories for reporting
WITH Months AS (
    SELECT 1 AS month_num, 'January' AS month_name FROM DUAL
    UNION ALL SELECT 2, 'February' FROM DUAL
    UNION ALL SELECT 3, 'March' FROM DUAL
    -- ... more months
),
Categories AS (
    SELECT DISTINCT category FROM item WHERE category IS NOT NULL
)
SELECT
    m.month_name,
    c.category
FROM Months m
CROSS JOIN Categories c
ORDER BY m.month_num, c.category

---

Common NetSuite Join Patterns

Transaction + Customer

SELECT t.*, c.companyname, c.email
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'CustInvc'

Transaction + Lines + Items

SELECT t.tranid, tl.*, i.itemid, i.displayname
FROM transaction t
INNER JOIN transactionline tl ON t.id = tl.transaction
INNER JOIN item i ON tl.item = i.id
WHERE t.type = 'SalesOrd'

Transaction + Vendor

SELECT t.tranid, t.amount, v.companyname
FROM transaction t
INNER JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill'

Customer + Primary Contact

SELECT c.companyname, con.firstname, con.lastname, con.email
FROM customer c
LEFT JOIN contact con ON c.id = con.company
WHERE con.isprimarycontact = 'T' OR con.id IS NULL

Employee + Department + Location

SELECT
    e.entityid,
    e.title,
    d.name AS department,
    l.name AS location
FROM employee e
LEFT JOIN department d ON e.department = d.id
LEFT JOIN location l ON e.location = l.id
WHERE e.isinactive = 'F'

---

Join Performance Tips

Do's

-- DO: Join on indexed fields (usually IDs)
SELECT * FROM transaction t
INNER JOIN customer c ON t.entity = c.id

-- DO: Filter early to reduce rows
SELECT * FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'CustInvc'  -- Filter before join logic

-- DO: Select only needed columns
SELECT t.tranid, c.companyname
FROM transaction t
INNER JOIN customer c ON t.entity = c.id

Don'ts

-- DON'T: Join on non-indexed fields
SELECT * FROM transaction t
INNER JOIN customer c ON t.memo = c.comments  -- Slow!

-- DON'T: Use functions on join columns
SELECT * FROM transaction t
INNER JOIN customer c ON UPPER(t.entityname) = UPPER(c.companyname)

-- DON'T: Cross join without understanding size
SELECT * FROM item CROSS JOIN customer  -- Millions of rows!

---

Troubleshooting Joins

Problem: Duplicate Rows

Cause: One-to-many relationship multiplies rows

-- This might return 100 rows for 1 order with 10 lines
SELECT t.tranid, t.amount, tl.item
FROM transaction t
JOIN transactionline tl ON t.id = tl.transaction
WHERE t.id = 12345

Solution: Aggregate if you need order-level data

-- One row per order
SELECT t.tranid, t.amount, COUNT(tl.id) AS line_count
FROM transaction t
JOIN transactionline tl ON t.id = tl.transaction
WHERE t.id = 12345
GROUP BY t.id, t.tranid, t.amount

Problem: Missing Rows

Cause: INNER JOIN filters out non-matching rows

Solution: Use LEFT JOIN to include all rows from primary table

-- All customers, even those without orders
SELECT c.companyname, t.tranid
FROM customer c
LEFT JOIN transaction t ON c.id = t.entity

Problem: NULL in Results

Cause: LEFT/RIGHT JOIN with no match

Solution: Use NVL/COALESCE for display

SELECT
    c.companyname,
    NVL(t.tranid, 'No Orders') AS latest_order
FROM customer c
LEFT JOIN transaction t ON c.id = t.entity

---

Quick Reference

Join Syntax Patterns

-- INNER JOIN
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.foreign_id

-- LEFT JOIN
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.foreign_id

-- Multiple conditions
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.foreign_id
                    AND t2.type = 'specific_type'

-- Multiple tables
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.t1_id
INNER JOIN table3 t3 ON t2.id = t3.t2_id
LEFT JOIN table4 t4 ON t1.id = t4.t1_id

Common NetSuite Relationships

Primary Table	Foreign Key	Related Table
transaction	entity	customer/vendor
transaction	salesrep	employee
transactionline	transaction	transaction
transactionline	item	item
customer	salesrep	employee
customer	subsidiary	subsidiary
item	parent	item (self)
employee	supervisor	employee (self)
employee	department	department