Detailed explanation of the use of Join in Mysql

Detailed explanation of the use of Join in Mysql

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

In this section, we will introduce how to use MySQL JOIN to query data in two or more tables.

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

Below we will demonstrate the difference between the use of MySQL LEFT JOIN and JOIN.

Using JOIN in Command Prompt

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

Examples

Try the following examples:

root@host#mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> 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 use MySQL JOIN to connect the above two tables to read the runoob_count field values ​​corresponding to all runoob_author fields in the runoob_tbl table in the tcount_tbl table:

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>

Using JOIN in PHP scripts

PHP uses the mysql_query() function 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 will read all the data in the left table, even if there is no corresponding data in the right table.

Examples

Try the following examples to understand the application of MySQL LEFT JOIN:

root@host#mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> 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)

In the above example, LEFT JOIN is used. This statement will read all selected field data from the left data table runoob_tbl, even if there is no corresponding runoob_author field value in the right table tcount_tbl.

The above is a detailed explanation of the use of Join in MySQL introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:
  • MySQL joint table query basic operation left-join common pitfalls
  • Summary of various common join table query examples in MySQL
  • MySQL 8.0.18 stable version released! Hash Join is here as expected
  • Join operation in Mysql
  • In-depth understanding of MySQL self-connection and join association
  • Mysql inner join on usage examples (must read)
  • Summary of seven MySQL JOIN types

<<:  VUE implements bottom suction button

>>:  How to configure Java environment variables in Linux system

Recommend

Two simple menu navigation bar examples

Menu bar example 1: Copy code The code is as foll...

Steps to install RocketMQ instance on Linux

1. Install JDK 1.1 Check whether the current virt...

Detailed example of remotely connecting to Docker using TLS encrypted communication

By default, Docker runs over a non-networked UNIX...

Implementation steps of encapsulating components based on React

Table of contents Preface How does antd encapsula...

Vue implements adding watermark to uploaded pictures

This article shares the specific implementation c...

JavaScript to implement a simple shopping form

This article shares the specific code of JavaScri...

WeChat applet silent login and maintenance of custom login state detailed explanation

Table of contents 1. Background 2. What is silent...

Tomcat class loader implementation method and example code

Tomcat defines multiple ClassLoaders internally s...

Briefly explain the use of group by in sql statements

1. Overview Group by means to group data accordin...

How to use the Linux basename command

01. Command Overview basename - strip directories...

Screen command and usage in Linux

Screen Introduction Screen is a free software dev...

Example analysis of the page splitting principle of MySQL clustered index

This article uses an example to illustrate the pa...

Linux deb package decompression, modification and other operation methods code examples

For various reasons, sometimes you need to modify...

How to remotely connect to MySQL database with Navicat Premium

The party that creates a new connection is equiva...

jQuery implements breathing carousel

This article shares the specific code of jQuery t...