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    | 
+------+--------+---------------+------+----------+

COALESCE

In most of DB engine you can find COALESCE function. It is helpful where you need to work with NULL values.

source table

mysql> select * from INFO;
+----+-------------+--------------+
| ID | GALLERY     | PHOTOGRAPHER |
+----+-------------+--------------+
|  1 | Cars        | Tom          | 
|  2 | Countryside | Petr         | 
|  3 | City        | Jan          | 
|  4 | Sport       | NULL         | 
+----+-------------+--------------+

When you need to return not NULL report, you can use COALESCE

COALESCE function

mysql> select GALLERY, coalesce(PHOTOGRAPHER,"unknown") as PHOTOGRAPHER from coalesce;
+-------------+--------------+
| GALLERY     | PHOTOGRAPHER |
+-------------+--------------+
| Cars        | Tom          | 
| Countryside | Petr         | 
| City        | Jan          | 
| Sport       | unknown      | 
+-------------+--------------+

Mysql - join multiple rows

This week I need fill one table from data from another tables. It was necessary join multiple columns to one column and join multiple rows to one row too.

I use function CONCAT and GROUP_CONCAT in Mysql

I create table RESULT_TABLE for result data. The table contains any data. The table look like:

mysql> select * from RESULT_TABLE;
+---------+------------+-------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+-------------+------------+
|                  meanwhile EMPTY                |
+---------+------------+-------------+------------+

In DB I have following table:

mysql> select * from BOOK;
+---------+------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_SCOPE |
+---------+------------+------------+
|       1 | title 1    | law        |
|       2 | title 2    | law        |
|       3 | title 3    | math       |
+---------+------------+------------+

mysql> select * from AUTHOR;
+-----------+------------------+-----------------+
| AUTHOR_ID | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME |
+-----------+------------------+-----------------+
|         1 | first1           | last1           |
|         2 | first2           | last2           |
|         3 | first3           | last3           |
|         4 | first4           | last4           |
|         5 | first5           | last5           |
+-----------+------------------+-----------------+

mysql> select * from BOOK_X_AUTHOR;
+---------+-----------+
| BOOK_ID | AUTHOR_ID |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         4 |
|       3 |         5 |
+---------+-----------+

---

In first step I join tables

mysql> select BOOK.BOOK_ID, BOOK.BOOK_TITLE, AUTHOR.AUTHOR_FIRSTNAME, AUTHOR.AUTHOR_LASTNAME, BOOK.BOOK_SCOPE from BOOK, AUTHOR, BOOK_X_AUTHOR
    -> where
    -> BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID and
    -> BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+------------------+-----------------+------------+
| BOOK_ID | BOOK_TITLE | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME | BOOK_SCOPE |
+---------+------------+------------------+-----------------+------------+
|       1 | title 1    | first1           | last1           | law        | 
|       1 | title 1    | first2           | last2           | law        | 
|       2 | title 2    | first3           | last3           | law        | 
|       2 | title 2    | first4           | last4           | law        | 
|       3 | title 3    | first5           | last5           | math       | 
+---------+------------+------------------+-----------------+------------+

---

In second step I use function concat and alias. Function concat return more columns as one column. With alias we can return another column name (BOOK_AUTHOR).

mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+--------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR  | BOOK_SCOPE |
+---------+------------+--------------+------------+
|       1 | title 1    | first1 last1 | law        | 
|       1 | title 1    | first2 last2 | law        | 
|       2 | title 2    | first3 last3 | law        | 
|       2 | title 2    | first4 last4 | law        | 
|       3 | title 3    | first5 last5 | math       | 
+---------+------------+--------------+------------+

---

In third step I use function GROUP_CONCAT. You notice the clause GROUP BY at the end of select. I join multiple rows from table AUTHOR to one colum.

mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat(concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> group by BOOK_ID;
+---------+------------+---------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR               | BOOK_SCOPE |
+---------+------------+---------------------------+------------+
|       1 | title 1    | first2 last2,first1 last1 | law        | 
|       2 | title 2    | first4 last4,first3 last3 | law        | 
|       3 | title 3    | first5 last5              | math       | 
+---------+------------+---------------------------+------------+

---

I add SEPARATOR and clause ORDER BY to function GROUP_CONCAT

mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
    -> ORDER BY AUTHOR.AUTHOR_ID
    -> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> GROUP BY BOOK_ID;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR                | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
|       1 | title 1    | first1 last1, first2 last2 | law        | 
|       2 | title 2    | first3 last3, first4 last4 | law        | 
|       3 | title 3    | first5 last5               | math       | 
+---------+------------+----------------------------+------------+

---

At the end I inserting result to RESULT_TABLE

mysql> insert into RESULT_TABLE (BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, BOOK_SCOPE)
    -> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
    -> ORDER BY AUTHOR.AUTHOR_ID
    -> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> GROUP BY BOOK_ID;

mysql> select *  from RESULT_TABLE;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR                | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
|       1 | title 1    | first1 last1, first2 last2 | law        | 
|       2 | title 2    | first3 last3, first4 last4 | law        | 
|       3 | title 3    | first5 last5               | math       | 
+---------+------------+----------------------------+------------+