Join in MySQL

Join clause is used to combine rows from two or more tables depending upon common field between them.

There are different kinds of Joins.

The most common and useful Join is Inner Join.

Inner Join:
It will return all rows from multiple tables where the condition is met.



Syntax:

SELECT column names
FROM tblname1 
JOIN tblname2
ON tblname1.column_name=tblname2.column_name 
WHERE some_condition

For example:

Let's consider two tables below:
1)tbl_employees

eid	name	address	email	mobile
1	Ryan	Ktm	ryan@gmail.com	9874512663
2	Sunaina	Lagankhel	s@gmail.com	98745256325
3	Preeti	Budhanagar	p@gmail.com	987412321

2) tbl_salaries

sid	eid	salary
1	1	20000
2	2	10000
3	3	15000

Here, we have two tables tbl_employees and tbl_salaries and eid is common on both table.
Now if we want to find the name of employee who earns 15000 income then we have to combine two tables using JOIN as below:

SELECT tbl_employees.name
FROM tbl_employees 
JOIN tbl_salaries
ON tbl_employees.eid=tbl_salaries.eid 
WHERE tbl_salaries.eid='15000' 

OR

If you want the set of result only without using condition then use SQL as below:

SELECT tbl_employees.name 
FROM tbl_employees 
JOIN tbl_salaries
ON tbl_employees.eid=tbl_salaries.eid



LEFT JOIN:
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. It is also called LEFT OUTER JOIN.

Syntax:
SELECT column_name(s)
FROM tblname1
LEFT JOIN tblname2
ON tblname1.column_name=tblname2.column_name   

 For example:

Lets consider two tables as shown up:

SELECT tbl_employees.name, tbl_employees.email 
FROM tbl_employees
LEFT JOIN tbl_salaries
ON tbl_employees.eid=tbl_salaries.eid


RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Syntax
SELECT column_name(s)
FROM tblname1
RIGHT JOIN tblname2
ON tblname1.column_name=tblname2.column_name

For example:
 Using RIGHT JOIN or RIGHT OUTER JOIN on the table shown up, SQL will be as below:

SELECT tbl_employees.name, tbl_employees.email 
FROM tbl_employees
RIGHT JOIN tbl_salaries
ON tbl_employees.eid=tbl_salaries.eid


FULL OUTER JOIN
Full Outer Join is not supported in MySQL. But we can get the result by using UNION keyword.

Syntax:
SELECT column_name(s)
FROM tblname1
LEFT JOIN tblname2
ON tblname1.column_name=tblname2.column_name
UNION
SELECT column_name(s)
FROM tblname1
RIGHT JOIN tblname2
ON tblname1.column_name=tblname2.column_name

For example:

SELECT tbl_employees.name, tbl_employees.email 
FROM tbl_employees
LEFT JOIN tbl_salaries
ON tbl_employees.eid=tbl_salaries.eid
UNION
SELECT tbl_employees.name, tbl_employees.email 
FROM tbl_employees
RIGHT JOIN tbl_salaries
ON tbl_employees.eid=tbl_salaries.eid