FULL OUTER JOIN Explained

A FULL OUTER JOIN:

• Returns all records from both tables.
• If there’s a match between A and B, it combines them.
• If there’s no match, you still get the row — with NULL in the missing side.

Summary:

Situation	FULL OUTER JOIN behavior
Row matches in both A and B	Combine them.
Row in A, but no match in B	Show A row + NULL for B.
Row in B, but no match in A	Show B row + NULL for A.

---

Example

Customers

customer_id	name
1	Alice
2	Bob
3	Charlie

Orders

order_id	customer_id	product
101	1	Laptop
102	2	Smartphone
103	4	Tablet

---

Query:

SELECT Customers.customer_id, Customers.name, Orders.order_id, Orders.product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Result:

customer_id	name	order_id	product
1	Alice	101	Laptop
2	Bob	102	Smartphone
3	Charlie	NULL	NULL
NULL	NULL	103	Tablet

✅ What’s happening:

• Alice and Bob match an order — perfect, rows are merged.
• Charlie has no orders — so we see his row with NULL for order_id and product.
• Order 103 is for customer_id = 4, but no such customer — so customer_id and name are NULL on the left side.

---

Why Use FULL OUTER JOIN?

• When you want to see everything: all customers, all orders — even if there’s no match.
• Useful for reporting, audits, or finding mismatches (like customers without orders, or orders without customers).

For example:

• Customers who haven’t ordered anything yet.
• Orders placed but no matching customer exists (e.g., data entry errors).

---

FULL OUTER JOIN = LEFT JOIN + RIGHT JOIN

You could think of it as:

SELECT * FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id
UNION
SELECT * FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

But FULL OUTER JOIN does this in one step.

---

Limitations

• Not supported by all databases natively (e.g., MySQL doesn’t support FULL OUTER JOIN directly — you have to simulate it with UNION of LEFT JOIN and RIGHT JOIN).

---

Visual (Venn Diagram)

     🟢 (Customers)      🔵 (Orders)
         \_________intersection_________/

FULL OUTER JOIN = 🟢 everything in both circles 🔵

---

Would you like me to show how to simulate a FULL OUTER JOIN in MySQL (since MySQL doesn’t have a direct FULL OUTER JOIN keyword)? 🚀✨

Main Types of JOINs

JOIN Type	What It Does
INNER JOIN	Returns rows where there’s a match in both tables.
LEFT JOIN	Returns all rows from the left table, plus matches from the right (NULL if no match).
RIGHT JOIN	Returns all rows from the right table, plus matches from the left (NULL if no match).
FULL OUTER JOIN	Returns all rows from both tables — matches where possible, NULLs where no match.
CROSS JOIN	Returns every combination of rows — Cartesian product (no ON condition).
SELF JOIN	Join a table to itself — used when you need to compare rows within the same table.

---

1. INNER JOIN

• Only rows with matching keys in both tables are returned.

SELECT * FROM A
INNER JOIN B ON A.id = B.a_id;

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager

➡️ Only rows where A.id = B.a_id.

2. LEFT JOIN (LEFT OUTER JOIN)

• All rows from the left table, matched rows from right table, NULLs where no match.

SELECT * FROM A
LEFT JOIN B ON A.id = B.a_id;

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager
2	Bob	NULL	NULL	NULL

➡️ Bob is shown even though he has no match.

3. RIGHT JOIN (RIGHT OUTER JOIN)

• All rows from the right table, matched rows from left table, NULLs where no match.

SELECT * FROM A
RIGHT JOIN B ON A.id = B.a_id;

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager
NULL	NULL	11	5	Engineer

➡️ Even if B.a_id = 5 has no match in A, it shows up with NULLs from A.

4. FULL OUTER JOIN

• All rows from both tables — match if possible, NULLs if not.

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager
2	Bob	NULL	NULL	NULL
NULL	NULL	11	5	Engineer

➡️ Combines the behavior of LEFT and RIGHT JOINs.

5. CROSS JOIN

• Every row of A is paired with every row of B (no condition).

SELECT * FROM A
CROSS JOIN B;

