Why Your Data Lives in Different Houses
Imagine your contacts live in one app, and your calendar events live in another. They're separate but related. A good database works the same way; customer information is in a `CUSTOMERS` table, and movie rental records are in a `RENTAL_HISTORY` table. This is efficient and organized.
But how do you connect them to see which customer rented which movie? That's where Keys and JOINs come in. They are the fundamental tools for combining data from different tables to answer real business questions.
The Keys to the Kingdom 🔑
Keys are special columns that create relationships between tables.
1. Primary Key
A Primary Key is a column that contains a **unique identifier** for every single row in a table. Think of it like a Social Security Number for data—no two rows can have the same one.
In our database, `CUSTOMERS.customer_id` is a primary key.
2. Foreign Key
A Foreign Key is a copy of a Primary Key from one table that's placed in another table to **create a link**. It's the "connector" piece.
The `customer_id` column in the `RENTAL_HISTORY` table is a foreign key that points back to the primary key in `CUSTOMERS`.
Bringing It All Together with JOINs
A `JOIN` is the SQL command you use to combine rows from two or more tables based on their related key columns.
INNER JOIN
Returns only the records that have matching values in both tables. This is the most common type of join.
Business Question: "Show me only the customers who have actually rented a movie."
SELECT c.first_name, c.last_name, rh.rental_date
FROM customers c
INNER JOIN rental_history rh ON c.customer_id = rh.customer_id;LEFT JOIN
Returns **all** records from the left table (`customers`), and the matched records from the right table (`rental_history`). If there is no match, the result is NULL on the right side.
Business Question: "List all of our customers, and show their rental dates if they have any. This helps us find customers who have never rented!"
SELECT c.first_name, c.last_name, rh.rental_date
FROM customers c
LEFT JOIN rental_history rh ON c.customer_id = rh.customer_id;Refining Your Queries with Logical Operators
Logical operators are used in the `WHERE` clause to combine multiple conditions and filter your data precisely.
- AND: Returns a record if **all** conditions are true.
`WHERE category = 'SCIFI' AND rating = 'R'` - OR: Returns a record if **any** of the conditions are true.
`WHERE category = 'COMEDY' OR category = 'DRAMA'` - NOT: Returns records that do **not** meet the condition.
`WHERE rating NOT IN ('G', 'PG')`
Putting It All Together
Let's solve a real business problem by combining multiple JOINs and an `AND` operator.
Business Question: "Get the first and last names of all customers who rented a 'COMEDY' movie in 2010."
SELECT DISTINCT c.first_name, c.last_name
FROM customers c
INNER JOIN rental_history rh ON c.customer_id = rh.customer_id
INNER JOIN media me ON rh.media_id = me.media_id
INNER JOIN movies m ON me.title_id = m.title_id
WHERE m.category = 'COMEDY'
AND EXTRACT(YEAR FROM rh.rental_date) = 2010;