Join allows combining records from multiple tables.
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
For join concepts, we will take the given tables as an example.
| id | username | password | |
| 1 | Aman | aman@gmail.com | 12345 |
| 2 | Kamal | kamal@gmail.com | 12345 |
| 3 | Om | Om@gmail.com | 12345 |
| id | user_id | title | post |
| 1 | 1 | What is the computer | Electronic machine |
| 2 | 1 | WWW stands for | World wide web |
| 3 | 2 | Who invented HTML | Tim Berner Lee |
| 4 | 2 | 1 MB equivalent to | 1024 KB |
INNER JOIN
INNER JOIN returns only those records from both tables where condition matched.
Display all records from both tables (users, posts) where user_id is matched.
SELECT users.id, users.username, posts.title, posts.post FROM users
INNER JOIN posts
ON users.id = posts.user_id;
| id | username | title | post |
| 1 | Aman | What is the computer | Electronic machine |
| 1 | Aman | WWW stands for | World wide web |
| 2 | Kamal | Who invented HTML | Tim Berner Lee |
| 2 | Kamal | 1 MB equivalent to | 1024 KB |
LEFT JOIN
LEFT JOIN returns all the records from the left table and matching records from the right table.
Fetch all users from the users table and related posts from the posts table.
SELECT users.id, users.username, posts.title, posts.post FROM users
LEFT JOIN posts
ON users.id = posts.user_id;
| id | username | title | post |
| 1 | Aman | What is the computer | Electronic machine |
| 1 | Aman | WWW stands for | World wide web |
| 2 | Kamal | Who invented HTML | Tim berner lee |
| 2 | Kamal | 1 MB equivalent to | 1024 KB |
| 3 | Om | NULL | NULL |
RIGHT JOIN
RIGHT JOIN return all the records from the right table and match records from the left table.
Fetch all posts from the posts table and related users from the users table.
SELECT posts.id, posts.title, posts.post, users.username FROM users
RIGHT JOIN posts
ON posts.user_id = users.id;
| id | title | post | username |
| 1 | What is the computer | Electronic machine | Aman |
| 2 | WWW stands for | World wide web | Aman |
| 3 | Who invented HTML | Tim berner lee | Kamal |
| 4 | 1 MB equivalent to | 1024 KB | Kamal |