In the previous chapters, we were getting data from one table at a time. This is good enough for simple takes, but in most of the real world MySQL usages, you will often need to get data from multiple tables in a single query.
You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.
You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN.
Using Joins at the Command Prompt
Assume we have two tables tcount_tbl and tutorials_tbl, in TUTORIALS. Now take a look at the examples given below −
Example
The following examples −
root@host# mysql -u root -p password;
Enter password:
mysql> use mydb;
Database changed
mysql> select * from clients;
+----+------------+-----------+---------------+------------------------------+------------+
| id | first_name | last_name | mobile_phone | email_address | agent_name |
+----+------------+-----------+---------------+------------------------------+------------+
| 1 | Sergienko | Anne | (123)555-0102 | mariya@northwindtraders.com | Nancy |
| 2 | Thorpe | Steven | 7788665544 | steven@northwindtraders.com | Cencini |
| 3 | Neipper | Michael | (123)555-0105 | michael@northwindtraders.com | Mariya |
+----+------------+-----------+---------------+------------------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from employees;
+----+-------------------+------------+-----------+-----------------------+---------------+-----------------------------+
| id | company | first_name | last_name | job_title | mobile_phone | email_address |
+----+-------------------+------------+-----------+-----------------------+---------------+-----------------------------+
| 1 | Northwind Traders | Nancy | kotas | developer | (123)555-0100 | nancy@northwindtraders.com |
| 2 | Northwind Traders | Cencini | Andrew | Vice President, Sales | 777 333 8888 | andrew@northwindtraders.com |
| 3 | Northwind Traders | Mariya | Jan | Sales Representative | 444 333 8888 | mariya@northwindtraders.com |
+----+-------------------+------------+-----------+-----------------------+---------------+-----------------------------+
3 rows in set (0.00 sec)
mysql>Now we can write an SQL query to join these two tables. This query will select all the employee names from table employees and will pick up the corresponding clients name and their email address.
mysql> select a.first_name,a.email_address,b.client_first_name,b.client_email_address
-> from employees a, clients b
-> where a.first_name = b.client_agent_name;
+------------+-----------------------------+-------------------+------------------------------+
| first_name | email_address | client_first_name | client_email_address |
+------------+-----------------------------+-------------------+------------------------------+
| Nancy | nancy@northwindtraders.com | Sergienko | mariya@northwindtraders.com |
| Cencini | andrew@northwindtraders.com | Thorpe | steven@northwindtraders.com |
| Mariya | mariya@northwindtraders.com | Neipper | michael@northwindtraders.com |
+------------+-----------------------------+-------------------+------------------------------+
3 rows in set (0.00 sec)
mysql>Using Joins in a PHP Script
You can use any of the above-mentioned SQL query in the PHP script. You only need to pass the SQL query into the PHP function mysqli_query() and then you will fetch results in the usual way.
Example
The following example −
<title>Like clause in mysql</title>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydb";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT a.first_name,a.email_address,b.client_first_name,b.client_email_address from employees a, clients b where a.first_name = b.client_agent_name";
$result = mysqli_query($conn, $sql);
echo "SELECT a.first_name,a.email_address,b.client_first_name,b.client_email_address from employees a, clients b where a.first_name = b.client_agent_name <br> <hr>";
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_array($result)) {
echo
"Employee First Name: {$row['first_name']} <br> ".
"Employee Email : {$row['email_address']} <br> ".
"Client First Name: {$row['client_first_name']} <br> ".
"Client Email : {$row['client_email_address']} <br> ".
"--------------------------------<br>";
} }
else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>MySQL LEFT JOIN
A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left.
If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join: thus ensuring (in my example) that every employee gets a mention.
Example
Try the following example to understand the LEFT JOIN.
root@host# mysql -u root -p password;
Enter password:
mysql> use TUTORIALS;
Database changed
mysql> select a.first_name,a.email_address,b.client_first_name,b.client_email_address
-> from employees a LEFT JOIN clients b
-> ON a.first_name = b.client_agent_name;
+------------+-----------------------------+-------------------+------------------------------+
| first_name | email_address | client_first_name | client_email_address |
+------------+-----------------------------+-------------------+------------------------------+
| Nancy | nancy@northwindtraders.com | Sergienko | mariya@northwindtraders.com |
| Cencini | andrew@northwindtraders.com | Thorpe | steven@northwindtraders.com |
| Mariya | mariya@northwindtraders.com | Neipper | michael@northwindtraders.com |
+------------+-----------------------------+-------------------+------------------------------+
3 rows in set (0.02 sec)You would need to do more practice to become familiar with JOINS. This is slightly a bit complex concept in MySQL/SQL and will become more clear while doing real examples.