PHP-MYSQL JOIN (INNER,LEFT,RIGHT) discussion

Hi friends, in this tutorial we are going to talk about MySQL JOINS, which is very very useful in the application development. often it would be trouble for developer for applying it in applications. let us begin with small example so that we could understand how it is flowing in the applications.

First we need to create 2 tables in your db as given in below,

Table 1 : posts

create fields for posts ->

pid (auto increment, primary),
posts(varchar).

Table 2 : Comments

create fields for comments ->

cid (auto increment, primary),
pid(int),
comments(varchar).

And then insert some dummy data into those tables, like below

for posts,

for comments,

That is it. From here we need to start our MySQL join queries,

Inner Join:

eg. 1=>

SELECT * FROM posts INNER JOIN comments ON posts.pid=comments.pid;

the output would be,

eg. 2=>

SELECT * FROM posts.posts, comments.comments FROM posts INNER JOIN comments ON posts.pid=comments.pid;

the output would be,

LEFT JOIN:

go through following example, you will be clear.

we can use same query used for last eg.

explain: posts table  joins left comments table by using foreign key pid. comments(left) table does not have any data with relevant id which put null for that.
eg. 1=>

SELECT * FROM posts.posts, comments.comments FROM posts LEFT JOIN comments ON posts.pid=comments.pid;

the output would be,

RIGHT JOIN:

for right join,

explain: posts table  joins right comments table by using foreign key pid. posts(right) table does not have any data with relevant id which put null for that.

1=>
SELECT * FROM posts.posts, comments.comments FROM posts RIGHT JOIN comments ON posts.pid=comments.pid;

the output would be,

I believe that you will be cleared after practicing this example.

Posts your comments. Have a good day.