Code Self Study Forum

Notes On Joins and Many to Many SQL Tables - How to store complex SQL tables - Relational Tables 101

I have been using SQL for years, but I always used it as an excel table.

The real power lies in relational tables with ids that link.

This means having a shared id between tables.

Why do this? It reduces data repetition, and null values.

So for example assume I have a food diary.

I have

User_Table
id
name

Food_Table
id
food

Union_Table
id
name_id
food_id

I might have users that sign up, but never enter a food. If I had the food id as a row in the user table, then it would have a null value.

I can join any names that have foods using the union table.

One other thing I learned is that each table should have the id as a primary key.
Then in the union table, the name_id, and food_id should be noted as foreign keys, as such:

CREATE TABLE Union_Table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_id INT,
    food_id INT,
    FOREIGN KEY(name_id) REFERENCES User_Table(id),
    FOREIGN KEY(food_id) REFERENCES Food_Table(id)
);

Defining a foreign key like this will link the keys for SQL. This will prevent a number being entered in the foreign key that is not also there for the primary key in the reference table.

You can also use functions like Cascade delete to delete all instances of the linked key.

Now, how would I query all these tables to make one result or table?

SELECT 
    name,
    food
FROM Food_Table
INNER JOIN Union_Table
    ON Food_Table.id = Union_Table.food_id
INNER JOIN User_Table
    ON User_Table.id = Union_Table.name_id
ORDER BY name;

These are my notes so far. I will try come back and add more. I invite you to ask any questions. :slight_smile:

2 Likes