Understanding SQL Joins: INNER, LEFT, RIGHT, and FULL OUTER JOIN

1. INNER JOIN

An INNER JOIN returns only the matching records between two tables based on the specified condition. If there is no match, no record is returned.

Example:

SELECT e.entityid, e.companyname, t.tranid, t.total
FROM entity e
INNER JOIN transaction t ON e.id = t.entity;

How it works:

• Retrieves only records where there is a match between entity and transaction.
• If an entity has no transactions, it is excluded from the result.

2. LEFT OUTER JOIN

A LEFT OUTER JOIN returns all records from the left table and the matching records from the right table. If there’s no match, NULL values appear in the right table’s columns.

Example:

SELECT e.entityid, e.companyname, t.tranid, t.total
FROM entity e
LEFT OUTER JOIN transaction t ON e.id = t.entity;

How it works:

• Retrieves all records from entity.
• If an entity has transactions, the transaction details appear.
• If an entity has no transactions, NULL appears in the transaction columns.

3. RIGHT OUTER JOIN

A RIGHT OUTER JOIN returns all records from the right table and the matching records from the left table. If there’s no match, NULL values appear in the left table’s columns.

Example:

SELECT e.entityid, e.companyname, t.tranid, t.total
FROM entity e
RIGHT OUTER JOIN transaction t ON e.id = t.entity;

How it works:

• Retrieves all records from transaction.
• If a transaction is linked to an entity, the entity details appear.
• If a transaction has no matching entity, NULL appears in the entity columns.

4. FULL OUTER JOIN

A FULL OUTER JOIN returns all records from both tables. If there is a match, data from both tables is combined. If there is no match, NULL values appear for the missing columns.

Example:

SELECT e.entityid, e.companyname, t.tranid, t.total
FROM entity e
FULL OUTER JOIN transaction t ON e.id = t.entity;

How it works:

• Retrieves all records from entity and transaction.
• If an entity has transactions, they appear together.
• If an entity has no transactions, NULL appears in the transaction columns.
• If a transaction has no matching entity, NULL appears in the entity columns.

Note: SuiteQL does not support FULL OUTER JOIN directly. Instead, use:

SELECT e.entityid, e.companyname, t.tranid, t.total
FROM entity e
LEFT OUTER JOIN transaction t ON e.id = t.entity
UNION
SELECT e.entityid, e.companyname, t.tranid, t.total
FROM entity e
RIGHT OUTER JOIN transaction t ON e.id = t.entity;