Besides INNER JOIN, what other types of JOIN are there in MySQL?

In MySQL, JOIN is an operation used to join multiple tables by matching rows in two or more tables to obtain related data. In addition to the common INNER JOIN, MySQL also supports several other types of JOIN operations, including LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS JOIN.

In relational databases, JOIN is a very important operation that can be used to join data in two or more tables to obtain more comprehensive and accurate data. MySQL provides multiple JOIN types, each JOIN type has its specific purpose and syntax. Understanding these different JOIN types can help us use them flexibly in actual queries and improve query efficiency and accuracy.

INNER JOIN

INNER JOIN is the most common and basic JOIN type, which returns data that meets conditions by matching rows in two tables. INNER JOIN returns only matching rows that exist in both tables and can be used to obtain related data.

INNER JOIN syntax and examples

SELEC T 列名
FROM 表1
INNER JOIN 表2
ON 表1.列 = 表2.列;
1.
2.
3.
4.

Example:

SELEC T Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
1.
2.
3.
4.

LEFT JOIN

LEFT JOIN returns all rows in the left table and matching rows in the right table. If there is no matching row in the right table, a NULL value is returned. LEFT JOIN can be used to obtain all data in the left table and the data in the right table associated with it.

LEFT JOIN syntax and examples

SELEC T 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列 = 表2.列;
1.
2.
3.
4.

Example:

SELEC T Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
1.
2.
3.
4.

RIGHT JOIN

RIGHT JOIN returns all rows in the right table and matching rows in the left table. If there is no matching row in the left table, a NULL value is returned. RIGHT JOIN can be used to obtain all data in the right table and the data in the left table associated with it.

RIGHT JOIN syntax and examples

SELEC T 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.列 = 表2.列;
1.
2.
3.
4.

Example:

SELEC T Employees.LastName, Orders.OrderID
FROM Employees
RIGHT JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID;
1.
2.
3.
4.

FULL JOIN

FULL JOIN returns all rows in the left and right tables, or NULL if there are no matching rows in the left or right tables. FULL JOIN can be used to obtain all data from the left table and the right table.

FULL JOIN syntax and examples

SELEC T 列名
FROM 表1
FULL JOIN 表2
ON 表1.列 = 表2.列;
1.
2.
3.
4.

Example:

SELEC T Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
1.
2.
3.
4.

CROSS JOIN

CROSS JOIN is a Cartesian product operation that returns all possible combinations of two tables. That is, it combines each row of the left table with each row of the right table to generate a new result set.

CROSS JOIN syntax and examples

SELEC T 列名
FROM 表1
CROSS JOIN 表2;
1.
2.
3.

Example:

SELEC T Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;
1.
2.
3.

In MySQL, JOIN is an important operation used to join multiple tables. In addition to the common INNER JOIN, MySQL also supports different types of JOIN operations such as LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS JOIN. By choosing the appropriate JOIN type and using its syntax flexibly, we can efficiently join multiple tables to obtain accurate and comprehensive data to meet actual query needs.