If A has 3 rows and B has 2 rows ➔ 3 × 2 = 6 rows result.

A.id	A.name	B.id	B.title
1	Alice	10	Manager
1	Alice	11	Engineer
2	Bob	10	Manager
2	Bob	11	Engineer

6. SELF JOIN

• Join a table to itself — useful for hierarchies, like employees and managers.

SELECT A.name AS employee, B.name AS manager
FROM Employees A
JOIN Employees B ON A.manager_id = B.id;

employee	manager
Bob	Alice

➡️ Bob’s manager is Alice.

---

Summary Table

JOIN Type	Key Idea
INNER JOIN	Matches in both tables.
LEFT JOIN	All left rows, matched right rows.
RIGHT JOIN	All right rows, matched left rows.
FULL OUTER JOIN	All rows from both sides.
CROSS JOIN	All combinations — Cartesian product.
SELF JOIN	Table joined to itself.

🎨 Quick Sketch in Text

JOIN Type	Diagram	Description
INNER JOIN	(🟢 ∩ 🔵)	Only common (intersection).
LEFT JOIN	🟢 + (🟢 ∩ 🔵)	All A, and matches from B (else NULL).
RIGHT JOIN	🔵 + (🟢 ∩ 🔵)	All B, and matches from A (else NULL).
FULL OUTER JOIN	(🟢 ∪ 🔵)	All from A and B, NULL if no match.
CROSS JOIN	🟢 × 🔵	Every A row with every B row (Cartesian).
SELF JOIN	A ↔ A	A table joined to itself.

Denormalization is the process of intentionally adding redundancy into a database — basically undoing some of the normalization — in order to improve read performance.

Key Idea:

• In normalization, we split data into multiple related tables to reduce redundancy.
• In denormalization, we combine tables or add redundant columns to make reads faster (at the cost of some redundancy).

Why Denormalize?

1. Performance:
   • Complex queries in a fully normalized database might require multiple JOIN operations.
   • Joins are expensive — they cost time and resources.
   • Denormalization can speed up queries by reducing or eliminating joins.
2. Simpler Queries:
   • Fewer joins mean easier, faster queries.
3. Precomputed Data:
   • Sometimes, denormalization involves storing computed values like totals, counts, or derived data to avoid recalculating them.

Example

Normalized Tables (good design, normalized to 3NF):

Customers

customer_id	customer_name
1	Alice
2	Bob

Orders

order_id	customer_id	order_date
101	1	2025-06-01
102	2	2025-06-02

To get the customer’s name with the order:

SELECT o.order_id, c.customer_name, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id;

Denormalized Table:

order_id	customer_id	customer_name	order_date
101	1	Alice	2025-06-01
102	2	Bob	2025-06-02

Now:

• customer_name is stored redundantly in the Orders table.
• No JOIN needed to fetch the customer name!

But:

• If Bob changes his name, you have to update it everywhere Bob’s name appears — risk of inconsistency.

Advantages vs Disadvantages

Advantages	Disadvantages
Faster reads (less joins).	Redundancy — same data in many places.
Simpler queries (fewer joins to write).	Harder to update — more maintenance.
Precomputed values can improve speed.	Risk of inconsistencies.
Better for reporting, analytics systems.	More storage space used.

---

In Simple Words:

• Normalization = Break apart to avoid redundancy. Good for updates and data consistency.
• Denormalization = Copy data around to make reads faster. Good for performance and speed.

In Simple Words:

• Normalization = Break apart to avoid redundancy. Good for updates and data consistency.
• Denormalization = Copy data around to make reads faster. Good for performance and speed.

Real-World Analogy

• Normalized: You have one file cabinet for customers, another for orders — to find a customer’s order, you cross-reference both.
• Denormalized: You make a folder for each order and also write the customer name on each one — easier to find, but if the customer changes their name, you have to update every folder manually!

Third Normal Form (3NF)

Goal:

• Be in 2NF AND
• Eliminate transitive dependencies (non-key columns must depend only on the primary key, not on other non-key columns).

---

A table in 2NF but NOT in 3NF:

