Working with JOINS
Designing a normalized database is all about is all about splitting data into separate tables in order to optimize storage space and to reduce redundant data. But in order to keep that data useful we have to have a way to bring all of that data together again. The SQL language gives us the ability to JOIN tables together. The Northwind database has 12 related tables that each store specific information. But by just looking at the data in each table it is difficult to know how that data is related. For instance, it is not possible to immediately know which customer an order belongs to; or what products were ordered in each order. We must look up each of those details separately in the respective tables. Image: Northwind Traders Database Diagram
Updated
Database Relationships
- Database Relationships: an overview (Apprentice): The three kinds of relationship between tables (one-to-many, one-to-one and many-to-many) and how a diagram shows each.
- One-to-Many (Apprentice): How a one-to-many relationship links many child rows to one parent row through a foreign key, like a customer and their orders.
- One-to-One (Apprentice): How a one-to-one relationship uses the child table's primary key as its foreign key, and why you would move rarely used data into its own table.
- Many-to-Many (Journeyman): Why two tables can't link many-to-many directly, and how an associative (junction) table between them turns it into two one-to-many relationships.
Joining Tables
- Joining Tables: an overview (Apprentice): The two kinds of join, inner and outer, and the small chores database (person and task) used to compare them.
- INNER JOIN (Journeyman): How an INNER JOIN combines two tables and returns only the rows that have a match in both.
- OUTER JOIN (Journeyman): How LEFT, RIGHT and FULL outer joins keep unmatched rows from one or both tables, and how to get a full join in MySQL, which has no FULL OUTER JOIN.
Joining Multiple Tables
- Joining Multiple Tables (Journeyman): How to join three or more tables in one query by following the keys that link them, from customers to the categories of the products they ordered.