DataslopeInner Joins
Learn how SQLite INNER JOIN keeps only rows that have matching partners in both tables.
An inner join keeps only rows that match on both sides. If a row has no partner, it disappears from the result.
In SQLite, JOIN by itself means INNER JOIN.
Only matches survive
This data includes two unmatched things:
- Linus is a customer with no order.
- Order
104points to customer7, who is not in the customers table.
Ada and Grace appear because they have matching orders. Linus disappears because no order matches him. Order 104 disappears because customer 7 does not exist.
What happens to Linus in the query above?
He appears with zero for the order amount.
He is left out because he has no matching order.
He appears with the last order in the table.
The query fails because one customer has no order.
JOIN and INNER JOIN mean the same thing
Most people write just JOIN because inner join is the default.
You can write INNER JOIN when you want to be extra clear, especially near outer joins.
Choosing the matching columns
The most common inner join condition is:
child_table.foreign_key = parent_table.primary_keyThe query keeps author-book pairs where the book's author_id points to the author's id.
Aliases and column prefixes
Aliases make inner joins readable. Prefixing columns with aliases also avoids confusion when both tables have columns named id.
AS is optional for table aliases in SQLite, but it can make beginner queries easier to read.
Joining more than two tables
You can chain inner joins. Each JOIN ... ON ... adds another table and another matching rule.
The orders table sits in the middle: it points to both the customer and the product.
When inner joins are the right tool
Use an inner join when your question means with a matching partner:
- orders with their customers
- books with their authors
- students with their enrolled courses
- products that have actually sold
Check your understanding
What rows does an inner join return?
Every row from the left table, matched or not.
Only rows where the join condition finds a match in both tables.
Every row from both tables, with blanks for missing values.
Only rows with NULL values.
Which two SQLite keywords are equivalent here for a normal matching join?
LEFT JOIN and INNER JOIN
JOIN and INNER JOIN
WHERE and JOIN
ORDER BY and INNER JOIN
You want "products that have been sold." Which join is a natural fit?
A query from products only.
An inner join from products to order rows, keeping products with matching sales.
A left join that keeps all unsold products too.
A join with no matching condition.
Why prefix columns like c.id and o.id in a join?
SQLite refuses all unprefixed columns.
Prefixes tell SQLite and the reader which table's column you mean.
Prefixes turn ids into foreign keys.
Prefixes sort the output.