-- Learning not just technology, but dreams!
Home HTML JAVASCRIPT CSS VUE REACT PYTHON3 JAVA C C++ C# AI GO SQL LINUX VS CODE BOOTSTRAP GIT Local Bookmarks
MySQL Tutorial
MySQL Tutorial
MySQL Installation
MySQL Management
MySQL JOIN
MySQL Create Database
MySQL Drop Database
MySQL Select Database
MySQL Data Types
MySQL Create Table
MySQL Drop Table
MySQL Insert Data
MySQL Query Data
MySQL WHERE Clause
MySQL UPDATE
MySQL DELETE Statement
MySQL LIKE Clause
MySQL UNION
MySQL ORDER BY Statement
MySQL GROUP BY
MySQL JOIN Usage
MySQL NULL Values
MySQL Regular Expressions
MySQL Transactions
MySQL ALTER Command
MySQL Indexes
MySQL Temporary Tables
MySQL Copy Tables
MySQL Metadata
MySQL Sequences
MySQL Handling Duplicates
MySQL and SQL Injection
MySQL Export Data
MySQL Import Data
MySQL Functions
MySQL Operators
MySQL Commands Reference
MySQL Quiz
MySQL Programming Languages
MySQL Node.js
MySQL PHP
MySQL Python
MySQL Java
MySQL GROUP BY Statement
MySQL NULL Values
MySQL JOIN Usage
In the previous chapters, we have learned how to read data from a single table, which is relatively simple. However, in real-world applications, it is often necessary to read data from multiple tables.
In this chapter, we will introduce how to use MySQL's JOIN to query data from two or more tables.
You can use MySQL's JOIN in SELECT, UPDATE, and DELETE statements to combine queries across multiple tables.
JOINs are generally classified into three types based on functionality:
INNER JOIN (Inner Join, or Equi-Join): Retrieves records with matching fields from both tables.
LEFT JOIN (Left Join): Retrieves all records from the left table, even if there are no matching records in the right table.
RIGHT JOIN (Right Join): Opposite of LEFT JOIN, retrieves all records from the right table, even if there are no matching records in the left table.
The database structure and data used in this chapter can be downloaded here: -mysql-join-test.sql.
INNER JOIN
INNER JOIN returns matching rows from both tables that satisfy the join condition. Here is the basic syntax for an INNER JOIN statement:
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Parameter Explanation:
column1, column2, ... are the names of the columns you want to select. Use * to select all columns.
table1, table2 are the names of the two tables to be joined.
table1.column_name = table2.column_name is the join condition, specifying the columns used for matching in the two tables.
Simple INNER JOIN:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
The above SQL statement selects the order ID and customer name from the orders and customers tables where the join condition is met.
2. Using Table Aliases:
SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;
The above SQL statement uses table aliases `o` and `c` for the orders and customers tables, respectively.
3. Multi-table INNER JOIN:
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;
The above SQL statement involves joining four tables: orders, customers, order_items, and products. It selects the order ID, customer name, and product name, joining these tables on their related columns.
4. Filtering with a WHERE Clause:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date >= '2023-01-01';
The above SQL statement uses a WHERE clause after the INNER JOIN to filter orders with an order date on or after '2023-01-01'.
LEFT JOIN
LEFT JOIN returns all rows from the left table and includes matching rows from the right table. If there are no matching rows in the right table, it returns NULL values. Here is the basic syntax for a LEFT JOIN statement:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
1. Simple LEFT JOIN:
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
The above SQL statement selects the customer ID and customer name from the customers table, including all rows from the left table (customers) and the matching order ID (if any).
2. Using Table Aliases:
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
The above SQL statement uses table aliases `c` and `o` to represent the customers and orders tables, respectively.
3. Multi-table LEFT JOIN:
SELECT customers.customer_id, customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.product_id;
The above SQL statement joins four tables: customers, orders, order_items, and products, and selects the customer ID, customer name, order ID, and product name. The LEFT JOIN ensures that customer and order information is returned even if there are no matching rows in order_items or products.
4. Filtering with a WHERE Clause:
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date >= '2023-01-01' OR orders.order_id IS NULL;
The above SQL statement uses a WHERE clause after the LEFT JOIN to filter orders with an order date on or after '2023-01-01', as well as customers with no matching orders.
LEFT JOIN is a commonly used join type, especially when you need to return all rows from the left table. When there are no matching rows in the right table, the related columns will display as NULL. When using LEFT JOIN, ensure you understand the join condition and filter the results as needed.
RIGHT JOIN
RIGHT JOIN returns all rows from the right table and includes matching rows from the left table. If there are no matching rows in the left table, it returns NULL values. Here is the basic syntax for a RIGHT JOIN statement:
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Here is a simple RIGHT JOIN example:
SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
The above SQL statement selects all order IDs from the right table (orders) and includes the matching customer ID from the left table (customers). If there is no matching customer ID in the customers table, the related columns will display as NULL.
In development, RIGHT JOIN is not frequently used because it can be achieved with a LEFT JOIN by swapping the table order. For example, the above query can be rewritten using LEFT JOIN as:
SELECT customers.customer_id, orders.order_id
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;
The above SQL statement returns the same result because LEFT JOIN and RIGHT JOIN are symmetric. In practice, you can choose which form to use based on personal preference or organizational standards.
Using INNER JOIN at the Command Prompt
INNER JOIN can join multiple tables based on specified join conditions, allowing you to retrieve related data in a query.
When using INNER JOIN, ensure the join condition is accurate and understand the relationships between the joined tables.
Example
We have two tables in the database: tcount_tbl and tutorial_tbl.
Try the following example:
Test Example Data
mysql> use ;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| tutorial_author | tutorial_count |
+---------------+--------------+
| | 10 |
| .COM | 20 |
| Google | 22 |
+---------------+--------------+
3 rows in set (0.01 sec)
mysql> SELECT * from tutorial_tbl;
+-----------+---------------+---------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | Learn PHP | | 2017-04-12 |
| 2 | Learn MySQL | | 2017-04-12 |
| 3 | Learn Java | .COM | 2015-05-01 |
| 4 | Learn Python | .COM | 2016-03-06 |
| 5 | Learn C | FK | 2017-04-05 |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)
Now, let's use MySQL's INNER JOIN (you can also omit INNER and just use JOIN, with the same effect) to join the above two tables and read the tutorial_count field value from the tcount_tbl table corresponding to all tutorial_author fields in the tutorial_tbl table:
INNER JOIN
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorial_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| a.tutorial_id | a.tutorial_author | b.tutorial_count |
+-------------+-----------------+----------------+
| 1 | | 10 |
| 2 | | 10 |
| 3 | .COM | 20 |
| 4 | .COM | 20 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)
The above SQL statement is equivalent to:
WHERE Clause
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorial_tbl a, tcount_tbl b WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| a.tutorial_id | a.tutorial_author | b.tutorial_count |
+-------------+-----------------+----------------+
| 1 | | 10 |
| 2 | | 10 |
| 3 | .COM | 20 |
| 4 | .COM | 20 |
+-------------+-----------------+----------------+
4 rows in set (0.01 sec)
MySQL LEFT JOIN
LEFT JOIN is a commonly used join type, especially when you need to return all rows from the left table.
When there are no matching rows in the right table, the related columns will display as NULL.
When using LEFT JOIN, ensure you understand the join condition and filter the results as needed.
MySQL LEFT JOIN is different from JOIN; LEFT JOIN reads all data from the left table, even if there is no corresponding data in the right table.
Example
Try the following example, using tutorial_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.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorial_tbl a LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| a.tutorial_id | a.tutorial_author | b.tutorial_count |
+-------------+-----------------+----------------+
| 1 | | 10 |
| 2 | | 10 |
| 3 | .COM | 20 |
| 4 | .COM | 20 |
| 5 | FK | NULL |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)
The above example uses LEFT JOIN. This statement reads all selected field data from the left table tutorial_tbl, even if there is no corresponding tutorial_author field value in the right table tcount_tbl.
MySQL RIGHT JOIN
MySQL RIGHT JOIN reads all data from the right table, even if there is no corresponding data in the left table.
In actual development, RIGHT JOIN is not frequently used because it can be achieved with a LEFT JOIN by swapping the table order.
Example
Try the following example, using tutorial_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.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorial_tbl a RIGHT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| a.tutorial_id | a.tutorial_author | b.tutorial_count |
+-------------+-----------------+----------------+
| 1 | | 10 |
| 2 | | 10 |
| 3 | .COM | 20 |
| 4 | .COM | 20 |
| NULL | NULL | 22 |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)
The above example uses RIGHT JOIN. This statement reads all selected field data from the right table tcount_tbl, even if there is no corresponding tutorial_author field value in the left table tutorial_tbl.
Using JOIN in PHP Scripts
In PHP, the mysqli_query() function is used to execute SQL statements. You can use the same SQL statements as parameters for the mysqli_query() function.
Try the following example:
MySQL ORDER BY Test:
<?php
$dbhost = 'localhost'; // MySQL server host address
$dbuser = 'root'; // MySQL username
$dbpass = '123456'; // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled text
mysqli_query($conn , "set names utf8");
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorial_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author';
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Cannot read data: ' . mysqli_error($conn));
}
echo '
MySQL JOIN Test';
echo '
| Tutorial ID | Author | Login Count |
';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
echo "| {$row['tutorial_id']} | ".
"{$row['tutorial_author']} | ".
"{$row['tutorial_count']} | ".
"
";
}
echo '
';
mysqli_close($conn);
?>
The output result is shown in the following image:
MySQL GROUP BY Statement
MySQL NULL Values
ByteArk Coding Plan
Supports mainstream large models like Doubao, GLM, DeepSeek, Kimi, MiniMax, officially supplied, stable and reliable.
Configuration Guide
¥9.9
/month
Subscribe Now
iFlytek Star Coding Plan
Includes free model call quota, DeepSeek, GLM, Kimi, MiniMax, one-stop experience and deployment platform.
Configuration Guide
¥3.9
/month
Subscribe Now
Click to Share Notes
Category Navigation
Python / Data Science
AI / Intelligent Development
Frontend Development
Backend Development
Database
Mobile Development
DevOps / Engineering
Programming Languages
Computer Fundamentals
XML / Web Service
.NET
Website Construction
Advertisement
Explore Further
Language Resources
Scripting Languages
Educational Resources
Data Management
Programming
Distance Education
Programming Languages
Social Sciences
MySQL Tutorial
MySQL Tutorial
MySQL Installation
MySQL Management
MySQL JOIN
MySQL Create Database
MySQL Drop Database
MySQL Select Database
MySQL Data Types
MySQL Create Table
MySQL Drop Table
MySQL Insert Data
MySQL Query Data
MySQL WHERE Clause
MySQL UPDATE
MySQL DELETE Statement
MySQL LIKE Clause
MySQL UNION
MySQL ORDER BY Statement
MySQL GROUP BY
MySQL JOIN Usage
MySQL NULL Values
MySQL Regular Expressions
MySQL Transactions
MySQL ALTER Command
MySQL Indexes
MySQL Temporary Tables
MySQL Copy Tables
MySQL Metadata
MySQL Sequences
MySQL Handling Duplicates
MySQL and SQL Injection
MySQL Export Data
MySQL Import Data
MySQL Functions
MySQL Operators
MySQL Commands Reference
MySQL Quiz
MySQL Programming Languages
MySQL Node.js
MySQL PHP
MySQL Python
MySQL Java
Online Examples
·HTML Examples
·CSS Examples
·JavaScript Examples
·Ajax Examples
·jQuery Examples
·XML Examples
·Java Examples
Character Sets & Tools
· HTML Character Set Settings
· HTML ASCII Character Set
· JS Obfuscation/Encryption
· PNG/JPEG Image Compression
· HTML Color Picker
· JSON Formatter Tool
· Random Number Generator
Latest Updates
· AI Ethics and Safety
· How AI Works
· AI Tool Panorama
· Prompt Prompts
· AI Introduction
· AI Beginner Tutorial
· Large Model Multimodal (M...
Site Information
· Feedback
· Disclaimer
· About Us
· Article Archive
Follow WeChat
My Favorites
Mark Article
Browsing History
Clear All
No Records
YouTip