SQL LEFT JOIN - Learn the LEFT JOINS in SQL Examples

LEFT JOIN is a keyword in SQL that allows you to select all the rows from the left table (the one that you mentioned first) and join it with the right table. If there are no matching rows from the right table, then it will fill NULL values for columns from the right table. If you want to give priority to one table, LEFT JOIN can be the best way to do it.

In this tutorial, we are going to learn the implementation of the LEFT JOIN command in SQL, syntax, and some examples to help you understand how to use LEFT JOIN.

Table of Content

• What is LEFT JOIN in SQL
• SQL LEFT JOIN Syntax
• SQL LEFT JOIN Example
• Conclusion

What is LEFT JOIN in SQL

The LEFT JOIN in SQL basically returns all records from the left table and the matched records from the right tables. If any row is present in the left table and is not present in the right table, then the result table will include that row, but there will be a NULL value in each column from the right table. 

 For example, let’s say, we have two tables, Table A and Table B. When LEFT JOIN is applied on these two tables, all records from Table A and only the matched records from Table B will be displayed.

SQL LEFT JOIN Syntax

SELECT columns

FROM table1 name

LEFT JOIN table2 name

ON table1.coumn_x = table2.column_y;

Where SELECT, LEFT JOIN, and ON are the keywords, columns are the list of columns, table1 is the first table and table2 is the second table, and column_x and column_y are the columns for performing the LEFT JOIN, followed by a semicolon.

SQL LEFT JOIN Example

Let’s create the Student table having columns, StudentID, Name and CourseID. Make sure that StudentID is a primary key.

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Name VARCHAR(50),

    CourseID INT

);

INSERT INTO Students (StudentID, Name, CourseID)

VALUES 

(1, 'John', 101),

(2, 'Sarah', 102),

(3, 'Mike', NULL),

(4, 'Emma', 104);

Let’s create the second table having columns that are CourseID, CourseName and Instructor. Here, CourseID will be the primary key.

CREATE TABLE Courses (

    CourseID INT PRIMARY KEY,

    CourseName VARCHAR(50),

    Instructor VARCHAR(50)

);

INSERT INTO Courses (CourseID, CourseName, Instructor)

VALUES 

(101, 'Python for Beginners', 'John Doe'),

(102, 'Data Science with AI', 'Jane Smith'),

(103, 'Advanced SQL', 'James Lee');

Let’s perform LEFT JOIN on the above two tables.

SELECT 

    Students.StudentID,

    Students.Name AS StudentName,

    Courses.CourseName,

    Courses.Instructor

FROM 

    Students

LEFT JOIN 

    Courses

ON 

    Students.CourseID = Courses.CourseID;

Here, the Student table is my Left Table as you can see that this is first table in the query, right after FROM.

Courses is my Right Table as it is mentioned after LEFT JOIN.

The LEFT JOIN will make sure that all rows from the Student table are included in the result. If there is no match then Courses table will have NULL values in those rows.

Conclusion

LEFT JOIN is one of the most essential SQL tools which can be used for merging data from datasets while not missing data in the main table in cases when matches are not found. It’s perfectly useful in the treatment of incomplete or irregular data and gives room for flexibility in queries. Once you master this, you will be able to uncover insights, handle null values properly, and generate solid reports. Try using LEFT JOINs with various datasets to appreciate their power. With this skill, you’re well on your way to becoming a more effective data analyst or developer. Happy querying!