How to perform multi-table query in MySQL?

How to perform multi-table query in MySQL?

In database queries, multi-table queries are a common requirement. Through multi-table query, we can connect and correlate data in multiple tables to get more accurate and comprehensive query results. MySQL provides a variety of ways to perform multi-table queries, including using JOIN statements, subqueries, and union queries. This article will introduce how to perform multi-table queries in MySQL, with code examples.

1. Use INNER JOIN for multi-table queries
   INNER JOIN is one of the most common and commonly used multi-table query methods. It connects the associated fields of two or more tables. Returns records that meet the join conditions. The following is an example:

SELECT 
    Orders.OrderID,
    Customers.CustomerName,
    Orders.OrderDate
FROM
    Orders
INNER JOIN
    Customers ON Orders.CustomerID = Customers.CustomerID;

The above query will return records from the Orders table and Customers table that meet the connection conditions. The query results include the OrderID, CustomerName and OrderDate fields.

2. Use LEFT JOIN for multi-table query
   LEFT JOIN is another common multi-table query method. It combines all records in the left table (left table) and the right table (right table ) to match records that meet the connection conditions. If there is no matching record in the right table, the corresponding field in the left table will display NULL. The following is an example:

SELECT 
    Customers.CustomerName,
    Orders.OrderID
FROM
    Customers
LEFT JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID;

The above query will return all Customers table records and match Orders table records that meet the connection conditions. The query results include the CustomerName and OrderID fields.

3. Use RIGHT JOIN for multi-table query
   RIGHT JOIN is the opposite of LEFT JOIN. It combines all the records in the right table (right table) and the records in the left table (left table) that meet the connection conditions. Records are matched. If there is no matching record in the left table, the corresponding field in the right table will display NULL. The following is an example:

SELECT 
    Orders.OrderID,
    Customers.CustomerName
FROM
    Orders
RIGHT JOIN
    Customers ON Orders.CustomerID = Customers.CustomerID;

The above query will return all Orders table records and match the Customers table records that meet the connection conditions. The query results include the OrderID and CustomerName fields.

4. Use FULL OUTER JOIN for multi-table query
   FULL OUTER JOIN is a query method that combines LEFT JOIN and RIGHT JOIN. It returns all records that meet the connection conditions and will not satisfy the Records for the condition appear as NULL. However, MySQL does not directly support FULL OUTER JOIN, but you can use the UNION operator to achieve similar effects. The following is an example:

SELECT 
    Customers.CustomerName,
    Orders.OrderID
FROM
    Customers
LEFT JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
UNION
SELECT 
    Customers.CustomerName,
    Orders.OrderID
FROM
    Customers
RIGHT JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
    Customers.CustomerID IS NULL;

The above query will return all Customers table records and Orders table records that meet the connection conditions, and display records that do not meet the conditions as NULL.

Multi-table query is one of the important skills in database query. By rationally using connection operators and conditions, we can perform related queries on multiple tables according to actual needs and obtain accurate and comprehensive results. I hope this article will help you understand how to perform multi-table queries in MySQL.

The above is the detailed content of How to perform multi-table query in MySQL?. For more information, please follow other related articles on the PHP Chinese website!