Detailed description of Join instances in Mysql, and detailed description of mysqljoin instances

Detailed description of Join instances in Mysql, and detailed description of mysqljoin instances

In the previous chapters, we have learned that reading data from a table is relatively simple, but in real applications, we often need to read data from multiple data tables.

This section describes how to use MySQL JOIN to query data in two or more tables.

You can use Mysql join statements in SELECT, UPDATE, and DELETE statements to join multiple tables.

The following describes the differences between MySQL left join and JOIN.

Use JOIN in a command prompt

We have two tables in the RUNOOB Database: tcount_tbl and runoob_tbl. The two data tables are as follows:

Instance

Try the following instances:

root@host# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> SELECT * FROM tcount_tbl;+-----------------+----------------+| runoob_author | runoob_count |+-----------------+----------------+| mahran   |    20 || mahnaz   |   NULL || Jen    |   NULL || Gill   |    20 || John Poul  |    1 || Sanjay   |    1 |+-----------------+----------------+6 rows in set (0.01 sec)mysql> SELECT * from runoob_tbl;+-------------+----------------+-----------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-------------+----------------+-----------------+-----------------+|   1 | Learn PHP  | John Poul  | 2007-05-24  ||   2 | Learn MySQL | Abdul S   | 2007-05-24  ||   3 | JAVA Tutorial | Sanjay   | 2007-05-06  |+-------------+----------------+-----------------+-----------------+3 rows in set (0.00 sec)mysql>

Next, we will use MySQL JOIN to connect the above two tables to read the runoob_author field values of all runoob_author fields in the runoob_tbl table in runoob_count:

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count -> FROM runoob_tbl a, tcount_tbl b -> WHERE a.runoob_author = b.runoob_author;+-------------+-----------------+----------------+| runoob_id | runoob_author | runoob_count |+-------------+-----------------+----------------+|   1 | John Poul  |    1 ||   3 | Sanjay   |    1 |+-------------+-----------------+----------------+2 rows in set (0.01 sec)mysql>

Use JOIN in a PHP script

Use the mysql_query () function in PHP to execute SQL statements. You can use the same SQL statement as the parameter of the mysql_query () function.

Try the following example:

<?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){ die('Could not connect: ' . mysql_error());}$sql = 'SELECT a.runoob_id, a.runoob_author, b.runoob_count  FROM runoob_tbl a, tcount_tbl b  WHERE a.runoob_author = b.runoob_author';mysql_select_db('RUNOOB');$retval = mysql_query( $sql, $conn );if(! $retval ){ die('Could not get data: ' . mysql_error());}while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){ echo "Author:{$row['runoob_author']} <br> ".   "Count: {$row['runoob_count']} <br> ".   "Tutorial ID: {$row['runoob_id']} <br> ".   "--------------------------------<br>";} echo "Fetched data successfully\n";mysql_close($conn);?>

MySQL LEFT JOIN

MySQL left join is different from join. MySQL left join reads all data from the LEFT table even if no corresponding data exists in the right table.

Instance

Try the following example to understand the application of MySQL left join:

root@host# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count -> FROM runoob_tbl a LEFT JOIN tcount_tbl b -> ON a.runoob_author = b.runoob_author;+-------------+-----------------+----------------+| runoob_id | runoob_author | runoob_count |+-------------+-----------------+----------------+|   1 | John Poul  |    1 ||   2 | Abdul S   |   NULL ||   3 | Sanjay   |    1 |+-------------+-----------------+----------------+3 rows in set (0.02 sec)

If left join is used in the preceding example, The statement reads all the selected field data from the LEFT-side data table runoob_tbl, even if no corresponding runoob_author field value exists in the right-side table tcount_tbl.

The above is a detailed description of Join instances in Mysql. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!