student_id	student_name	student_zipcode	zipcode_city
1	Alice	12345	New York
2	Bob	67890	Los Angeles

Problem:

• zipcode_city depends on student_zipcode, not directly on student_id.
• This is a transitive dependency: student_id → student_zipcode → zipcode_city.

---

Fix — Table in 3NF:

Split into two tables:

Students

student_id	student_name	student_zipcode
1	Alice	12345
2	Bob	67890

Zipcodes

student_zipcode	zipcode_city
12345	New York
67890	Los Angeles

✅ Now, all non-key columns depend only on the primary key.

---

Summary Table

Normal Form	What It Fixes	Requirement
1NF	Repeating groups, multivalued columns	Atomic values (no lists or sets inside fields).
2NF	Partial dependencies	No non-key column depends on part of a composite key.
3NF	Transitive dependencies	No non-key column depends on another non-key column.

---

🎯 In Simple Words:

• 1NF: One value per cell.
• 2NF: No partial key dependency.
• 3NF: No indirect dependency.

What is a Partial Dependency?

A partial dependency happens when a column (like student_name) depends on part of a composite key (like student_id), not the whole key (student_id, course).

---

Let’s See the Table Again

Imagine this table:

student_id	course	student_name
1	Math	Alice
1	History	Alice
2	Math	Bob

Suppose:

• Primary Key = (student_id, course) → both columns together uniquely identify a row.
• This means both student_id and course are needed to tell one row apart from another.

---

What depends on what?

• student_name — it depends only on student_id.
  • Wherever student_id = 1, the name is always “Alice”, no matter what course.
  • It has nothing to do with which course the student takes.

But:

• In a properly normalized table, all non-key columns should depend on the whole key — not just a part of it.

---

Why is this bad?

• Redundancy: “Alice” is repeated for every course Alice takes.
• Update problem: If Alice changes her name, you must update it in every row where student_id = 1.
• Data inconsistency: You could accidentally have “Alice” for Math and “Alise” for History — two different names for the same student!

Real-world Analogy

Think of it like this:

• Suppose you have a rental system where the key is (PersonID, CarID) because one person can rent multiple cars.
• But you store the person’s name with each rental.
• The name depends only on the person — not on which car they rent.

You shouldn’t repeat the name for each rental; you should store it once in a Person table!

Correct Fix — Split the Table

Students (student_id → student_name):

student_id	student_name
1	Alice
2	Bob

Enrollments (student_id + course):

student_id	course
1	Math
1	History
2	Math

Now:

• student_name depends only on student_id (good — and we don’t repeat it).
• course depends on both student_id and course (good — this pair is unique).

---

Key Point

In 2NF, every non-key column must depend on the whole composite key — not just part of it.

If you can say:

“This column only needs student_id to be known, not the full (student_id, course)” — then you have a partial dependency, and you are NOT in 2NF.

First Normal Form (1NF)

Goal:

• Eliminate repeating groups.
• Make sure each field contains atomic (indivisible) values.

---

A table NOT in 1NF:

student_id	name	courses
1	Alice	Math, History
2	Bob	Math

Problem:

• courses has multiple values in one column — this is not atomic.

---

Fix — Table in 1NF:

Break it into one row per course:

student_id	name	course
1	Alice	Math
1	Alice	History
2	Bob	Math

✅ Each field contains atomic values.
✅ No multi-valued columns.

Second Normal Form (2NF)

Goal:

• Be in 1NF AND
• Eliminate partial dependencies (no attribute should depend only on part of a composite primary key).

---

A table in 1NF but NOT in 2NF:

Assume the primary key is (student_id, course).

student_id	course	student_name
1	Math	Alice
1	History	Alice
2	Math	Bob

Problem:

• student_name depends only on student_id, not on the whole key (student_id, course).
• This is a partial dependency.

---

Fix — Table in 2NF:

Split into two tables:

Students

student_id	student_name
1	Alice
2	Bob

Enrollments

student_id	course
1	Math
1	History
2	Math

✅ Now every non-key column depends on the whole primary key.

Third Normal Form (3NF)

Goal:

