MySQL - Left Joins

In MySQL, a LEFT JOIN can be used to join data from multiple tables together. One of the unique things about a LEFT JOIN is it returns all the requested rows from the LEFT table even if no matches exist from the RIGHT table. Let's gain a better understanding of this with a practical example. Pretend we work for the administration department of a college and we need to query student class records to make sure all the students registered for there classes. Let's create our database and populate it with some data to query!

First, spin up a MySQL docker image to play with.


docker run -d -e MYSQL_ROOT_PASSWORD=root -p 3308:3306 --name left-join mysql:latest

We set the root password so the container will start, name the container left-join and map the host port 3308 to the container port 3306 to avoid conflict if mysql is running on the host.

Next exec into your mysql container.


docker exec -it left-join /bin/bash

Log into your MySQL database (enter your password in the prompt), create a school create database and set MySQL to use it


mysql -u root -p

CREATE DATABASE school;

USE school;

Create the student table.


CREATE TABLE students (
    ID int NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    phone int NOT NULL,
    email varchar(255) NOT NULL,
    city varchar(255) NOT NULL,
    state varchar(255) NOT NULL
);

Verify the table wa created correctly. I like to use show tables and describe table in one line. It's a personal preference but it confirms the table was created and defined correctly.


SHOW TABLES; DESCRIBE TABLE students;

Let's create some records.


INSERT INTO students (first_name, last_name, phone, email, city, state) VALUES ('John', 'Smith', 111223344, 'some_email@test.com', 'New York', 'New York');

INSERT INTO students (first_name, last_name, phone, email, city, state) VALUES ('Jane', 'Doe', 444332255, 'email.address@random.com', 'San Marcos', 'Texas');

INSERT INTO students (first_name, last_name, phone, email, city, state) VALUES ('Tim', 'Jones', 333225544, 'the.real.tim.jones@gmail.com', 'Asheville', 'North Carolina');

Confirm the data is in the table.


SELECT * FROM students;

Repeat the steps for the class records table


CREATE TABLE class_records (
    ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subject varchar(255) NOT NULL,
    name varchar(255) NOT NULL,
    student_id int NOT NULL,
    KEY (student_id)
);

SHOW TABLES; DESCRIBE TABLE class_records;

INSERT INTO class_records (subject, name, student_id) VALUES ('Math', 'Advanced Calculus', 1);

INSERT INTO class_records (subject, name, student_id) VALUES ('Science', 'Physics', 1);

INSERT INTO class_records (subject, name, student_id) VALUES ('Math', 'Advanced Calculus', 2);

INSERT INTO class_records (subject, name, student_id) VALUES ('Science', 'Biology', 2);

INSERT INTO class_records (subject, name, student_id) VALUES ('English', 'Grammar', 2);

SELECT * FROM class_records;

Now that we've created both tables and popuated them with data, let's do a left join.


SELECT s.first_name, s.last_name, c.subject, c.name FROM students as s LEFT JOIN class_records as c ON s.id = c.student_id;

Notice that all the rows returned even though Tim Jones doesn't have any records in the class records table. His student id isn't in the table. Even though there are no matches, that's the sql magic of a left join. There are several use cases where you need to check if matches don't exist and use the null results. This next example demonstrates this further.


SELECT s.first_name, s.last_name, c.subject, c.name FROM students as s LEFT JOIN class_records as c ON s.id = c.student_id AND s.id = 2;

Notice now that only Jane Doe's classes were matched, even though all the rows were returned. Not really a good use case but it further demonstrates how a LEFT JOIN works. If we move the s.id = 2 out of the ON clause and into a WHERE clause, we get a very different result...


SELECT s.first_name, s.last_name, c.subject, c.name FROM students as s LEFT JOIN class_records as c ON s.id = c.student_id WHERE s.id = 2;

Writing the query this way ONLY returns Jane Doe's records because it limits results to the student with id = 2.

Conclusion

LEFT JOINS in MySQL are a great tool for when you need all the rows from the first table to populate, even if there are no matches from the adjoining tables. There are several use cases where the null results of none matching rows can help to change functionality, from UIs to shopping carts to invoices. LEFT JOINS should be in every database admin and software engineer's toolbelt.