Sql · Inner Join
Table relationships
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Why We Use Multiple Tables
In most databases, data is organized into multiple related tables rather than a single large table. This organization technique is called normalization and has several important advantages.
Let's look at our example. Instead of a single large table containing all information about trainers and their Codémon, we have three specialized tables:
1. trainers - Information about each trainer
2. codemon - Information about each Codémon
3. ownership - Information about which trainers own which Codémon
Benefits of Using Multiple Tables
1. Eliminates data redundancy - Each piece of information is stored only once
2. Prevents update anomalies - When data changes, you only need to update it in one place
3. Improves data integrity - Related data stays consistent
4. Enables more flexible queries - Allows combinations of data in ways not initially anticipated
Exercise 2 — Advantages of Multiple Tables
What would be a potential problem if all Codémon and trainer data were stored in a single table?
Options
• We would need to store the same trainer information repeatedly for each Codémon they own
• The database would run faster with all data in one place
• We would need more tables to store the data properly
• SQL couldn't handle queries across trainer and Codémon data
Hint : Think about what happens when a trainer owns multiple Codémon and how that information would be structured in a single table.
Exercise 3 — Primary and Foreign Keys
When we split data across multiple tables, we need a way to connect related information. This is done using primary keys and foreign keys.
Primary Key: A column (or set of columns) that uniquely identifies each row in a table. In our database:
-
trainer_id
is the primary key in the trainers table
-
codemon_id
is the primary key in the codemon table
-
ownership_id
is the primary key in the ownership table
Foreign Key: A column that references the primary key of another table, creating a relationship between the tables. In our database:
-
trainer_id
in the ownership table is a foreign key referencing trainers
-
codemon_id
in the ownership table is a foreign key referencing codemon
In the image, we can see that the trainer with ID 0 owns Codemon with ID 0 and 1. Those two correspond to primary key ownership 0 and 1.
Types of Relationships
- One-to-One: Each record in Table A relates to exactly one record in Table B
- One-to-Many: Each record in Table A can relate to multiple records in Table B
- Many-to-Many: Multiple records in Table A can relate to multiple records in Table B (requires a junction table like our ownership table)
Exercise 4 — Match Keys with Their Purpose
Match each database concept with its correct description:
Items to match
• Primary Key
• Foreign Key
• One-to-Many Relationship
• Junction Table
Definitions
• Uniquely identifies each row in a table
• References a primary key in another table
• A trainer can own multiple Codémon
• Connects two tables in a many-to-many relationship
Exercise 5 — Understanding Table Relationships
In our Codémon database, what is the role of the trainer_id column in the ownership table?
Options
• It's a foreign key that links to the trainers table
• It's the primary key of the ownership table
• It's just a regular column with no special relationship function
• It's a duplicate copy of the trainer's data
Hint : Consider the purpose of columns that reference other tables and how they establish relationships between tables.
Exercise 6 — Why We Need Joins
When data is spread across multiple tables, we need a way to bring related information back together for queries. This is why we need joins.
For example, if we want to see a list of trainers and their Codémon, we need information from three tables:
1.
trainers
- For the trainer names
2.
codemon
- For the Codémon names and types
3.
ownership
- For the relationships between them
Without joins, we would need to run separate queries and manually match the data in our application code, which would be inefficient and error-prone.
Here's a visual representation of what we're trying to achieve with joins:
The Power of Joins
Joins allow us to:
- Query related data across tables in a single operation
- Filter based on conditions involving multiple tables
- Aggregate data across relationships
- Create reports that show the complete picture
Exercise 7 — Why Use Joins?
If you wanted to create a report showing all trainers and the Codémon they own, why would you need to use joins?
Options
• The data is stored in multiple related tables and joins allow you to combine it
• Joins are required for all SQL queries, even with a single table
• You could use separate queries instead of joins with the same efficiency
• The database wouldn't allow access to multiple tables in a single query without joins
Hint : Think about where the trainer data and Codémon data are stored, and how you would bring them together.
Exercise 8 — Matching Tables and Information
Match each piece of information with the table where it is stored in our database:
Items to match
• Trainer's hometown
• Codémon's type
• Date a Codémon was caught
• Which trainer owns which Codémon
Definitions
• trainers
• codemon
• ownership
• ownership
Exercise 9 — Finding Data Across Tables
If you wanted to create a report showing the names of trainers and the names of their Codémon, which tables would you need to join?
Options
• trainers, ownership, and codemon
• Only trainers and codemon
• Only ownership and codemon
• Only trainers and ownership
Hint : Think about which tables contain the trainer names, which contain the Codémon names, and which connect them together.
Exercise 10 — Relationship Identification
What type of relationship exists between the trainers and codemon tables in our database?
Options
• Many-to-many (many trainers can own many Codémon)
• One-to-one (each trainer owns exactly one Codémon)
• One-to-many (each Codémon can have only one trainer)
• No relationship (the tables are completely independent)
Hint : Examine the structure of the three tables and think about how trainers and Codémon relate to each other through the ownership table.