JOIN — SQL Technical Interview Questions & Answers

JOIN

JOIN

  • JOIN sticks two tables side by side using a match. students has names. marks has scores. SELECT s.name, m.subject, m.score FROM students s JOIN marks m ON s.roll = m.roll;
  • ON is the match rule, usually PK = FK. If you forget ON (or use comma joins), you get every student with every marks row — cartesian product. That looks ‘too many rows’.
  • INNER JOIN (plain JOIN) keeps only matches. LEFT JOIN keeps all students even if they have no marks yet (marks columns NULL).
  • Draw two tiny tables on paper, three rows each, then the join result. That is the interview. Don’t recite six join names without a picture.
  • On the example next to this theory: JOIN combines rows using a match condition between tables.
students          marks
  roll name         roll subject score
   12  Asha    JOIN  12  Maths    72
   13  Kabir         12  Eng      40
        │
        ▼  ON roll
  Asha | Maths | 72
  Asha | Eng   | 40

Technical Interview Tip: JOIN … ON s.roll = m.roll. Draw two small tables.

Code Examples for JOIN

JOIN — sample query

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 500), (11, 1, 200);
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

JOIN combines rows using a match condition between tables.

How to Explain JOIN to an Interviewer

What is a JOIN?

Combine related tables ON s.roll = m.roll

What if you omit ON?

Cartesian product Too many rows