Now, just as what is said, an inner join joins two tables together. However, what makes an inner join special is that executing the join will only return the records that are present and matching in both tables.
Using the “celeb_events” database with the two tables (events and aListCelebrities):
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)
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
event 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)
By using the following query to inner join the two tables on their IDs’:
SELECT aListCelebrities.Name, event.Name AS EventName FROM aListCelebrities INNER JOIN event ON aListCelebrities.ID = event.attendantID;
This returns the new query table:
Name EventName -------------------- ---------- Taylor Swift Grammys Stephen Curry ESPYs Michelle Yeoh Oscars (3 rows affected)
By using an inner join between the “Id’s” of both tables, the query returns the records where the “ID” from the aListCelebrities table and “attendantID” from the event table match, and those that do not have a match are not shown in the query table. In this particular scenario, Oscars attendantID 4 is not shown in the new table because they are not a registered A-list celebrity.
