One of the great characteristics of SQL (Structured Query Language) is the fact that one can develop access and modify data across various tables. There are several benefits to this, including greater ease of manipulation, increased speed of access, and reduced data redundancy. In MYSQL (as well as many other SQL languages), this is accomplished via the join command. In short, a join command unites some or all of the data from two or more tables into one comprehensive structure. A JOIN is a means for combining fields from two tables by using values common to each.
There are two tables “Persons” and “Orders”.

The “Persons” table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

 

The “Orders” table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

 

Types of JOIN:

INNER JOIN
An INNER JOIN is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combing column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross Join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a hash join or a sort-merge join where possible, since computing the Cartesian product is very inefficient.

 

SQL INNER JOIN Syntax

 

The result-set will look like this:

INNER JOIN:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

 

 LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (Persons), even if there are no matches in the right table (Orders). In the other words, left join extract all data from left table and matched data from right table having null against unmatched records.

SQL LEFT JOIN Syntax

 

The result-set will look like this:

LEFT JOIN:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove	NULL

 

 RIGHT JOIN

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
SQL RIGHT JOIN Syntax:

 

The result-set will look like this:

RIGHT JOIN:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
NULL	NULL	34764

 

 EQUI JOIN

An equi-join (also known as an equijoin) is a specific type of comparator-based join, or theta join that uses only equality comparisons in the join-predicate. Using other comparison operators (such as SQL EQUI JOIN Syntax:

 

The result-set will look like this:

EQUI JOIN:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

 

 CROSS JOIN

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

SQL CROSS JOIN Syntax:

 

The result-set will look like this:

CROSS JOIN:

LastName	FirstName	OrderNo
Hansen	Ola	34764
Svendson	Tove	77895
Pettersen	Kari	77895
Hansen	Ola	44678
Svendson	Tove	44678
Pettersen	Kari	44678
Hansen	Ola	22456
Svendson	Tove	22456
Pettersen	Kari	22456
Hansen	Ola	24562
Svendson	Tove	24562
Pettersen	Kari	24562
Hansen	Ola	34764
Svendson	Tove	34764
Pettersen	Kari	34764

 

NATURAL JOIN

A NATURAL JOIN is a type of equi-join where the join predicate arises implicitly by columns in both tables that have the same column-names in the jointed tables. The resulting joined table contains only one column for each pair of equally named columns.

SQL NATURAL JOIN Syntax:

The result-set will look like this:

NATURAL JOIN

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

 

 FULL JOIN

Mysql FULL JOIN is used to return all the records from both left and right outer join. The joined tables contain all records from both tables and fill null values for those missing matches on either side. The full join in MYSQL is the outcome result set of left outer join and right outer join using UNION clause.

SQL FULL JOIN Syntax:

 

The result-set will look like this:

FULL JOIN

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove
		34764

 

 SELF JOIN

A SELF JOIN is another type of join in sql which is used to join a table to itself, specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

 

SQL SELF JOIN Syntax:

 

The result-set will look like this:

SELF JOIN

LastName	FirstName	City
Svendson	Tove	Sandnes
Hansen	Ola	Sandnes