What is LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN in mysql?

The best way to learn about JOINS is the example. So below is the example to demonstrate JOINS
Suppose that there are two tables
1- Person
2-Information
Person table has definition and data like that
Person:
Number(Primary key) name phone
1 Mr Sana 0120 234564
2 Mr Urooj 0120 234567
3 Mr Imran 0120 245678

Information:
border=”1″ width=”400″ cellspacing=”0″ cellpadding=”10″>

Entry_id Number Address
1 1 ChandPura, Bahraich UP (INDIA)
2 3 Dadi hat, Bahraich UP (INDIA)
3 3 Qazi pura, Bahraich UP (INDIA)
4 3 New Ashok Nagar, Delhi UP (INDIA)
5 4 Faizadab UP (INDIA)

mysql> select * from person;

out put:
Number(Primary key) name phone
1 Mr Sana 0120 234564
2 Mr Urooj 0120 234567
3 Mr Imran 0120 245678

3 rows in set (0.00 sec)

mysql> select * from information;
Entry_id Number Address
1 1 Chand Pura, Bahraich UP (INDIA)
2 3 Dadi hat, Bahraich UP (INDIA)
3 3 Qazi pura, Bahraich UP (INDIA)
4 3 New Ashok Nagar, Delhi UP (INDIA)
5 4 Faizadab UP (INDIA)

5 rows in set (0.00 sec)

Regular Join:

If apply a regular JOIN (with out use of these keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:

mysql> select name, phone, address

from person join information

on person.number = information.number;

OR

mysql> select name, phone, address

from person,information where

person.number = information.number;

Output:
name Phone Address
Mr Sana 0120 234564 Chand Pura, Bahraich UP (INDIA)
Mr Imran 0120 245678 Dadi hat, Bahraich UP (INDIA)
Mr Imran 0120 245678 Qazi pura, Bahraich UP (INDIA)
Mr Imran 0120 245678 New Ashok Nagar, Delhi UP (INDIA)

4 rows in set (0.01 sec)

LEFT JOIN:
If I apply left joint with using keyword LEFT JOIN

mysql> select name, phone, address
from person left join information on person.number = information.number;

Output:
name phone address
Mr Sana 0120 234564 Chand Pura, Bahraich UP (INDIA)
Mr Urooj 0120 234567 NULL
Mr Imran 0120 245678 Dadi hat, Bahraich UP (INDIA)
Mr Imran 0120 245678 Qazi pura, Bahraich UP (INDIA)
Mr Imran 0120 245678 New Ashok Nagar, Delhi UP

5 rows in set (0.00 sec)

RIGHT JOIN:
If I apply right joint with using keyword RIGHT JOIN

mysql> select name, phone, address
from person right join information on person.number = information.number;
name phone address
Mr Sana 0120 234564 Chand Pura, Bahraich UP (INDIA)
Mr Imran 0120 245678 Dadi hat, Bahraich UP (INDIA)
Mr Imran 0120 245678 Qazi pura, Bahraich UP (INDIA)
Mr Imran 0120 245678 New Ashok Nagar, Delhi UP
NULL NULL Faizadab UP (INDIA)

5 rows in set (0.00 sec)

OUTER JOIN: Outer join does not support RDBMS.

Source: http://www.phpinterviewquestion.com/

Advertisements

About amitsonikhandwa
I am a web developer working in PHP and MYSQL with AJAX, jQuery and JavaScript. I am in web development since 2007.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s