JOINs & Subqueries

Because Syntra includes a full analytical SQL engine, you have access to the complete range of SQL features including JOINs, subqueries, CTEs, and window functions.

JOINs

INNER JOIN

Returns only rows with matching records in both tables:

SELECT
    i.ref_number AS InvoiceNumber,
    c.name,
    i.txn_date,
    i.balance_remaining
FROM invoices i
INNER JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.is_paid = false;

LEFT JOIN

Returns all rows from the left table, with NULLs for non-matching rows in the right table:

SELECT
    c.name,
    c.balance,
    MAX(i.txn_date) AS LastInvoiceDate
FROM customers c
LEFT JOIN invoices i ON c.list_id = i.customer_ref_list_id
GROUP BY c.name, c.balance;

Multiple JOINs

SELECT
    i.ref_number,
    c.name,
    il.item_ref_full_name AS Item,
    il.quantity,
    il.rate,
    il.amount
FROM invoice_lines il
JOIN invoices i ON il.txn_id = i.txn_id
JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.txn_date >= '2025-01-01'
ORDER BY i.txn_date DESC;

JOIN Types

Type	Behavior
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left table, NULLs for right if no match
RIGHT JOIN	All rows from right table, NULLs for left if no match
FULL OUTER JOIN	All rows from both tables, NULLs where no match
CROSS JOIN	Cartesian product of both tables

Common Join Columns

• Customers to Invoices: customers.list_id = invoices.customer_ref_list_id
• Vendors to Bills: vendors.list_id = bills.vendor_ref_list_id
• Invoices to Invoice Lines: invoices.txn_id = invoice_lines.txn_id
• Items to Invoice Lines: items.list_id = invoice_lines.item_ref_list_id

Subqueries

Scalar Subqueries

SELECT name, balance
FROM customers
WHERE balance > (SELECT AVG(balance) FROM customers);

IN Subqueries

SELECT *
FROM customers
WHERE list_id IN (
    SELECT customer_ref_list_id
    FROM invoices
    WHERE txn_date >= '2025-01-01'
);

EXISTS Subqueries

SELECT c.name, c.balance
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM invoices i
    WHERE i.customer_ref_list_id = c.list_id
      AND i.is_paid = false
);

Common Table Expressions (CTEs)

CTEs let you define temporary named result sets for clearer, more modular queries:

WITH overdue_invoices AS (
    SELECT
        customer_ref_list_id,
        COUNT(*) AS OverdueCount,
        SUM(balance_remaining) AS TotalOverdue
    FROM invoices
    WHERE is_paid = false
      AND due_date < CURRENT_DATE
    GROUP BY customer_ref_list_id
)
SELECT
    c.name,
    c.phone,
    c.email,
    oi.OverdueCount,
    oi.TotalOverdue
FROM customers c
JOIN overdue_invoices oi ON c.list_id = oi.customer_ref_list_id
ORDER BY oi.TotalOverdue DESC;

Window Functions

Syntra supports the full range of SQL window functions:

ROW_NUMBER

SELECT
    customer_ref_full_name,
    ref_number,
    txn_date,
    balance_remaining,
    ROW_NUMBER() OVER (PARTITION BY customer_ref_full_name ORDER BY txn_date DESC) AS rn
FROM invoices
WHERE is_paid = false;

Running Totals

SELECT
    txn_date,
    ref_number,
    amount,
    SUM(amount) OVER (ORDER BY txn_date) AS RunningTotal
FROM sales_receipts
WHERE txn_date >= '2025-01-01';

RANK and DENSE_RANK

SELECT
    name,
    balance,
    RANK() OVER (ORDER BY balance DESC) AS BalanceRank
FROM customers
WHERE balance > 0;

LAG and LEAD

SELECT
    txn_date,
    ref_number,
    amount,
    LAG(amount) OVER (ORDER BY txn_date) AS PreviousAmount,
    amount - LAG(amount) OVER (ORDER BY txn_date) AS Change
FROM invoices
WHERE customer_ref_full_name = 'Acme Corporation'
ORDER BY txn_date;