• Be in 2NF AND
• Eliminate transitive dependencies (non-key columns must depend only on the primary key, not on other non-key columns).

A table in 2NF but NOT in 3NF:

student_id	student_name	student_zipcode	zipcode_city
1	Alice	12345	New York
2	Bob	67890	Los Angeles

Problem:

• zipcode_city depends on student_zipcode, not directly on student_id.
• This is a transitive dependency: student_id → student_zipcode → zipcode_city.

---

Fix — Table in 3NF:

Split into two tables:

Students

student_id	student_name	student_zipcode
1	Alice	12345
2	Bob	67890

Zipcodes

student_zipcode	zipcode_city
12345	New York
67890	Los Angeles

✅ Now, all non-key columns depend only on the primary key.

---

Summary Table

Normal Form	What It Fixes	Requirement
1NF	Repeating groups, multivalued columns	Atomic values (no lists or sets inside fields).
2NF	Partial dependencies	No non-key column depends on part of a composite key.
3NF	Transitive dependencies	No non-key column depends on another non-key column.

---

🎯 In Simple Words:

• 1NF: One value per cell.
• 2NF: No partial key dependency.
• 3NF: No indirect dependency.

What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy (duplicate data) and improve data integrity.

It involves breaking a big, messy table into smaller, more focused tables and linking them through relationships (usually using primary and foreign keys).

---

Key Goals of Normalization:

• Eliminate redundant data — avoid storing the same data in multiple places.
• Ensure data dependencies make sense — data should be stored logically.
• Protect against anomalies:
  • Insertion anomaly: Trouble inserting data without other required data.
  • Update anomaly: Changing data in one place but not another, causing inconsistency.
  • Deletion anomaly: Losing important data because of deleting other data.

Why is Normalization Important?

1. Reduces data redundancy:
   • Saves storage space.
   • Keeps data clean and consistent.
2. Improves data integrity:
   • Avoids conflicting information in the database.
   • Ensures changes are propagated correctly.
3. Makes databases easier to maintain:
   • Easier to add, update, or delete data.
   • Fewer places to update if something changes.
4. Optimizes query performance (sometimes):
   • Smaller tables mean faster queries.
   • But — sometimes over-normalization can cause too many joins and slow things down (so we balance it carefully).

Example: Why Normalize?

Imagine a table like this:

student_id	student_name	course_1	course_2
1	Alice	Math	History
2	Bob	Math	NULL

Problems:

• What if a student takes 3 courses? We’d have to add more columns.
• If you update Math to Advanced Math, you have to update multiple places.
• If you delete Bob, you lose record of Math.

After Normalization:

Students Table:

student_id	student_name
1	Alice
2	Bob

Courses Table:

course_id	course_name
1	Math
2	History

Enrollments Table (relationship table):

student_id	course_id
1	1
1	2
2	1

Now:

• Courses are not duplicated.
• We can add more courses easily.
• No update anomalies — change Math name once in Courses.

In Short:

Without Normalization	With Normalization
Redundant data	Reduced redundancy
Update anomalies	Easier updates
Hard to scale	Easier to add new data
Risk of inconsistencies	Improved data integrity

DELETION ANOMALY

Let’s go back to this non-normalized table:

student_id	student_name	course_1	course_2
1	Alice	Math	History
2	Bob	Math	NULL

Now imagine you delete Bob:

DELETE FROM table WHERE student_id = 2;

Now the table looks like:

student_id	student_name	course_1	course_2
1	Alice	Math	History

➡️ Problem:

• Suppose Bob was the only student who had Math as their course (let’s imagine Alice drops Math later).
• When you delete Bob, you are not just deleting the student — you are also deleting the only place where “Math” was stored in the table.
• You’ve lost track of the fact that the course “Math” even existed because courses and students are all stored together in one row.

This is called a deletion anomaly.

---

With Normalization

When you separate Students and Courses into different tables:

Courses Table:

course_id	course_name
1	Math
2	History

Now, even if you delete Bob from the Students table:

DELETE FROM Students WHERE student_id = 2;

The Courses table still keeps the record that “Math” is a valid course — no data loss!

