डिप्लोमा इन ऑफिस मैनेजमेंट एण्ड अकाउटिंग

डिप्लोमा इन ऑफिस मैनेजमेंट एण्ड अकाउटिंग

Full Stack Web Development with Laravel

Full Stack Web Development with Laravel

Affiliate Program

Affiliate Program

MYSQL INNER, LEFT, RIGHT Join

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.

Table: users(Parent table in a join)
id username email password
1 Aman aman@gmail.com 12345
2 Kamal kamal@gmail.com 12345
3 Om Om@gmail.com 12345

 

Table: posts(child table in a join)
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

 

© 2016 - 2023, All Rights are Reserved.