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 VALUES (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:
CREATE TABLE event(
Name varchar(10),
attendantID int,
Date DATE
)
INSERT INTO event
VALUES
('Grammys', 1, '2023-02-05'), ('ESPYs', 2, '2023-07-12'), ('Oscars', 3, '2023-03-12'), ('Oscars', 4, '2023-03-12')
SELECT * FROM event
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!
