MySQL Joins with Examples

Sample Records for explain the Joins :


Inner Join or Equi-join

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. 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.



Example of an explicit inner join:
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

Example of an implicit inner join:

2. SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID

Types of inner joins

i) Natural join

A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.



Ex:
SELECT *
FROM employee NATURAL JOIN department


ii) Cross join

A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.

If A and B are two sets, then the cross join is written as A × B.



Example of an explicit cross join:

SELECT *
FROM employee CROSS JOIN department

Example of an implicit cross join:
SELECT *
FROM employee, department;


Outer Join

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

i) Left outer join

The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).



Syntax for LEFT JOIN
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Ex: SELECT * FROM table1
LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;


Ex:
SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

ii) Right outer join

A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every record 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 A.

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).



Syntax for RIGHT JOIN

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Ex: SELECT * FROM table1
RIGHT JOIN table2 ON table1.id=table2.id
RIGHT JOIN table3 ON table2.id=table3.id;

Ex:

SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID


iii) Full outer join

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.



Ex:
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID


Self Join

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQL query.
To write the query, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Ex:

Which customers are located in the same state (column name is Region)? Type this statement in the SQL window:

SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region
FROM Customers AS c1, Customers AS c2
WHERE c1.Region = c2.Region
AND c1.ContactName <> c2.ContactName
ORDER BY c1.Region, c1.ContactName;

Reference Sites
1. Wikipedia.org
2. cristhianny.com
3. W3Shoool.com