SuiteQL join examples that cover most real queries

SuiteQL joins are ordinary SQL joins. What makes them feel unfamiliar is NetSuite's schema: transactions split across a header and a line table, entities layered into a shared parent plus type-specific children, and a mainline flag that will double every total you compute until you know about it. These SuiteQL join examples are the ones worth memorising, in the order you will need them.

The join keys worth memorising

1. Header to line: the join you will write a thousand times

SELECT
    t.tranid                    AS invoice_no,
    t.trandate,
    BUILTIN.DF(t.entity)        AS customer,
    tl.linesequencenumber       AS line_no,
    BUILTIN.DF(tl.item)         AS item,
    tl.quantity,
    tl.netamount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'CustInvc'
  AND t.trandate >= TRUNC(SYSDATE) - 30
  AND tl.mainline = 'F'
ORDER BY t.trandate DESC, tl.linesequencenumber

tl.mainline = 'F' excludes the header summary line that every transaction carries. Without it you get an extra row per transaction holding the full total, and every SUM roughly doubles. If a number looks about twice as big as it should be, this is the reason nine times out of ten.

2. Header to line to item: where left versus inner actually matters

Not every transaction line has an item. Shipping, tax, discount and expense lines have a null item, so an inner join to item silently removes them. Whether that is correct depends on the question. For item profitability, dropping them is right. For reconciling to the invoice total, dropping them makes you short by exactly the freight and tax.

SELECT
    t.tranid                                    AS invoice_no,
    tl.linesequencenumber                       AS line_no,
    NVL(i.itemid, '-- non-item line --')        AS item_or_charge,
    NVL(BUILTIN.DF(i.itemtype), 'other')        AS item_type,
    tl.netamount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
LEFT JOIN item i        ON i.id = tl.item
WHERE t.type = 'CustInvc'
  AND t.tranid = 'INV20418'
  AND tl.mainline = 'F'
ORDER BY tl.linesequencenumber

3. Entity joins: customer, vendor, or the shared parent

transaction.entity holds an internal id from the shared entity table, which is the parent of customers, vendors, employees, partners and contacts. You have a choice of what to join to, and the choice does work for you.

• Join to `customer` when you want customer-only fields (terms, creditlimit, custentity_*). Because only customer ids exist in that table, the join itself acts as a filter.
• Join to `entity` when the query spans types — an AP and AR report in one list, or an audit of everything a given entity touched.
• Use `BUILTIN.DF(t.entity)` when all you need is the name. It saves a join and reads better.

SELECT
    c.entityid,
    c.companyname,
    BUILTIN.DF(c.terms)                 AS payment_terms,
    c.custentity_credit_tier            AS credit_tier,
    COUNT(DISTINCT t.id)                AS open_invoices,
    SUM(t.foreigntotal)                 AS open_value
FROM customer c
JOIN transaction t ON t.entity = c.id
WHERE t.type   = 'CustInvc'
  AND t.status = 'CustInvc:A'
  AND c.isinactive = 'F'
GROUP BY c.entityid, c.companyname, BUILTIN.DF(c.terms), c.custentity_credit_tier
ORDER BY open_value DESC
FETCH FIRST 50 ROWS ONLY

No line join here, so SUM(t.foreigntotal) is safe — the header total is counted once per invoice. The moment you add a transactionline join, that sum becomes nonsense and you have to move to summing line amounts instead. Deciding which side of that line you are on is most of the skill.

4. Anti-join: finding what is missing

The most useful queries often return rows that do not exist elsewhere. Customers who stopped ordering. Items never sold. Purchase orders received but never billed. All three are left joins with a null test, and none of them can be built as a single saved search.

SELECT
    po.tranid                       AS po_number,
    po.trandate,
    BUILTIN.DF(po.entity)           AS vendor,
    po.foreigntotal,
    rcpt.tranid                     AS receipt_no,
    rcpt.trandate                   AS received_on
FROM transaction po
JOIN transaction rcpt
       ON rcpt.createdfrom = po.id
      AND rcpt.type = 'ItemRcpt'
LEFT JOIN transaction bill
       ON bill.createdfrom = po.id
      AND bill.type = 'VendBill'
WHERE po.type = 'PurchOrd'
  AND bill.id IS NULL
  AND rcpt.trandate < TRUNC(SYSDATE) - 14
ORDER BY rcpt.trandate

Three references to transaction in one query, two of them self-joins through createdfrom. This is the shape a saved search cannot produce at all, and it is a genuinely useful report: goods you have received and not been billed for, older than a fortnight, which is an accrual waiting to surprise you at close.

5. Joining to the GL: accounts, subsidiaries and periods

