How to use the LEFT JOIN function in MySQL to get all records from the left table

How to use the LEFT JOIN function in MySQL to get all the records of the left table

In MySQL, we can use the LEFT JOIN function to easily get all the records of the left table. LEFT JOIN is a join query that returns all records in the left table while matching the corresponding records in the right table.

The syntax of LEFT JOIN is as follows:

SELECT column name
FROM left table
LEFT JOIN right table
ON left table.column name = right table.column name;

Among them, the left table and the right table are the two tables that need to be connected, and the ON clause is the connection condition. Match the column names in the ON clause to determine the association between the left table and the right table.

The following uses a specific example to demonstrate how to use the LEFT JOIN function to obtain all records in the left table.

Suppose we have two tables: students table (students) and course selection table (course_selections).

The structure of the students table is as follows:

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
gender VARCHAR(10) NOT NULL
);

The structure of the course selection table is as follows:

CREATE TABLE course_selections (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_name VARCHAR(100) NOT NULL
);

Now, we want to get the information of all students, including students who have not selected courses. We can do this using the LEFT JOIN function.

The code example is as follows:

SELECT students.id, students.name, students.age, students.gender, course_selections.course_name
FROM students
LEFT JOIN course_selections
ON students.id = course_selections.student_id;

In the above code, we first select the id, name, age and gender columns of the student table, and then connect the student table and course selection table through the LEFT JOIN function. The connection condition is that the id column of the student table is equal to the student_id column of the course selection table.

In this way, we can obtain all the records in the left table (that is, all the records in the student table), and display the corresponding course selection information through the LEFT JOIN function. If a student has not selected a course, the corresponding course selection information is displayed as NULL.

In this way, we can quickly and flexibly obtain all the records in the left table, and display relevant information in the right table according to actual needs.

To sum up, the LEFT JOIN function in MySQL is a very useful connection query method. It can help us obtain all the records of the left table and display relevant information of the right table according to the connection conditions. By using the LEFT JOIN function flexibly, we can easily solve many practical problems.

The above is the detailed content of How to use the LEFT JOIN function in MySQL to get all records from the left table. For more information, please follow other related articles on the PHP Chinese website!