❮ Mysql Create Database Mysql Alter ❯

Usage of MySQL Joins

In the previous chapters, we have learned how to retrieve data from a single table, which is relatively straightforward. However, in real-world applications, it is often necessary to retrieve data from multiple tables.

In this chapter, we will introduce how to use MySQL's JOIN to query data from two or more tables.

You can use MySQL's JOIN in SELECT, UPDATE, and DELETE statements to combine multiple tables.

JOIN is broadly categorized into the following three types:

INNER JOIN (Inner Join, or Equi Join): Retrieves records that have matching values in both tables.
LEFT JOIN (Left Join): Retrieves all records from the left table, and the matched records from the right table.
RIGHT JOIN (Right Join): Retrieves all records from the right table, and the matched records from the left table.

Database structure and data used in this chapter can be downloaded from: tutorialpro-mysql-join-test.sql.

Using INNER JOIN in Command Prompt

We have two tables, tcount_tbl and tutorialpro_tbl, in the tutorialpro database. The data in these tables is as follows:

Example

Try the following example:

Test Example Data

mysql> use tutorialpro;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| tutorialpro_author | tutorialpro_count |
+---------------+--------------+
| tutorialpro.org  | 10           |
| tutorialpro.org    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)

mysql> SELECT * from tutorialpro_tbl;
+-----------+---------------+---------------+-----------------+
| tutorialpro_id | tutorialpro_title  | tutorialpro_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | Learn PHP    | tutorialpro.org  | 2017-04-12      |
| 2         | Learn MySQL  | tutorialpro.org  | 2017-04-12      |
| 3         | Learn Java   | tutorialpro.org    | 2015-05-01      |
| 4         | Learn Python | tutorialpro.org    | 2016-03-06      |
| 5         | Learn C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

Next, we will use MySQL's INNER JOIN (or simply JOIN, which works the same way) to retrieve the tutorialpro_count field from tcount_tbl corresponding to the tutorialpro_author field in tutorialpro_tbl:

INNER JOIN

mysql> SELECT a.tutorialpro_id, a.tutorialpro_author, b.tutorialpro_count FROM tutorialpro_tbl a INNER JOIN tcount_tbl b ON a.tutorialpro_author = b.tutorialpro_author;
+-------------+-----------------+----------------+
| a.tutorialpro_id | a.tutorialpro_author | b.tutorialpro_count |
+-------------+-----------------+----------------+
| 1           | tutorialpro.org    | 10             |
| 2           | tutorialpro.org    | 10             |
| 3           | tutorialpro.org      | 20             |
| 4           | tutorialpro.org      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

The above SQL statement is equivalent to:

WHERE Clause

mysql> SELECT a.tutorialpro_id, a.tutorialpro_author, b.tutorialpro_count FROM tutorialpro_tbl a, tcount_tbl b WHERE a.tutorialpro_author = b.tutorialpro_author;
+-------------+-----------------+----------------+
| a.tutorialpro_id | a.tutorialpro_author | b.tutorialpro_count |
+-------------+-----------------+----------------+
| 1           | tutorialpro.org    | 10             |
| 2           | tutorialpro.org    | 10             |
| 3           | tutorialpro.org      | 20             |
| 4           | tutorialpro.org      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.01 sec)

MySQL LEFT JOIN

MySQL LEFT JOIN is different from JOIN. MySQL LEFT JOIN retrieves all records from the left table, even if there are no matching records in the right table.

Example

Try the following example, using tutorialprotbl as the left table and tcounttbl as the right table, to understand the application of MySQL LEFT JOIN:

LEFT JOIN

mysql> SELECT a.tutorialpro_id, a.tutorialpro_author, b.tutorialpro_count FROM tutorialpro_tbl a LEFT JOIN tcount_tbl b ON a.tutorialpro_author = b.tutorialpro_author;
+-------------+-----------------+----------------+
| a.tutorialpro_id | a.tutorialpro_author | b.tutorialpro_count |
+-------------+-----------------+----------------+
| 1           | tutorialpro.org    | 10             |
| 2           | tutorialpro.org    | 10             |
| 3           | tutorialpro.org      | 20             |
| 4           | tutorialpro.org      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

In the above example, LEFT JOIN is used to retrieve all selected fields from the left table tutorialpro_tbl, even if there are no corresponding tutorialpro_author field values in the right table tcount_tbl.

MySQL RIGHT JOIN

MySQL RIGHT JOIN retrieves all records from the right table, even if there are no matching records in the left table.

Example

Try the following example, using tutorialprotbl as the left table and tcounttbl as the right table, to understand the application of MySQL RIGHT JOIN:

RIGHT JOIN

mysql> SELECT a.tutorialpro_id, a.tutorialpro_author, b.tutorialpro_count FROM tutorialpro_tbl a RIGHT JOIN tcount_tbl b ON a.tutorialpro_author = b.tutorialpro_author;
+-------------+-----------------+----------------+
| a.tutorialpro_id | a.tutorialpro_author | b.tutorialpro_count |
+-------------+-----------------+----------------+
| 1           | tutorialpro.org    | 10             |
| 2           | tutorialpro.org    | 10             |
| 3           | tutorialpro.org      | 20             |
| 4           | tutorialpro.org      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

In the above example, RIGHT JOIN is used to retrieve all selected fields from the right table tcount_tbl, even if there are no corresponding tutorialpro_author field values in the left table tutorialpro_tbl.

Using JOIN in PHP Scripts

In PHP, you can use the mysqli_query() function to execute SQL statements. You can use the same SQL statements mentioned above as parameters for the mysqli_query() function. Try the following example:

MySQL ORDER BY Test:

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

$sql = 'SELECT a.tutorialpro_id, a.tutorialpro_author, b.tutorialpro_count FROM tutorialpro_tbl a INNER JOIN tcount_tbl b ON a.tutorialpro_author = b.tutorialpro_author';

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to fetch data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org MySQL JOIN Test<h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Author</td><td>Login Count</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td> {$row['tutorialpro_id']}</td> ".
         "<td>{$row['tutorialpro_author']} </td> ".
         "<td>{$row['tutorialpro_count']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown in the figure below:

❮ Mysql Create Database Mysql Alter ❯