---

In simple terms:

Without normalization:

• Some important data (like the course “Math”) exists only inside student rows.
• Deleting the student can accidentally delete information about the course too.

With normalization:

• Students and courses are separate.
• Deleting a student deletes only the student, not the course information.

1. Insertion Anomaly

An insertion anomaly happens when the database design prevents you from inserting data unless you have extra, unnecessary information.

---

Example without normalization:

student_id	student_name	course_name
1	Alice	Math
2	Bob	History

---

Problem:

Suppose you want to add a new course Physics that no students have enrolled in yet.

How would you do it?

You can’t insert:

INSERT INTO table (student_id, student_name, course_name)
VALUES (NULL, NULL, 'Physics');

You can’t insert a course without a student.

The database forces you to either put fake student data or leave it empty, which can break integrity.

With normalization:

Separate the tables:

Courses Table:

course_id	course_name
1	Math
2	History

Now you can easily:

INSERT INTO Courses (course_id, course_name) VALUES (3, 'Physics');

✅ No need for any student — you can add courses freely.

2. Update Anomaly

An update anomaly happens when the same data is stored in multiple places and you have to update it in all of them manually — or risk inconsistency.

---

Example without normalization:

student_id	student_name	course_name
1	Alice	Math
2	Bob	Math
3	Charlie	Math

Suppose the course “Math” is now renamed to “Advanced Mathematics.”

Problem:

You have to run:

UPDATE table SET course_name = 'Advanced Mathematics' WHERE course_name = 'Math';

But what if:

• You forget to update one row?
• You make a mistake in your WHERE condition?

Result: some students show “Math,” others show “Advanced Mathematics” — data inconsistency!

With normalization:

Courses Table:

course_id	course_name
1	Math

Enrollments Table:

student_id	course_id
1	1
2	1
3	1

Now, to rename the course:

UPDATE Courses SET course_name = 'Advanced Mathematics' WHERE course_id = 1;

✅ Update one place, and all students automatically point to the updated course name.

No inconsistency.

You forget to update one row?

Imagine you have this non-normalized table:

student_id	student_name	course_name
1	Alice	Math
2	Bob	Math
3	Charlie	Math

Now, suppose you manually update:

UPDATE table SET course_name = 'Advanced Mathematics' WHERE student_id = 1;

• You updated only Alice‘s row.
• You forgot to update Bob and Charlie.

Result:

student_id	student_name	course_name
1	Alice	Advanced Mathematics
2	Bob	Math
3	Charlie	Math

Now your database is inconsistent:

• Alice has the new course name.
• Bob and Charlie still have the old name.

---

---

You make a mistake in your WHERE condition?

Let’s say you want to update all rows where the course is “Math”:

UPDATE table SET course_name = 'Advanced Mathematics' WHERE course_name = 'Math';

But what if you accidentally write:

UPDATE table SET course_name = 'Advanced Mathematics' WHERE course_name = 'Mat';  -- typo!

• Now the database finds zero rows matching 'Mat', so nothing changes.
• You thought the update worked, but actually no rows got updated.

Or worse, what if you wrote:

UPDATE table SET course_name = 'Advanced Mathematics' WHERE student_id = 2;

UPDATE table SET course_name = 'Advanced Mathematics' WHERE student_id = 2;

• Only Bob’s course name would be updated — Alice and Charlie stay unchanged.

---

Why is this bad?

• Human error: You have to trust that every update touches exactly the right rows — but humans make mistakes.
• Inconsistency: Different rows now have different values for what should be the same course.

---

How Normalization helps

If you had a separate Courses table:

course_id	course_name
1	Math

and students just point to course_id, you would do:

UPDATE Courses SET course_name = 'Advanced Mathematics' WHERE course_id = 1;

✅ Only one row to update.
✅ No chance to forget or miss any student.
✅ All students who point to course_id = 1 automatically see the updated course name.

Summary

Without Normalization	Risk	With Normalization	Safe!
Duplicate course names in multiple rows	You can forget to update some rows	Single source of truth	Only one place to update

In Short:

