In relational databases, multi-table joins (JOINs) are common operations for retrieving data from multiple tables. By joining multiple tables, relevant data scattered in different tables can be combined together for more complex queries and analysis. In this article, we will dive into multi-table joins in MySQL and their various join types to help you better understand and apply these features.
What is a multi-table join?
Multi-table join refers to the operation of combining rows of data of two or more tables through one or more common fields. Common join conditions are based on foreign key relationships between tables. For example, the order table and the customer table can be connected by the customer's ID to obtain the customer information corresponding to each order.
MySQL supports multiple types of connections, mainly including the following:
1. Inner JOIN
Inner join returns matching rows in two tables that meet the join conditions. The result will be returned only if the connection condition is true. If there are no matching rows in a table, they will not appear in the result set.
grammar:
SELECT columns FROM table1 INNER JOIN table2 ON = ;
Example:Suppose we have two tablescustomers(Customer Table) andorders(Order form), we want to query all customer information with order records:
SELECT customers.customer_id, , orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
2. Left connection (LEFT JOIN or LEFT OUTER JOIN)
A left join returns all rows in the left table, even if there are no matching rows in the right table. For rows that do not match in the right table, the NULL value is displayed in the result set.
grammar:
SELECT columns FROM table1 LEFT JOIN table2 ON = ;
Example:Suppose we want to query all customers' information and display their order record (if any), even if some customers don't have any orders:
SELECT customers.customer_id, , orders.order_id, orders.order_date FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
3. Right connection (RIGHT JOIN or RIGHT OUTER JOIN)
Right join returns all rows in the right table, even if there are no matching rows in the left table. For rows that do not match in the left table, the NULL value is displayed in the result set.
grammar:
SELECT columns FROM table1 RIGHT JOIN table2 ON = ;
Example:Suppose we want to query the information of all orders and display their corresponding customer information (if any), even if some orders are not associated with customers:
SELECT customers.customer_id, , orders.order_id, orders.order_date FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
4. Full external connection (FULL OUTER JOIN)
Fully outer join returns all rows in both tables, whether the join condition is satisfied or not. For rows that do not match, the NULL value is displayed in the result set. It should be noted that MySQL does not directly support FULL OUTER JOIN, but similar effects can be achieved through UNION combined with LEFT JOIN and RIGHT JOIN.
Example:Suppose we want to query information for all customers and orders, including those without orders and orders without customer associations:
SELECT customers.customer_id, , orders.order_id, orders.order_date FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id UNION SELECT customers.customer_id, , orders.order_id, orders.order_date FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
5. Self Join
Self-joining refers to joining the same table with itself. It is usually used to query data with hierarchical structures, such as employee superior-level relationships.
grammar:
SELECT a.column_name, b.column_name FROM table_name a JOIN table_name b ON a.common_column = b.common_column;
Example:Suppose we have an employee listemployees, which contains the employee's ID, name and the ID of the superior manager. We want to query information for each employee and their superior manager:
SELECT e1.employee_id, AS employee_name, AS manager_name FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
Cross-connection (CROSS JOIN)
Cross join returns the Cartesian product of the two tables, that is, each row of the left table and each row of the right table. The number of rows in the result set is equal to the number of rows on the left table multiplied by the number of rows on the right table.
grammar:
SELECT columns FROM table1 CROSS JOIN table2;
Example:Suppose we have two tablescolorsandsizes, we want to list all the combinations of colors and sizes:
SELECT , FROM colors CROSS JOIN sizes;
Summarize
Through this article, you should have a clearer understanding of multi-table joins in MySQL and their various join types. Multi-table connection is a very powerful tool in database queries that can help us extract and combine data from multiple tables and perform complex data analysis. Choosing the appropriate connection type according to specific needs can improve query efficiency and ensure the accuracy of the results.
This is the article about multi-table connections and connection types in Mysql. For more related contents of mysql multi-table connections and connection types, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!