Organizational Research By

Surprising Reserch Topic

INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?


What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

asked Jun 9, 2015 in MYSQL by rajesh
0 votes
36 views



Related Hot Questions

2 Answers

0 votes

NNER JOIN gets all records from one table that have some related entry in a second table

LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL

RIGHT JOIN is like the above but gets all records in the RIGHT table

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table

answered Jun 9, 2015 by rajesh
0 votes
There are different types of joins available in SQL:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN: returns rows when there is a match in one of the tables.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

WE can take each first four joins in Details :

We have two tables with the following values.

TableA
id  firstName                  lastName
.......................................
1   arun                        prasanth                 
2   ann                         antony                   
3   sruthy                      abc                      
6   new                         abc                                           
TableB
id2 age Place
................
1   24  kerala
2   24  usa
3   25  ekm
5   24  chennai
....................................................................

INNER JOIN

Note :it gives the intersection of the two tables, i.e. rows they have common in TableA and TableB

syntax is :

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;
Result Will Be

firstName       lastName       age  Place
..............................................
arun            prasanth        24  kerala
ann             antony          24  usa
sruthy          abc             25  ekm
LEFT JOIN

Note : will give all selected rows in TableA, plus any common selected rows in TableB.

Syantax

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;
Result

firstName                   lastName                    age   Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL
RIGHT JOIN

Note : will give all selected rows in TableB, plus any common selected rows in TableA.

Syantax

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;
Result

firstName                   lastName                    age     Place
...............................................................................
arun                        prasanth                    24     kerala
ann                         antony                      24     usa
sruthy                      abc                         25     ekm
NULL                        NULL                        24     chennai
FULL JOIN

Note : It is same as union operation, it will return all selected values from both tables.

Syantax

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;
Result

firstName                   lastName                    age    Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL
NULL                        NULL                        24    chennai
Interesting Fact
For INNER joins the order doesn't matter

For (LEFT, RIGHT or FULL) OUTER joins,the order matter
answered Jun 9, 2015 by rajesh

...