How are primary and foreign keys used in joins?

This is where the magic happens! Primary Keys (PK) and Foreign Keys (FK) are the "glue" used in JOIN operations to combine data from multiple tables into a single result.

When you perform a JOIN, you are telling the database: "Match the rows where the Foreign Key in the first table equals the Primary Key in the second table."

The Practical Example

Using the tables from your experiment, let's say you want to see a list of items along with their category names (instead of just the ID numbers).

You would write a query like this:

SELECT 
    inventory_items.item_name, 
    inventory_items.unit_price, 
    categories.category_name
FROM inventory_items
JOIN categories ON inventory_items.category_id = categories.category_id;

How the database processes this:

1. The Link: The ON clause identifies the relationship: inventory_items.category_id (the Foreign Key) matches categories.category_id (the Primary Key).
2. The Comparison: The database looks at an item (e.g., "Gaming Laptop" with category_id 5).
3. The Lookup: It goes to the categories table and looks for the row where the Primary Key is exactly 5.
4. The Result: It finds "Electronics" and combines that information into one row for your report.

Why this structure is better than one big table:

• Efficiency: The database engine is highly optimized to match PKs and FKs. It is much faster than searching for text strings.
• Accuracy: Since the Foreign Key must match a Primary Key, you are guaranteed that the "Gaming Laptop" is linked to a valid, existing category.
• Storage: You aren't repeating the long "description" or "active status" of the category for every single item; you just link to it when you need it.

In short: Primary/Foreign Keys define the relationship, and JOINs are the tool use use to navigate that relationship.

Would you like to try running a JOIN command once you've inserted some data?