INNER JOIN

How JOIN, ON works

SELECT members.name, loans.loan_date
FROM members JOIN loans
  ON members.member_id = loans.member_id;

• Why is the data in two tables?

  So each fact is stored once. A loan only holds the book's id; the title lives in books. Change a title once and every loan shows the new one.

• What does JOIN do?

  FROM loans JOIN books ON loans.book_id = books.book_id

  It pairs each loan with the book whose id matches, so one row has columns from both tables.

• Why write loans.book_id?

  Both tables have a book_id. Writing the table name first says which one you mean. Without it, SQL says the name is ambiguous.

• What happens to rows with no match?

  JOIN (also written INNER JOIN) keeps only rows that match in both tables. A book nobody has borrowed is left out. The next lesson keeps them.

• How is this like Excel?

  It's XLOOKUP for every row at once: look up each loan's book_id in the books table and bring back the title.

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.