Anomaly	Problem without normalization	Solved by normalization
Insertion	Can’t insert a course without a student.	Separate course table — insert freely.
Update	Must update the same value in many rows, risking inconsistency.	Update only once in a separate table.

INNER JOIN:

• Returns only the rows that have matching values in both tables.
• If there’s no match between the two tables, that row is excluded from the result.

LEFT JOIN (or LEFT OUTER JOIN):

• Returns all rows from the left table.
• If there’s a match in the right table, it returns the matching row.
• If there’s no match, it still returns the row from the left table, but with NULLs for columns from the right table.

---

Example

Given two tables:

Customers

customer_id	name
1	Alice
2	Bob
3	Charlie

Orders

order_id	customer_id	product
101	1	Laptop
102	2	Smartphone

INNER JOIN example:

SELECT Customers.name, Orders.product
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Result:

name	product
Alice	Laptop
Bob	Smartphone

• Only Alice and Bob appear — because they have matching orders.
• Charlie doesn’t have an order, so he’s not shown.

LEFT JOIN example:

SELECT Customers.name, Orders.product
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

Result:

name	product
Alice	Laptop
Bob	Smartphone
Charlie	NULL

• Alice and Bob have matching orders — so we see their orders.
• Charlie has no matching order — but he still shows up, and product is NULL.

In Short:

JOIN Type	Rows Returned
INNER JOIN	Only where there is a match in both tables.
LEFT JOIN	All from left table, matched or not (fill NULLs if no match).

🎯 Analogy:

• INNER JOIN is like an intersection — show only what both have in common.
• LEFT JOIN is like a left sweep — show everything from the left, and match from the right if you can, otherwise leave NULL.

When you set up a foreign key with ON DELETE CASCADE, it means:

• If you delete a row in the parent table (the table with the primary key), all related rows in the child table (the table with the foreign key) are automatically deleted.

This keeps the database consistent and avoids “orphan” records (child records without a parent).

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
    ON DELETE CASCADE
);

How it works:

• Users is the parent table.
• Orders is the child table.
• user_id in Orders references user_id in Users.
• If you delete a user, all their orders are deleted too.

Sample Data:

INSERT INTO Users (user_id, username) VALUES (1, 'Alice'), (2, 'Bob');

INSERT INTO Orders (order_id, user_id, order_date) VALUES 
(101, 1, '2025-06-01'),
(102, 1, '2025-06-02'),
(103, 2, '2025-06-03');

Tables now:
Users

user_id	username
1	Alice
2	Bob

Orders

order_id	user_id	order_date
101	1	2025-06-01
102	1	2025-06-02
103	2	2025-06-03

---

Now, if we run:

DELETE FROM Users WHERE user_id = 1;

What happens?

• Alice is deleted from Users.
• Automatically, both her orders (order_id 101 and 102) are deleted from Orders because of ON DELETE CASCADE.

---

Resulting tables:
Users

user_id	username
2	Bob

Orders

order_id	user_id	order_date
103	2	2025-06-03

---

Without ON DELETE CASCADE

If you didn’t have ON DELETE CASCADE and tried to delete Alice:

• The database would throw an error because there are Orders rows that still reference her user_id.

A foreign key is a column (or a set of columns) in a table that creates a link between data in two tables. It acts as a reference to a primary key in another table.

Key points about foreign keys:

• Referential Integrity: The foreign key ensures that the value in the column must match a value in the referenced table’s primary key (or a unique key).
• Relationships: It’s used to define relationships between tables (e.g., one-to-many, many-to-one).
• Validation: You cannot insert a value into a foreign key column if that value doesn’t exist in the referenced table.
• Cascading: You can define actions like ON DELETE CASCADE or ON UPDATE CASCADE to automatically delete or update dependent rows.

Example:

Suppose you have two tables, Users and Orders.

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Users.user_id is the primary key.

Orders.user_id is a foreign key that references Users.user_id.

This means every user_id in Orders must exist in the Users table.

Visual:

Users	Orders
user_id	username
1	Alice
2	Bob

If you try to insert an order with user_id = 3, but user_id 3 does not exist in Users, the database will reject the insert.