Why do we need joins?

Imagine you are building an e-commerce application. Instead of storing everything in one table, relational databases usually split data into multiple related tables.

Users table

Orders table

The orders table stores User_ID, not the user's name. This avoids duplicate data.

If the business asks, "Show me user name plus product purchased", neither table alone can answer it. We need to combine both tables. That is what a join does.

What is a join?

A join combines rows from two or more tables based on a related column. Usually the related columns are a primary key and a foreign key.

Users.User_ID
    |
    v
Orders.User_ID

Types of joins

INNER JOIN

INNER JOIN returns only rows that match in both tables.

SELECT
  u.Name,
  o.Product
FROM Users u
INNER JOIN Orders o
ON u.User_ID = o.User_ID;

Output:

The monitor order is gone because User_ID = 5 does not exist in Users.

Memory trick:

INNER JOIN = only the intersection

LEFT JOIN

LEFT JOIN returns every row from the left table and matching rows from the right table. If no match exists, the right-side columns are NULL.

SELECT
  u.Name,
  o.Product
FROM Users u
LEFT JOIN Orders o
ON u.User_ID = o.User_ID;

Output:

Think:

LEFT JOIN = everything on the left + matching right

RIGHT JOIN

RIGHT JOIN is the opposite of LEFT JOIN. It keeps every row from the right table and matching rows from the left table.

SELECT
  u.Name,
  o.Product
FROM Users u
RIGHT JOIN Orders o
ON u.User_ID = o.User_ID;

Output:

The monitor order appears because it exists in Orders, even though there is no matching user.

FULL OUTER JOIN

FULL OUTER JOIN returns everything:

• All left rows.
• All right rows.
• Matching rows merged.

Example output:

Note: MySQL does not support FULL OUTER JOIN directly. It is commonly simulated with LEFT JOIN, RIGHT JOIN, and UNION.

CROSS JOIN

CROSS JOIN returns every possible combination.

If there are 2 users and 2 products, the result has 4 rows.

SELECT *
FROM Users
CROSS JOIN Products;

Formula:

Rows = A x B

Use it carefully because result size grows quickly.

SELF JOIN

A self join joins a table with itself.

Example: employees and managers.

Query:

SELECT
  e.Name AS Employee,
  m.Name AS Manager
FROM Employees e
JOIN Employees m
ON e.Manager_ID = m.ID;

The same table is treated as two logical tables using aliases.

Table aliases

Instead of writing:

SELECT Users.Name
FROM Users;

We write:

SELECT u.Name
FROM Users u;

Aliases make join queries shorter and easier to read.

ON vs WHERE

ON defines how tables should be matched.

WHERE filters the result after the join.

SELECT
  u.Name,
  o.Product
FROM Users u
JOIN Orders o
ON u.User_ID = o.User_ID
WHERE o.Product = 'Laptop';

• ON: match users with orders.
• WHERE: keep only laptop orders.

Business requirement mindset

Different joins answer different business questions.

The foreign key defines the relationship. The join decides which result set you want.

Interview notes

INNER JOIN vs LEFT JOIN

• INNER JOIN returns only rows with matching values in both tables.
• LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, the right-side columns are NULL.

Why do we use aliases?

Aliases make SQL queries shorter and are especially useful when joining multiple tables or performing self joins.

What is a SELF JOIN?

A self join joins a table with itself, usually with aliases, to represent relationships inside the same table.

Why is it called LEFT JOIN or RIGHT JOIN?

It depends on which table you write first.

FROM Users
LEFT JOIN Orders

Here Users is the left table and Orders is the right table.

Big picture

A foreign key only defines the relationship. It does not define what result you want.

The relationship is the road. The join tells SQL how to travel that road and which records to keep.