Relationship Queries

Introduction

Relationship queries answer 'who is connected to whom' — friend-of-friend, customers who bought X also bought Y, manager → reports. Combine joins, self-joins and CTEs.

Understanding the topic

Core concepts to understand:

• M:N: join via the bridge table.
• Friend-of-friend: two-step join on follows table.
• Co-purchase: order_items joined to itself.
• Hierarchy: recursive CTE.
• Watch combinatorial explosion on big graphs.

Syntax reference

Visual workflow / architecture:

-- Customers who bought both A and B
SELECT user_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE product_id IN ('A','B')
GROUP BY user_id
HAVING count(distinct product_id) = 2;

Real-world use

Recommendations ('people who bought X also bought Y'), social graph traversal, and audit trails are relationship queries.

Best practices

• Use HAVING + COUNT(DISTINCT) for 'has all of X'.
• Recursive CTE for trees, with depth limit.
• Limit graph traversal depth in production.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Find users with at least all of [A, B, C].
• Q2. Friend-of-friend query.
• Q3. Recursive CTE for org chart.
• Q4. Co-purchase pattern.
• Q5. Limits of relational graph traversal.