Why does my SuiteQL RIGHT JOIN between transaction and transactionLine return 0 rows?

Because the RIGHT JOIN keyword was ignored and the statement ran as an inner join. Between transaction and transactionLine, RIGHT JOIN returns nothing and FULL OUTER JOIN returns the header side only, which is a LEFT join from the header, not an inner one. No error either way. LEFT JOIN from the header is honoured, and so is the Oracle (+) operator.

Minimal reproduction

Join on a condition that can never match. A working outer join then has to null-supply the preserved side, and an inner join has to return zero. One statement per call:

SELECT COUNT(*) AS n FROM transaction t LEFT JOIN transactionLine tl ON 1 = 0

SELECT COUNT(*) AS n FROM transaction t RIGHT JOIN transactionLine tl ON 1 = 0

SELECT COUNT(*) AS n FROM transaction t FULL OUTER JOIN transactionLine tl ON 1 = 0

Results:

Call	Should return	Actually returned
LEFT	every transaction row	every transaction row, correct
RIGHT	every transactionLine row	0, an inner join
FULL OUTER	headers plus lines	the header count only, a LEFT join from the header

RIGHT is the inner-join case: under ON 1 = 0 an inner join must return zero, and zero is what came back. FULL is not. An inner join would have returned zero here too, and FULL returned every header row instead, so it was downgraded to LEFT rather than to INNER. Writing the line table on the left changes nothing: transactionLine tl FULL OUTER JOIN transaction t ON 1 = 0 also came back as the header count.

The same shape against transactionAccountingLine does not run at all:

SELECT COUNT(*) AS n FROM transaction t RIGHT JOIN transactionAccountingLine tal ON 1 = 0

N/query.runSuiteQL error: Search error occurred: Invalid or unsupported search

That string is NetSuite's own text.

The fix

To preserve the header side and leave the line side optional, use a plain LEFT JOIN from the header. That is the measured working form: under a never-matching condition it still returned every transaction row.

SELECT COUNT(*) AS n FROM transaction t LEFT JOIN transactionLine tl ON tl.transaction = t.id

The Oracle (+) operator works too, with the operator on the optional side. This form also preserved every transaction row:

SELECT COUNT(*) AS n
FROM transaction t, transactionLine tl
WHERE tl.transaction(+) = t.id AND tl.id(+) = -999

The line side is the case this page cannot close. No call in this run demonstrated an outer join that preserves transactionLine against transaction. The candidate is (+) moved to the header side:

SELECT COUNT(*) AS n FROM transaction t, transactionLine tl WHERE tl.transaction = t.id(+)

It parses and runs, but the account had no orphan lines, so its result was identical to an inner join and its outer semantics were never shown. Run the check below on it before relying on it.

Why this happens

It is not a limitation of transactionLine. RIGHT JOIN from an unrelated table into transactionLine preserved every line row, and the control pair of two unrelated tables gave correct LEFT, RIGHT and FULL results, the FULL equalling the sum of both sides. It is the pair transaction plus transactionLine that triggers it.

Stated as hypothesis, because the run did not confirm it: when both sides belong to the same native NetSuite search hierarchy, a record and its line sublist, the engine substitutes its own built-in parent/child join and discards the requested outer semantics.

Worth knowing why this was nearly missed. On a real join condition, INNER, LEFT, LEFT OUTER, FULL OUTER, the comma join and (+) all returned an identical count, which proves nothing: where every header has a line and every line has a header, inner and outer coincide. You have to join ON 1 = 0 to see the difference.

Scope: one production account on NetSuite 2026.1. Every probe behind this page ran entirely through N/query.runSuiteQL; the finding was not tested on the REST endpoint.

How to tell if you are affected

Two calls. Replace the ON clause of the outer join you rely on with ON 1 = 0, then compare its count against a bare count of the table you expected to be preserved.

SELECT COUNT(*) AS n FROM transaction t RIGHT JOIN transactionLine tl ON 1 = 0

SELECT COUNT(*) AS n FROM transactionLine

If the two agree, the outer join was honoured. If the first returns 0 and the second does not, the keyword was ignored and you have been running an inner join. On this pair, the first returns 0.

For your own pair of tables the same check is a template. Substitute your table names:

SELECT COUNT(*) AS n FROM your_left_table a RIGHT JOIN your_right_table b ON 1 = 0

This matters most for the query nobody double-checks: the orphan hunt. Lines with no header, headers with no lines, accounting lines with no matching transaction line. Those come back empty here, and empty reads as a clean bill of health.