SELECT
    s.name                          AS subsidiary,
    a.acctnumber,
    BUILTIN.DF(tal.account)         AS account_name,
    ap.periodname,
    SUM(tal.debit)                  AS total_debit,
    SUM(tal.credit)                 AS total_credit,
    SUM(tal.amount)                 AS net_amount
FROM transactionaccountingline tal
JOIN transaction      t  ON t.id  = tal.transaction
JOIN transactionline  tl ON tl.transaction = tal.transaction
                        AND tl.id          = tal.transactionline
JOIN account          a  ON a.id  = tal.account
JOIN accountingperiod ap ON ap.id = t.postingperiod
LEFT JOIN subsidiary  s  ON s.id  = tl.subsidiary
WHERE t.posting   = 'T'
  AND ap.isadjust = 'F'
  AND ap.periodname = 'Jun 2026'
GROUP BY s.name, a.acctnumber, BUILTIN.DF(tal.account), ap.periodname
ORDER BY s.name, a.acctnumber

The compound join between transactionline and transactionaccountingline needs both keys — transaction and line — because line ids are only unique within a transaction. t.posting = 'T' restricts to documents that actually hit the GL, which drops sales orders, quotes and other non-posting records. The subsidiary join is a left join so single-instance accounts without OneWorld still return rows.

6. Custom records, joined both ways

SELECT
    audit.id,
    audit.name                              AS audit_ref,
    audit.custrecord_audit_date             AS audit_date,
    c.companyname                           AS customer,
    BUILTIN.DF(audit.custrecord_audit_owner) AS owner,
    COUNT(f.id)                             AS finding_count
FROM customrecord_equipment_audit audit
JOIN customer c
       ON c.id = audit.custrecord_audit_customer
LEFT JOIN customrecord_audit_finding f
       ON f.custrecord_finding_audit = audit.id
WHERE audit.custrecord_audit_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3)
GROUP BY audit.id, audit.name, audit.custrecord_audit_date,
         c.companyname, BUILTIN.DF(audit.custrecord_audit_owner)
HAVING COUNT(f.id) > 0
ORDER BY finding_count DESC

Swap in your own record and field ids; the pattern is what carries over. Custom record tables are named after the record id, and every custom field becomes a column with its own custrecord_ id. Get both from Customization > Lists, Records & Fields, not from the label on the form — labels get renamed and ids do not. More on discovery and field types in how to query NetSuite custom records.

7. A correlated subquery, when a join would multiply

SELECT
    c.entityid,
    c.companyname,
    SUM(tal.amount)                 AS ar_balance,
    (SELECT MAX(t2.trandate)
       FROM transaction t2
      WHERE t2.entity = c.id
        AND t2.type   = 'CustInvc')  AS last_invoiced
FROM customer c
JOIN transaction              t   ON t.entity   = c.id
JOIN transactionaccountingline tal ON tal.transaction = t.id
JOIN account                  a   ON a.id       = tal.account
WHERE t.posting = 'T'
  AND a.accttype = 'AcctRec'
GROUP BY c.id, c.entityid, c.companyname
HAVING SUM(tal.amount) <> 0
ORDER BY ar_balance DESC

Summing every posting to an AR-type account per customer nets invoices against payments and credit memos, so the result is a live receivables balance rather than a list of invoices. The subquery for last invoice date is correlated rather than joined, because joining a second transaction set would multiply the AR rows. Once you have those balances, bucket them with the AR aging calculator to get weighted average days overdue and a reserve estimate.

Join hygiene: five rules that prevent wrong answers

Performance notes for multi-join queries

• Filter the driving table first. A date or type predicate on transaction shrinks the set before the line join happens. Filtering only on a joined table's column makes the engine build the wide result first.
• Join `transactionaccountingline` last and only when you need GL amounts. It is the widest of the transaction tables and the most expensive to fan out.
• Prefer `BUILTIN.DF()` over a join when you want one label. It saves a table from the plan.
• Read the query before you tune it. A five-table join written as one line hides its own bugs; the SuiteQL formatter puts one clause per line so you can see which table drives the plan.
• Bound everything. A relative date range costs nothing to add and prevents a full history scan. When you need exact fiscal boundaries rather than rolling days, work them out with the fiscal period calculator.

If any of the syntax here was unfamiliar, the ground floor is in SuiteQL tutorial for beginners, and ready-made versions of the AR, open-order and revenue queries are collected in the SuiteQL transaction queries cookbook.

Why the join is the thing worth checking

Every wrong number in this post came from a join, not from arithmetic. A missing mainline filter, a WHERE that should have been an ON, a header sum after a line join. None of them throw an error. All of them return something that looks right.

That is why ERPray prints the SuiteQL underneath every answer it gives. You are not being asked to trust that the join was right — you can read it. Scan for mainline, check which side the date filter sits on, confirm the posting flag, and either accept the number or tell it to try again. Read-only by default, NetSuite-first, running as a dedicated instance you control.