SQL - Left Join vs Right Join: A Comprehensive Guide for Beginners

Hello there, aspiring SQL enthusiasts! I'm thrilled to be your guide on this exciting journey through the world of SQL joins. As someone who's been teaching computer science for years, I can assure you that mastering joins is like unlocking a superpower in the database realm. So, let's dive in and unravel the mysteries of Left Join and Right Join together!

SQL - Left Join vs Right Join

Understanding Joins: The Basics

Before we delve into the specifics of Left and Right Joins, let's start with a quick refresher on what joins are in SQL. Imagine you're planning a party and have two lists: one with guests' names and another with their favorite drinks. Joins are like magic wands that help you combine these lists in various ways to get the information you need.

Working of Left Join

What is a Left Join?

A Left Join is like inviting everyone on your guest list to the party, regardless of whether you know their favorite drink or not. It returns all records from the left table (the first table you mention in the query) and the matching records from the right table.

Left Join Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Left Join Example

Let's create two simple tables to illustrate this:

CREATE TABLE Guests (
    GuestID INT PRIMARY KEY,
    GuestName VARCHAR(50)
);

CREATE TABLE Drinks (
    DrinkID INT PRIMARY KEY,
    GuestID INT,
    FavoriteDrink VARCHAR(50)
);

INSERT INTO Guests (GuestID, GuestName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

INSERT INTO Drinks (DrinkID, GuestID, FavoriteDrink) VALUES
(1, 1, 'Mojito'),
(2, 2, 'Beer'),
(3, 3, 'Wine');

Now, let's use a Left Join to get all guests and their favorite drinks (if known):

SELECT Guests.GuestName, Drinks.FavoriteDrink
FROM Guests
LEFT JOIN Drinks ON Guests.GuestID = Drinks.GuestID;

Result:

GuestName	FavoriteDrink
Alice	Mojito
Bob	Beer
Charlie	Wine
David	NULL

As you can see, David appears in the result even though we don't know his favorite drink. That's the magic of Left Join!

Working of Right Join

What is a Right Join?

A Right Join is like focusing on the drinks list and seeing which guests prefer each drink. It returns all records from the right table and the matching records from the left table.

Right Join Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Right Join Example

Using our previous tables, let's perform a Right Join:

SELECT Guests.GuestName, Drinks.FavoriteDrink
FROM Guests
RIGHT JOIN Drinks ON Guests.GuestID = Drinks.GuestID;

Result:

GuestName	FavoriteDrink
Alice	Mojito
Bob	Beer
Charlie	Wine

Notice how David doesn't appear in this result because he doesn't have a favorite drink listed.

Left Join Vs Right Join

Now that we've seen both joins in action, let's compare them side by side:

Aspect	Left Join	Right Join
Focus	All records from the left (first) table	All records from the right (second) table
Null values	In right table columns for non-matches	In left table columns for non-matches
Use case	When you want all records from the main table	When you want all records from the joined table
Result set size	Always includes all left table records	Always includes all right table records

When to Use Left Join vs Right Join

Use Left Join when:
- You want to keep all records from the main (left) table.
- You're okay with having NULL values for unmatched records from the right table.
Use Right Join when:
- You want to keep all records from the secondary (right) table.
- You're okay with having NULL values for unmatched records from the left table.

Pro Tip

In my years of teaching, I've noticed that many developers prefer using Left Joins over Right Joins. Why? It's often more intuitive to read and understand. You can always rewrite a Right Join as a Left Join by simply swapping the table order. So, if you find Right Joins confusing, feel free to stick with Left Joins!

Practical Exercise

Let's cement our understanding with a fun exercise. Imagine you're organizing a book club. You have two tables: Members and Books.

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    MemberName VARCHAR(50)
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    MemberID INT,
    BookTitle VARCHAR(100)
);

INSERT INTO Members (MemberID, MemberName) VALUES
(1, 'Emma'),
(2, 'James'),
(3, 'Sophia'),
(4, 'Oliver');

INSERT INTO Books (BookID, MemberID, BookTitle) VALUES
(1, 1, 'Pride and Prejudice'),
(2, 2, '1984'),
(3, 2, 'To Kill a Mockingbird'),
(4, 3, 'The Great Gatsby');

Now, try these queries:

Left Join to get all members and their books (if any):

SELECT Members.MemberName, Books.BookTitle
FROM Members
LEFT JOIN Books ON Members.MemberID = Books.MemberID;

Right Join to get all books and their owners (if any):

SELECT Members.MemberName, Books.BookTitle
FROM Members
RIGHT JOIN Books ON Members.MemberID = Books.MemberID;

Compare the results. Can you spot the differences?

Conclusion

Congratulations! You've just mastered the art of Left and Right Joins in SQL. Remember, practice makes perfect, so don't hesitate to experiment with these joins using different scenarios.

As a parting thought, I always tell my students: "SQL joins are like puzzle pieces. The more you play with them, the better you'll become at putting together the big picture of your data!"

Happy querying, and may your joins always be spot on!

Credits: Image by storyset