A JOIN combines related rows from two tables so one query can answer a question whose facts are stored separately. For interview questions, the hard part is not memorizing JOIN syntax: it is identifying the relationship, predicting the result's grain, and checking whether the join creates missing or repeated rows.
Key Takeaways at a Glance:
- •INNER JOIN returns rows ONLY when there is a match in both joined relations based on the ON predicate.
- •The ON clause defines the join linkage condition; the WHERE clause filters the joined result set.
- •One-to-many joins duplicate the parent row for every matching child row (Cartesian fanout).
- •Always qualify column names with short table aliases (e.g. e.salary, d.dept_name) to avoid ambiguous column errors.
In Simple Words (The Mental Model)
An INNER JOIN is like matching two puzzle pieces together. You take rows from Table A and look for matching rows in Table B using a common key (like customer_id). If a customer has never bought anything, they don't appear. If an order has no valid customer, it doesn't appear. Only matched pairs make it into the result.
Real-World Analogy
Think of matching nametags at a wedding reception. Table A has guest names; Table B has assigned dinner table numbers linked by Guest_ID. You check each guest's ID on both lists and write down: 'Aarav -> Table 4'. Anyone who didn't show up or doesn't have an assigned table is omitted.