Article directory
How to use inner join with multiple tables in MySQL database ? What is the efficiency of left join?
Mysql use of connection
In the previous chapters, we have learned how to read data from one table, which is relatively simple, but in real applications it is often necessary to read data from multiple data tables.
In this chapter we will show you how to use MySQL's JOIN to query data in two or more tables.
You can use Mysql's JOIN in SELECT, UPDATE and DELETE statements to join multi-table queries.
JOIN is roughly divided into the following three categories according to its function:
- INNER JOIN (inner join, or equijoin): Get records with matching fields in two tables.
- LEFT JOIN (left join):Get all records in the left table, even if there are no matching records in the right table.
- RIGHT JOIN (right join): Contrary to LEFT JOIN, it is used to get all records in the right table, even if there are no corresponding matching records in the left table.
Using INNER JOIN in Command Prompt
We have two tables tcount_tbl and chenweiliang_tbl in the chenweiliang database.The data of the two data tables are as follows:
Examples
Try the following examples:
test instance data
mysql> use chenweiliang; Database changed mysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | chenweiliang_author | chenweiliang_count | +---------------+--------------+ | 陈沩亮博客 | 10 | | chenweiliang.com | 20 | | Google | 22 | +---------------+--------------+ 3 rows in set (0.01 sec) mysql> SELECT * from chenweiliang_tbl; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+ 5 rows in set (0.01 sec)
Next, we will use MySQL's INNER JOIN (you can omit INNER and use JOIN, the effect is the same) to join the two tables above and read the values of the chenweiliang_author field in the chenweiliang_tbl table and their corresponding chenweiliang_count field values in the tcount_tbl table:
INNER JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec)
The above SQL statement is equivalent to:
WHERE clause
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a, tcount_tbl b WHERE a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.01 sec)
MySQL LEFT JOIN
MySQL left join is different from join. MySQL LEFT JOIN will read all the data in the left data table, even if the right table has no corresponding data.
Examples
Try the following example, using chenweiliang_tbl as the left table and tcount_tbl as the right table, to understand the application of MySQL LEFT JOIN:
LEFT JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a LEFT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
In the above example, LEFT JOIN is used. This statement will read all the selected field data of the data table chenweiliang_tbl on the left, even if there is no corresponding field value of chenweiliang_author in the table tcount_tbl on the right.
MySQL RIGHT JOIN
MySQL RIGHT JOIN will read all the data in the data table on the right, even if there is no corresponding data in the table on the left.
Examples
Try the following example, using chenweiliang_tbl as the left table and tcount_tbl as the right table, to understand the application of MySQL RIGHT JOIN:
RIGHT JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a RIGHT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
RIGHT JOIN is used in the above example, this statement will read all the selected field data of the data table tcount_tbl on the right, even if there is no corresponding field value of chenweiliang_author in the table chenweiliang_tbl on the left.
Using JOIN in PHP script
The mysqli_query() function is used in PHP to execute SQL statements, you can use the same SQL statement as above as a parameter to the mysqli_query() function.
Try the following example:
MySQL ORDER BY test:
<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author';
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL JOIN 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>作者</td><td>登陆次数</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr><td> {$row['chenweiliang_id']}</td> ".
"<td>{$row['chenweiliang_author']} </td> ".
"<td>{$row['chenweiliang_count']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>Hopefully, the article "Using MySQL Database inner join across multiple tables? Efficiency of left join" shared on Chen Weiliang's blog ( https://www.chenweiliang.com/ ) will be helpful to you.
Feel free to share this article's link: https://www.chenweiliang.com/cwl-488.html