MYSQL - JOIN conjunction

Where you have two and more tables, you can made conjunction between table.

You can use this kind of conjunction:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL JOIN
• CROSS JOIN

---

Source table

mysql> select* from EMPLOYEE;
+----+--------+---------------+
| ID | NAME   | ID_DEPARTMENT |
+----+--------+---------------+
|  1 | Tom    |             1 | 
|  2 | Petr   |             1 | 
|  3 | Jan    |             2 | 
|  4 | Michal |             2 | 
|  5 | Pepa   |             3 | 
|  6 | Zdenek |             0 | 
+----+--------+---------------+

mysql> select* from DEPARTMENT;
+----+----------+
| ID | TITLE    |
+----+----------+
|  1 | economy  | 
|  2 | it       | 
|  3 | research | 
|  4 | sales    | 
+----+----------+

---

INNER JOIN

Return only corresponding records. You use inner join or join or empty clause.

This selects are the same:

select * from EMPLOYEE inner join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

select * from EMPLOYEE join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

select * from EMPLOYEE, DEPARTMENT where EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

+----+--------+---------------+----+----------+
| ID | NAME   | ID_DEPARTMENT | ID | TITLE    |
+----+--------+---------------+----+----------+
|  1 | Tom    |             1 |  1 | economy  | 
|  2 | Petr   |             1 |  1 | economy  | 
|  3 | Jan    |             2 |  2 | it       | 
|  4 | Michal |             2 |  2 | it       | 
|  5 | Pepa   |             3 |  3 | research | 
+----+--------+---------------+----+----------+

---

LEFT OUTER JOIN

Return all records from left table. You can use left outer join or
left join clause only.

mysql> SELECT *                         
    -> FROM EMPLOYEE
    -> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+----+--------+---------------+------+----------+
| ID | NAME   | ID_DEPARTMENT | ID   | TITLE    |
+----+--------+---------------+------+----------+
|  1 | Tom    |             1 |    1 | economy  | 
|  2 | Petr   |             1 |    1 | economy  | 
|  3 | Jan    |             2 |    2 | it       | 
|  4 | Michal |             2 |    2 | it       | 
|  5 | Pepa   |             3 |    3 | research | 
|  6 | Zdenek |             0 | NULL | NULL     | 
+----+--------+---------------+------+----------+

---

RIGHT OUTER JOIN

Return all records from right table. You can use right outer join or
right join clause only.

mysql> SELECT *
    -> FROM EMPLOYEE
    -> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+----+----------+
| ID   | NAME   | ID_DEPARTMENT | ID | TITLE    |
+------+--------+---------------+----+----------+
|    1 | Tom    |             1 |  1 | economy  | 
|    2 | Petr   |             1 |  1 | economy  | 
|    3 | Jan    |             2 |  2 | it       | 
|    4 | Michal |             2 |  2 | it       | 
|    5 | Pepa   |             3 |  3 | research | 
| NULL | NULL   |          NULL |  4 | sales    | 
+------+--------+---------------+----+----------+

CROSS JOIN

Create cartesian product, You can use cross join or empty.

This selects are the same:

SELECT * FROM EMPLOYEE CROSS JOIN DEPARTMENT

SELECT * FROM EMPLOYEE, DEPARTMENT

mysql> SELECT *
    -> FROM EMPLOYEE
    -> CROSS JOIN DEPARTMENT;
+----+--------+---------------+----+----------+
| ID | NAME   | ID_DEPARTMENT | ID | TITLE    |
+----+--------+---------------+----+----------+
|  1 | Tom    |             1 |  1 | economy  | 
|  1 | Tom    |             1 |  2 | it       | 
|  1 | Tom    |             1 |  3 | research | 
|  1 | Tom    |             1 |  4 | sales    | 
|  2 | Petr   |             1 |  1 | economy  | 
|  2 | Petr   |             1 |  2 | it       | 
|  2 | Petr   |             1 |  3 | research | 
|  2 | Petr   |             1 |  4 | sales    | 
|  3 | Jan    |             2 |  1 | economy  | 
|  3 | Jan    |             2 |  2 | it       | 
|  3 | Jan    |             2 |  3 | research | 
|  3 | Jan    |             2 |  4 | sales    | 
|  4 | Michal |             2 |  1 | economy  | 
|  4 | Michal |             2 |  2 | it       | 
|  4 | Michal |             2 |  3 | research | 
|  4 | Michal |             2 |  4 | sales    | 
|  5 | Pepa   |             3 |  1 | economy  | 
|  5 | Pepa   |             3 |  2 | it       | 
|  5 | Pepa   |             3 |  3 | research | 
|  5 | Pepa   |             3 |  4 | sales    | 
|  6 | Zdenek |             0 |  1 | economy  | 
|  6 | Zdenek |             0 |  2 | it       | 
|  6 | Zdenek |             0 |  3 | research | 
|  6 | Zdenek |             0 |  4 | sales    | 
+----+--------+---------------+----+----------+

---

FULL JOIN

You need to use two JOINs and UNION. More example in article How to simulate FULL OUTER JOIN in MySQL

mysql> SELECT *
    -> FROM EMPLOYEE
    -> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID
    -> UNION
    -> SELECT *
    -> FROM EMPLOYEE
    -> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+------+----------+
| ID   | NAME   | ID_DEPARTMENT | ID   | TITLE    |
+------+--------+---------------+------+----------+
|    1 | Tom    |             1 |    1 | economy  | 
|    2 | Petr   |             1 |    1 | economy  | 
|    3 | Jan    |             2 |    2 | it       | 
|    4 | Michal |             2 |    2 | it       | 
|    5 | Pepa   |             3 |    3 | research | 
|    6 | Zdenek |             0 | NULL | NULL     | 
| NULL | NULL   |          NULL |    4 | sales    | 
+------+--------+---------------+------+----------+