Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

In MySQL,LEFT JOINUsed to return all records from the left table (i.e. the table to the left of the LEFT JOIN keyword), even if there are no matching records in the right table. For those records that do not have a matching record in the right table, the right table portion of the result set will be filled with NULL.ONandWHEREThe use of clauses, they areLEFT JOINThe roles are different:

• ON Clause: used to define the join conditions, that is, to determine which records should be joined together. When you need to combine records based on the relationship between certain field values ​​in the left and right tables, these conditions should be placed inONBehind. IfONThere are additional conditions for the right table later, which will also be applied during the connection, but will not affect the principle of returning all records from the left table.
• WHERE Clause: used toJOINThe result set produced by the operation is further filtered.WHEREWhen a clause is included, records that do not meet the conditions (whether from the left or right table) will be removed, which may affect the original intention of returning all records from the left table, especially when dealing with NULL values.

Specific case 1:

Suppose we have two tables, one isemployees(employee table), the other isdepartments(Department table), we want to find all employees and their department names, even if some employees are not assigned to a specific department.

employees table:

departments table:

in,employeesTabledepartment_idThe field can be NULL, indicating that no department is assigned.

Example using ON:

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

                    上一篇
                    下一篇