The JOIN keyword is used in a MySQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

For example, suppose we need a report that displays employee id, name, job and department name. The first three attributes are present in Emp table where as the last one is in Dept table. To produce the report, we need to link the Emp and Dept tables and access data from both of them. This is called JOIN operation. To gain better understanding of JOIN, it would be helpful to first clarify the concept of Cartesian product.

Cartesian JOIN

In a Cartesian join, also called a Cartesian product or cross join, each record in the first table is matched with each record in the second table.

A Cartesian product is formed under following conditions:

  1. When a join condition is omitted
  2. When a join condition is invalid

Consider the following example:

SELECT * FROM Emp, Dept;

In the above example, if Emp table has 14 rows and Dept table has 4 rows, then their Cartesian product would generate 14 x 4 = 56 rows.

In fact, the ISO standard provides a special format of the SELECT statement for the Cartesian product:

SELECT * FROM Emp CROSS JOIN Dept;

A Cartesian product tends to generate a large number of rows and its result is rarely useful. It is always necessary to include a valid join condition in a WHERE clause. Hence a join is always a subset of a Cartesian product.

Types of JOINS

Following image represents types of joins in SQL:

Types of JOINS in MySQL

 

 

 

 

 

 

 

 

 

1. EQUALITY JOINS:

If the join contains an equality condition, it is also called Equi-Join, Natural Join or Inner Join.

Example:

To retrieve the employee name, their job and department name, we need to extract data from two tables, Emp and Dept. This type of join is called equi-join that is, values in the deptno column on both tables must be equal. Equi-join is also called simple join or inner join. Example of equi-join:

SELECT E.ename, E.job, D.dname FROM Emp E, Dept D WHERE E.deptno = D.deptno;

2. NON-EQUALITY JOIN:

A non-equi-join enables you to join two tables where there is no direct correspondence mns in the tables. A non-equi-join relates two folders using one or more join conditions that use non-equi-join operators.

For example, the schema below uses a non-equi-join to join the Emp and salgrade tables because there are no matching columns in the two tables.                 

SELF JOIN

When a table is joined to itself then it is called as SELF JOIN or in other words we can just say "joining a table to itself is called self-join".

Example: (and notice the use of table alias to provide a meaningful name for the data origin):

SELECT Worker.ename|| ' works for ' || Manager.ename FROM Emp Worker, 

Emp Manager WHERE Worker.mgr = Manager.empno;

OUTER JOINS

Use OUTER JOIN to return records which don't have direct match and OUTER JOIN operator is the plus sign (+). In outer join operation, all records from the source table included in the result even though they don't satisfy the join condition.

Outer joins are classified into three types:

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Following image represents NON_EQUAL joins in SQL:

Non-Equal JOINS in MySQL

Syntax

A basic syntax is shown in below example:

SELECT column_names FROM both_tables FROM table_name1 LEFT|RIGHT|FULL OUTER 

JOIN table_name2 ON condition;

Left OUTER JOIN example:

SELECT E.ename, D.deptno, D.dname FROM Emp E LEFT OUTER JOIN Dept D ON 

(E.deptno = D.deptno);

Right OUTER JOIN example:

SELECT E.ename, D.deptno, D.name FROM Emp E RIGHT OUTER JOIN Dept D ON 

(E.deptno = D.deptno);

Full OUTER JOIN example:

SELECT E.ename, D.deptno, D.dname FROM Emp E FULL OUTER JOIN Dept D ON 

(E.deptno = D.deptno);

See more about individual joins and examples in following chapters.