JOINs Explained: Connecting ICM Tables Together

Why JOINs Matter

A single table gives you limited insight. Participants live in CSC_PARTICIPANT. Results live in CSC_RESULTS. Compensation plans live in CSC_COMP_PLAN. To answer real questions like "What is each participant's total earned amount and their assigned plan?", you need data from multiple tables.

That's where JOINs come in. A JOIN combines rows from two tables based on a matching condition.

INNER JOIN: Only Matching Rows

INNER JOIN returns rows that exist in BOTH tables. If a participant has no results, they don't appear.

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  r.RESULT_AMOUNT,
  r.PERIOD_ID
FROM   CSC_PARTICIPANT p
INNER JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE r.PERIOD_ID = '2026-Q1';

Breaking this down:

• FROM CSC_PARTICIPANT p — Start with participants; alias it as "p"
• INNER JOIN CSC_RESULTS r — Join the results table; alias it as "r"
• ON r.PARTICIPANT_ID = p.PARTICIPANT_ID — Match rows where the result's PARTICIPANT_ID equals the participant's PARTICIPANT_ID

The query returns only participants who have results in Q1 2026. Participants with zero results are excluded.

Aliases (p and r) are crucial for clarity when referencing columns from different tables. Instead of typing CSC_PARTICIPANT.PARTICIPANT_ID, you type p.PARTICIPANT_ID.

LEFT JOIN: All Rows from the Left Table

LEFT JOIN returns ALL rows from the left table (the one in FROM) plus matching rows from the right table. If there's no match in the right table, those columns are NULL.

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  r.RESULT_AMOUNT,
  r.PERIOD_ID
FROM   CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE r.PERIOD_ID = '2026-Q1'
  OR  r.PERIOD_ID IS NULL;

Now the query returns ALL participants, including those with no results in Q1 2026. For those participants, RESULT_AMOUNT and PERIOD_ID are NULL.

The Critical Difference in ICM

Here's where JOIN type matters for compensation:

If you use INNER JOIN to report on participants and accidentally exclude those with zero results, your manager sees inflated totals and you'll face a compensation dispute. Always ask: "Should this participant appear in the report even if they have zero results?" If yes, use LEFT JOIN.

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is the reverse of LEFT JOIN. It returns all rows from the right table. FULL OUTER JOIN returns all rows from both tables. These are rarely used in ICM, so we'll skip the details, but know they exist.

Real ICM Example: Three-Table Query

Here's a production query: "Show me each participant, their assigned compensation plan, and their total earned in Q1 2026."

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  cp.PLAN_NAME,
  SUM(r.RESULT_AMOUNT) AS TOTAL_EARNED
FROM   CSC_PARTICIPANT p
LEFT JOIN CSC_COMP_PLAN cp
  ON cp.PLAN_ID = p.PLAN_ID
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
  AND r.PERIOD_ID = '2026-Q1'
WHERE p.STATUS = 'ACTIVE'
GROUP BY p.PARTICIPANT_ID, p.NAME, cp.PLAN_NAME
ORDER BY TOTAL_EARNED DESC;

Notice:

• We use LEFT JOIN twice. If we used INNER JOIN, we'd exclude participants with no plan or no results.
• The second join condition includes AND r.PERIOD_ID = '2026-Q1'. This filters which results match, not the JOIN itself.
• SUM(r.RESULT_AMOUNT) aggregates earnings (we'll cover GROUP BY in Lesson 04, but notice we're summing multiple results rows per participant).
• All participants appear, even those with NULL earnings (which means zero earned).

Join Conditions: ON vs WHERE

Join conditions go in the ON clause, not WHERE:

-- CORRECT: ON specifies how to join
FROM CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE p.STATUS = 'ACTIVE';

-- WRONG: Filtering in ON clause with LEFT JOIN
FROM CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
  AND r.PERIOD_ID = '2026-Q1';  -- This can filter out rows!

The difference is subtle but critical. When you put a filter in the ON clause of a LEFT JOIN, rows that don't match the condition are still returned, but with NULLs. The WHERE clause filters after the join is complete, potentially excluding rows you want.

Multi-Table Joins: Participant → Plan → Results

Here's a cleaner version of the three-table query, focusing on just one period:

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  cp.PLAN_NAME,
  r.RESULT_AMOUNT,
  r.PERIOD_ID
FROM   CSC_PARTICIPANT p
LEFT JOIN CSC_COMP_PLAN cp
  ON cp.PLAN_ID = p.PLAN_ID
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE p.STATUS = 'ACTIVE'
  AND r.PERIOD_ID = '2026-Q1'
ORDER BY p.NAME;

Now you get one row per participant-result combination. Participants with no plan show PLAN_NAME as NULL. Participants with no results for Q1 show RESULT_AMOUNT as NULL.

Callidus Commissions Equivalent

The syntax is identical on Oracle Database. Just change the table names from CSC_ to CS_:

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  cp.PLAN_NAME,
  r.RESULT_AMOUNT
FROM   CS_PARTICIPANT p
LEFT JOIN CS_COMP_PLAN cp
  ON cp.PLAN_ID = p.PLAN_ID
LEFT JOIN CS_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE p.STATUS = 'ACTIVE'
ORDER BY p.NAME;

Debugging JOIN Issues

If a query returns unexpected results, check:

• Wrong row count? Did you use INNER vs LEFT? INNER silently excludes unmatched rows.
• Missing participants? Check the WHERE clause. If you filter on a column from the right table with LEFT JOIN, unmatched rows might disappear.
• Duplicate rows? If multiple results per participant and you didn't use GROUP BY, you get one row per result (which is correct, not a bug).
• Wrong data in matched rows? Verify the ON condition. ON p.PARTICIPANT_ID = r.PARTICIPANT_ID is correct. ON p.NAME = r.NAME is disaster (matches by name, not ID).

What Comes Next

Now that you can pull data from multiple tables, Lesson 04 shows how to summarize it. GROUP BY collapses multiple result rows into summaries, which is how you get "total earned by participant" instead of one row per transaction.