What is a Left-Join in SQL?

app, application, arrow-1646213.jpg

As SQL is a programming language used to manage databases by storing and connecting thousands of categorical and numerical information, learning about joins is essential. A join in SQL is the process of combining various tables within a unique database. Popular joins include Inner Join, Left [outer] Join, Right [outer] Join, and Full [outer] Join.

Let’s look at the left-join first:

As mentioned before, a left-join connects data from two tables: a “left” table and a “right” table. Unlike other joins, however, the left join focuses on all data in the “left” table. This means that all records in the specified fields in the left table pass through the query and into the output. Furthermore, joining the “right” table onto the left using the left-join would mean that matching records from the right table will be returned and non-matching records will return null.

Here’s an example using tables “aListCelebrities” and “event” under the “celeb_events” database to see the A-list celebrities who attended specific celebrity events. Let’s say we have:

CREATE TABLE aListCelebrities(
ID int,
Name varchar(20),
famous_for varchar(20)

INSERT INTO aListCelebrities
(1, 'Taylor Swift', 'Singer'), (2, 'Stephen Curry', 'Athlete'), (3, 'Michelle Yeoh', 'Actress')
SELECT * FROM aListCelebrities

aListCelebrities table:

ID          Name                 famous_for
----------- -------------------- --------------------
1           Taylor Swift         Singer
2           Stephen Curry        Athlete
3           Michelle Yeoh        Actress

(3 rows affected)

Then to create the events table:

Name varchar(10),
attendantID int,
('Grammys', 1, '2023-02-05'), ('ESPYs', 2, '2023-07-12'), ('Oscars', 3, '2023-03-12'), ('Oscars', 4, '2023-03-12')

events table:

Name       attendantID Date
---------- ----------- ----------
Grammys    1           2023-02-05
ESPYs      2           2023-07-12
Oscars     3           2023-03-12
Oscars     4           2023-03-12

(4 rows affected)

After creating and selecting the two tables, we will left join the aListCelebrities table to the event table:

SELECT event.Name AS EventName, 
aListCelebrities.name AS AListCelebrity
FROM event
LEFT JOIN aListCelebrities
ON event.attendantID = aListCelebrities.ID
ORDER BY EventName, AListCelebrity, AListCelebrities.ID;

That finally returns the left join table…

EventName  AListCelebrity
---------- --------------------
ESPYs      Stephen Curry
Grammys    Taylor Swift
Oscars     NULL
Oscars     Michelle Yeoh

(4 rows affected)

As we can see all of the event names are returned but there is an extra Oscars attendant who is not a registered A-list celebrity!