Inner joins
Combining rows from two or three tables with INNER JOIN … ON, naming tables with aliases, joining a table with itself and spotting the Cartesian product left by a forgotten condition.
- 20 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
A few cards from the deck
Inner join: what is it?
Show the answer
The operation that pairs the rows of two tables according to a condition and keeps only rows that have a partner.
A client without any order vanishes from the result of an inner join of client and commande: it has nobody to pair with.
Why does PostgreSQL reject this query?
```sql
SELECT id, nom
FROM commande
JOIN client ON commande.id_client = client.id;`- id exists in both tables: it must be written commande.id or client.id
- An inner join cannot be mixed with a two-column SELECT
- DISTINCT is missing before id to avoid duplicates
- ON must come before JOIN in the FROM clause
Show the answer
id exists in both tables: it must be written commande.id or client.id
The message is
column reference "id" is ambiguous. A column name present in several tables must be prefixed with the table or its alias.In the FROM clause, the join condition is written after the word …, and JOIN written on its own is the same as ….
Show the answer
In the FROM clause, the join condition is written after the word ON, and JOIN written on its own is the same as INNER JOIN.
FROM commande JOIN client ON commande.id_client = client.idis an inner join. The condition can also go in WHERE with a comma between the tables, but ON says plainly what links what.Join condition: what is it?
Show the answer
The predicate of the ON clause that says which left rows go with which right rows, most often a foreign key equal to a primary key.
ON commande.id_client = client.idlinks each order to its client. Without it, every combination is returned.