Join
One query that pulls matching rows from two tables together, so you get the user's name next to their orders.
See it
What it is
A join stitches rows from two tables together on a matching column, usually a foreign key, so one query returns the order and the customer's name side by side. INNER JOIN keeps only rows with a match on both sides. LEFT JOIN keeps every row from the left table and fills nulls where the right side has nothing, which is what you want for 'all users, with their subscription if they have one'.
Reach for a join instead of two round trips whenever you need related data together: it is one plan, one trip, and the database is very good at this. Index the columns the planner actually probes and filters on, which is usually the referencing foreign key, since the referenced side already has an index behind its key. Indexing both sides on reflex is not the rule: when a join reads most of a table, a sequential scan feeding a hash join is genuinely the fastest plan, and EXPLAIN ANALYZE will tell you which one you are in.
Gotcha: joining a one-to-many relationship multiplies rows. Join orders to order_items and a three-item order becomes three rows, so any SUM over the order total now triple-counts. This is called fan-out, and the fix is to aggregate in a subquery or CTE first, then join the single-row result. Second gotcha: filtering the right-hand table in the WHERE clause of a LEFT JOIN quietly turns it back into an INNER JOIN, because null fails the comparison. Put that condition in the ON clause instead.
Ask AI for it
Write a single SQL query that joins these tables instead of querying them separately. Use INNER JOIN where the related row must exist and LEFT JOIN where it is optional, alias every table, and select only the columns I need rather than *. If any join is one-to-many and I am aggregating, pre-aggregate the child table in a CTE before joining so totals do not fan out and double-count. Then run EXPLAIN ANALYZE and tell me which join and filter columns actually want an index (usually the referencing foreign key), and where the plan is already fine on a sequential scan and a hash join so I do not add an index for nothing.