MySQL joint query tutorial

MySQL joint query tutorial

In MySQL, joint queries are used to retrieve data from multiple tables, often used to associate information in tables. Joint query (JOIN) forms a virtual result set by joining two or more tables according to certain conditions. MySQL supports multiple types of joint queries, includingINNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOINwait.

This article will introduce in detail the use of MySQL joint query, helping you master different types of joins and their application scenarios.

1. Basic joint query

1.1 INNER JOIN(Inner connection)

INNER JOINIt is the most common way of joining, it returns matching records in two tables. If a row in one table does not match a row in another table, the row data will not appear in the result set.

grammar:

SELECT column_names
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Suppose there are two tablesemployees(employee form) anddepartments(Department table):

employeessurface:

departmentssurface:

QueryemployeesanddepartmentsThe matching record in the table returns the employee name and department name:

SELECT  AS employee_name,  AS department_name
FROM employees
INNER JOIN departments
ON employees.department_id = ;

result:

1.2 LEFT JOIN(Left connection)

LEFT JOINReturns all records in the left table, as well as the matching records in the right table. If there is no matching record on the right table, returnNULL。

grammar:

SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = ;

result:

Assume we areemployeesAn employee without a corresponding department was added to the table:

INSERT INTO employees (name, department_id) VALUES ('David', NULL);

The query result will be:

1.3 RIGHT JOIN(Right Connection)

RIGHT JOINReturns all records in the right table, as well as matching records in the left table. If there is no matching record on the left table, returnNULL。

grammar:

SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = ;

result:

ifdepartmentsA department without employees was added to the table:

INSERT INTO departments (id, name) VALUES (3, 'Finance');

The query result will be:

1.4 FULL OUTER JOIN(Full connection)

FULL OUTER JOINReturns all records in the left and right tables. If the rows in one table do not match the rows in another table, returnNULL. MySQL itself does not support it directlyFULL OUTER JOIN, but can be passedUNIONTo simulate.

grammar:

SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
UNION
SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = 
UNION
SELECT  AS employee_name,  AS department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = ;

result:

2. Joint query based on multiple conditions

2.1 Connect with multiple conditions

Can beONMultiple conditions are used for joint query in clauses, and multiple conditions are used betweenANDorORMake a connection.

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
INNER JOIN departments
ON employees.department_id = 
AND  > 25;

This query returns the name of an employee older than 25 years of age in the department.

2.2 UseUSINGKeywords

If the two tables have the same column name, you can useUSINGTo simplify query statements.USINGThe keyword automatically takes the same column name as the join condition.

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
INNER JOIN departments
USING (department_id);

3. Sort and limitations in joint query

3.1 Sort query results

Can be used in joint queryORDER BYto sort the results. The sorting can be based on one or more columns.

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
INNER JOIN departments
ON employees.department_id = 
ORDER BY ;

3.2 Limit query results

AvailableLIMITStatement limits the number of records returned by a joint query.

Example:

SELECT  AS employee_name,  AS department_name
FROM employees
INNER JOIN departments
ON employees.department_id = 
LIMIT 5;

4. Summary

This article introduces the commonly used joint query types in MySQL, including:

• INNER JOIN: Returns the matching records in the two tables.
• LEFT JOIN: Returns all records in the left table and matching records in the right table.
• RIGHT JOIN: Returns all records in the right table and matching records in the left table.
• FULL OUTER JOIN: Return all records in both tables, usingUNIONsimulation.
• Multiple conditional connections: Can be usedAND、ORFor multiple conditions, connect and query.

Mastering the usage of these joint queries can help you operate MySQL database more flexibly and obtain the required results. If you want to learn more about it, please refer to itMySQL official documentation.

This is the end of this article about MySQL joint query tutorial. For more related mysql joint query content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!