A LEFT JOIN in MySQL is used to retrieve data from multiple tables based on a related column between them. It returns all records from the left table (table1), and the matched records from the right table (table2). If there's no match, NULL values are returned for the columns from the right table.
Example of the students and courses tables. This time, we'll use a LEFT JOIN to retrieve all students, even if they are not enrolled in any courses.
Students table:
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 21 |
| 4 | David | 23 |
+----+-----------+-----+courses table:
+----+------------+------------+
| id | name | student_id |
+----+------------+------------+
| 1 | Math | 1 |
| 2 | Physics | 2 |
| 3 | Chemistry | 1 |
| 4 | Biology | 3 |
| 5 | History | 2 |
| 6 | English | NULL |
+----+------------+------------+Now, let's modify the query to include additional information such as student age and handle cases where a course might not have any enrolled students:
SELECT students.name AS student_name, students.age AS student_age, courses.name AS course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;In this query:
- We're selecting the student names (students.name) and ages (students.age) from the students table, along with the course names (courses.name) from the courses table.
- We're using a LEFT JOIN to ensure that all rows from the students table are included in the result, even if there are no matching rows in the courses table.
- The condition ON students.id = courses.student_id specifies how the tables are joined.
The result of this query will be:
+--------------+------------+-------------+
| student_name | student_age| course_name |
+--------------+------------+-------------+
| Alice | 20 | Math |
| Alice | 20 | Chemistry |
| Bob | 22 | Physics |
| Bob | 22 | History |
| Charlie | 21 | Biology |
| David | 23 | NULL |
+--------------+------------+-------------+