The JOIN operator lets you write SQL queries that retrieve data from multiple tables based on a specific join condition.
A SQL query walks into a bar and sees two tables.
He walks up to them and says:
'Can I join you?'
The JOIN operator works similarly to set operations in relational algebra.
Let’s say we have two tables: cats and candies. Each row in the cat table contains the ID of the candy that belongs to it — candy_id.
cat table
| id | name | candy_id |
|---|---|---|
| 1 | Mia | 3 |
| 2 | Astronaut Cat’s Brother | null |
| 3 | Astronaut Cat | 2 |
candy table
| id | name |
|---|---|
| 1 | Plutonian Praline |
| 2 | Explosive Comet |
| 3 | Universal Harmony |
null — a special value meaning «no data»; the cat with ID 2 has no candy.
Let’s build an SQL query that joins the cat and candy tables using the equality condition for the candy ID (candy_id = id) in both tables.
SELECT cat.name, candy.name -- select cat names and candy names
FROM cat -- from the cat table
JOIN candy ON cat.candy_id = candy.id; -- join with the candy table by matching the candy IDThe result of the query will be a joined table with rows merged from the cat and candy.
Joined cat and candy table
| cat.name | candy.name |
|---|---|
| Mia | Universal Harmony |
| Astronaut Cat | Explosive Comet |
Besides INNER JOIN, there are other ways to join tables:
LEFT JOIN includes all rows from the table listed before JOIN and the rows that match in both tables.
RIGHT JOIN includes all rows from the table listed after JOIN and the rows that match in both tables.
FULL JOIN includes all rows from both tables.
To select only rows that don’t appear in the intersection of the tables, you need to add this condition to the query:
WHERE table_name.column_name IS NULLLEFT JOIN with NULL
RIGHT JOIN with NULL
FULL JOIN with NULL
Write an SQL query that returns all cats and the candies that belong to them.
Logical operators are not supported by the simulator.
Database schema:
Уровень повышен!
