Detailed tutorial on using JOIN statements in MySQL _ MySQL-mysql tutorial

This article mainly introduces the detailed tutorial on using JOIN statements for connection operations in MySQL. it is the basic knowledge in MySQL getting started. For more information, see, we have learned how to get data from a table. This is a simple requirement, but in most real MySQL applications, you often need to query data from a single query in multiple tables.

You can use a single SQL query in multiple tables. Join in MySQL means that two or more tables can be connected to one table by adding MySQL tables to SELECT, UPDATE, and DELETE statements. We will see an example of left join simple MySQL connection.
Use join at the command prompt:

Assume that the complete list of the tutorial tcount_tbl and tutorials_tbl in the two tables is as follows:
Example:

Try the following example:

root@host# mysql -u root -p password;Enter password:*******mysql> use TUTORIALS;Database changedmysql> SELECT * FROM tcount_tbl;+-----------------+----------------+| tutorial_author | tutorial_count |+-----------------+----------------+| mahran     |       20 || mahnaz     |      NULL || Jen       |      NULL || Gill      |       20 || John Poul    |       1 || Sanjay     |       1 |+-----------------+----------------+6 rows in set (0.01 sec)mysql> SELECT * from tutorials_tbl;+-------------+----------------+-----------------+-----------------+| tutorial_id | tutorial_title | tutorial_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>

Now, we can write an SQL query by adding these two tables. In this query, all authors are selected from the table tutorials_tbl, and the corresponding number of tutorials are picked up from the table tcount_tbl.

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count  -> FROM tutorials_tbl a, tcount_tbl b  -> WHERE a.tutorial_author = b.tutorial_author;+-------------+-----------------+----------------+| tutorial_id | tutorial_author | tutorial_count |+-------------+-----------------+----------------+|      1 | John Poul    |       1 ||      3 | Sanjay     |       1 |+-------------+-----------------+----------------+2 rows in set (0.01 sec)mysql>

Use join in PHP script:

You can use any PHP script for the preceding SQL query. You only need to use the PHP function mysql_query () to execute the SQL query, and then obtain the result using the conventional method.
Example:

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.tutorial_id, a.tutorial_author, b.tutorial_count    FROM tutorials_tbl a, tcount_tbl b    WHERE a.tutorial_author = b.tutorial_author';mysql_select_db('TUTORIALS');$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['tutorial_author']} 
 ".     "Count: {$row['tutorial_count']} 
 ".     "Tutorial ID: {$row['tutorial_id']} 
 ".     "--------------------------------
";} echo "Fetched data successfully\n";mysql_close($conn);?>

Left MySQL connection:

A simple connection is different from a MySQL left connection. A MySQL left join provides additional considerations for the table on the LEFT.

If left join is performed, all the records are matched in the same way. In addition, an additional record is obtained for each unmatched record, join in the left table-to ensure that each author is associated (in this example ):
Instance:

Try the following example to understand left join:

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

More practices are required to familiarize yourself with JOINS. This is a complex concept that will become clearer in MySQL/SQL.