Joins and Relationship Queries Without Duplicate Surprises

A join combines rows when its condition matches, so result cardinality comes from the relationship—not from the number of source tables. Correct joins state every ownership key, preserve missing rows intentionally, and distinguish “return matching details” from “test whether a match exists.” Duplicate-looking rows are usually a modeling or cardinality clue, not a reason for reflexive DISTINCT.

What will you be able to do?

Use inner, left, cross, and lateral joins deliberately.
Predict one-to-one, one-to-many, and many-to-many cardinality.
Express semi-joins with EXISTS and anti-joins with NOT EXISTS.
Prevent cross-tenant join mistakes.

Prerequisites and mental model

Imagine a join as nested matching: for each left row, find every right row satisfying ON. Zero matches disappear in an inner join or become one null-extended row in a left join. Multiple matches multiply rows.

How do you join the ticket thread?

sql

SELECT
  t.id,
  t.subject,
  c.display_name AS customer,
  m.id AS message_id,
  m.author_kind,
  m.body
FROM signaldesk.tickets AS t
JOIN signaldesk.customers AS c
  ON c.organization_id = t.organization_id
 AND c.id = t.customer_id
LEFT JOIN signaldesk.messages AS m
  ON m.organization_id = t.organization_id
 AND m.ticket_id = t.id
WHERE t.organization_id = $1
ORDER BY t.id, m.created_at, m.id;

One ticket with three messages becomes three result rows. That is correct for message detail but wrong if the consumer expects one row per ticket.

When should you use EXISTS?

Return tickets that have at least one customer message without multiplying them:

sql

SELECT t.id, t.subject
FROM signaldesk.tickets AS t
WHERE t.organization_id = $1
  AND EXISTS (
    SELECT 1
    FROM signaldesk.messages AS m
    WHERE m.organization_id = t.organization_id
      AND m.ticket_id = t.id
      AND m.author_kind = 'customer'
  );

Use NOT EXISTS for tickets with no messages. It handles nulls more predictably than many NOT IN constructions.

How does LATERAL solve “top item per parent”?

sql

SELECT t.id, t.subject, last_message.body, last_message.created_at
FROM signaldesk.tickets AS t
LEFT JOIN LATERAL (
  SELECT m.body, m.created_at
  FROM signaldesk.messages AS m
  WHERE m.organization_id = t.organization_id
    AND m.ticket_id = t.id
  ORDER BY m.created_at DESC, m.id DESC
  LIMIT 1
) AS last_message ON true
WHERE t.organization_id = $1;

The lateral subquery can reference each ticket. Window functions offer another solution; compare plans and clarity.

Why does this matter in AI-native systems?

A careless join can duplicate chunks, messages, or evaluation cases, over-weighting evidence in a prompt and distorting metrics. A join missing tenant keys can leak data even when both individual tables include tenant IDs. Retrieval context construction must verify cardinality, ownership, and deduplication before token assembly.

Prove it worked

Seed one ticket with zero messages, one with one, and one with three. Before each query, write expected row count. Verify inner join, left join, EXISTS, NOT EXISTS, and lateral-last-message results. If DISTINCT changes the answer, explain the multiplication you hid.

Production judgment

Foreign keys help the planner and correctness but do not automatically add indexes on referencing columns.
Outer-join filters placed in WHERE can accidentally turn the result into an inner join; put match-specific filters in ON when missing parents must remain.
Natural joins and USING can be concise but become fragile when schemas add same-named columns.
Join order is planned; write for semantics, then inspect performance.

Common mistakes

Unexpected duplicates: one-to-many relationship ignored → define expected grain and aggregate or use EXISTS.
Missing parent rows: right-side filter in WHERE after left join → move match filter into ON.
Tenant leak: joined only on local ID → include organization/tenant keys.
DISTINCT everywhere: symptom hidden → explain cardinality first.

AI Pair-Programmer Prompt

text

Analyze this PostgreSQL relationship query. State the expected grain, source keys,
relationship cardinality, maximum multiplication, tenant conditions, and behavior for
missing matches. Prefer EXISTS for existence tests. Do not add DISTINCT until you can
name the duplicate source. Supply minimal seed data and exact expected row counts.

Hands-on exercise

Return one row per ticket with customer name, message count, and latest message body, including tickets with zero messages.

Acceptance criterion: the result has exactly one row per ticket, a zero count for empty threads, deterministic latest-message selection, and tenant keys in every relationship.

Knowledge check

Why does a ticket repeat in a join to messages?
When is EXISTS better than an inner join?
How can a left join accidentally behave like an inner join?
Why is DISTINCT not the first fix?

Answers

Each matching message produces a result row.
When only the existence of a match matters and parent rows should not multiply.
A WHERE predicate rejecting null-extended right-side rows removes unmatched parents.
It can hide incorrect grain, join conditions, or modeling while adding work.

Completion checklist

I state result grain before joining.
I predict row multiplication.
Tenant ownership is present in joins.
Missing relationships behave intentionally.

Joins and Relationship Queries Without Duplicate Surprises

What will you be able to do?

Prerequisites and mental model

How do you join the ticket thread?

When should you use EXISTS?

How does LATERAL solve “top item per parent”?

Why does this matter in AI-native systems?

Prove it worked

Production judgment

Common mistakes

AI Pair-Programmer Prompt

Hands-on exercise

Knowledge check

Answers

Completion checklist

Primary references

Joins and Relationship Queries Without Duplicate Surprises

What will you be able to do?

Prerequisites and mental model

How do you join the ticket thread?

When should you use EXISTS?

How does LATERAL solve “top item per parent”?

Why does this matter in AI-native systems?

Prove it worked

Production judgment

Common mistakes

AI Pair-Programmer Prompt

Hands-on exercise

Knowledge check

Answers

Completion checklist

Primary references