Introduction to Table Relationships
Table Relationships Introduction
In a database, table relationships connect data stored in different tables. Instead of putting everything in one table, we divide data into smaller tables and link them using keys.
For example:
users table
| id | name |
|---|---|
| 1 | Rahul |
| 2 | Sneha |
| 3 | Amit |
orders table
| order_id | user_id | amount |
|---|---|---|
| 101 | 1 | 500 |
| 102 | 1 | 300 |
| 103 | 2 | 700 |
Here, users.id is a primary key and orders.user_id is a foreign key.
This creates a one to many relationship because one user can have many orders.
Now let’s understand different JOIN types using these tables.
1. INNER JOIN
INNER JOIN returns only matching records from both tables.
Query:
SELECT users.name, orders.order_id, orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
Result:
| name | order_id | amount |
|---|---|---|
| Rahul | 101 | 500 |
| Rahul | 102 | 300 |
| Sneha | 103 | 700 |
Amit is not shown because he has no orders.
2. LEFT JOIN
LEFT JOIN returns all records from the left table and matching records from the right table.
Query:
SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;
Result:
| name | order_id |
|---|---|
| Rahul | 101 |
| Rahul | 102 |
| Sneha | 103 |
| Amit | NULL |
Amit appears even though he has no orders.
3. RIGHT JOIN
RIGHT JOIN returns all records from the right table and matching records from the left table.
Query:
SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;
Result:
| name | order_id |
|---|---|
| Rahul | 101 |
| Rahul | 102 |
| Sneha | 103 |
Since all orders have valid users, the result looks like INNER JOIN in this case.
4. FULL JOIN
FULL JOIN returns all records from both tables. If there is no match, SQL fills the missing side with NULL.
Query:
SELECT users.name, orders.order_id
FROM users
FULL JOIN orders
ON users.id = orders.user_id;
Result:
Shows:
- All users
- All orders
- NULL where no match exists
Note: MySQL does not directly support FULL JOIN.
5. SELF JOIN
SELF JOIN joins a table with itself.
Example: employees and managers in the same table.
employees table
| id | name | manager_id |
|---|---|---|
| 1 | Raj | NULL |
| 2 | Meena | 1 |
| 3 | Arjun | 1 |
Query:
SELECT A.name AS Employee, B.name AS Manager
FROM employees A
LEFT JOIN employees B
ON A.manager_id = B.id;
Result:
| Employee | Manager |
|---|---|
| Raj | NULL |
| Meena | Raj |
| Arjun | Raj |
6. CROSS JOIN
CROSS JOIN returns all possible combinations of rows.
Example Tables:
table1
| id |
|---|
| 1 |
| 2 |
table2
| letter |
|---|
| A |
| B |
Query:
SELECT *
FROM table1
CROSS JOIN table2;
Result:
| id | letter |
|---|---|
| 1 | A |
| 1 | B |
| 2 | A |
| 2 | B |
It creates combinations of every row.
Summary
- INNER JOIN → Only matching rows
- LEFT JOIN → All left table rows
- RIGHT JOIN → All right table rows
- FULL JOIN → All rows from both tables
- SELF JOIN → Table joined with itself
- CROSS JOIN → All possible combinations
Understanding these JOIN types helps you combine data correctly and write strong SQL queries.
Check out our resources!
- Bootstrap Templates: Explore our Bootstrap Projects section.
- Free E-Books: Download your Free E-Books here.
