FAQ: Multiple Tables - Left Joins

This community-built FAQ covers the “Left Joins” exercise from the lesson “Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise Left Joins

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

When the task asked us to find the number of users who subscribed to the print newspaper but not to the online, can we just do a SELECT * FROM newspaper; instead of taking a left join of newspaper and online, and then eliminating all those rows in online that have a valid id?

1 Like

Can anyone explain the logic behind number 2 of this problem?

It says to join newspaper.id = online.id and then where online.id is NULL;

Wouldn’t that mean that the newspaper id is also null?

If you just write SELECT * FROM newspaper; , you actually do a search only to the newspaper table. However, the task is asking you to do a search at the second table (online) as well in order to find those that are not line subscribers. This simultaneous query at the 2 tables can be done here only by using LEFT JOIN. Otherwise, your query is limited only to the newspaper table.

No it wouldn’t. It’s actually showing you ONLY customers who have print subscriptions and not online. The first query shows you people who 1) have both PRINT and ONLINE subscriptions and 2) only have PRINT subscriptions. To figure out which ones only have PRINT from the first query you will have to manually eye scan the results for NULLs in the online section; the task is teaching you how to whittle down the results from the first query quickly by using the ONLNE.ID = NULL command.

I wondered the same thing

Hello, @tsfrankenberg. Welcome to the forum.
If we only do a SELECT query from the newspaper table, we will know how many newspaper subscribers there are in total, but many of those subscribers are also online subscribers. By doing the LEFT JOIN, and then eliminating the online subscribers we eliminate those who have subscribed to both, and are left with newspaper only subscribers.

Thanks @midlinder! I hear that. To @meeps123 's point though, we could achieve the same result with SELECT * FROM newspaper; could we not? Using the left join here is just for purpose of illustration of the left join concept.

No. The newspaper table doesn’t have a column to tell us that the subscriber is also subscribed to the online paper. We perform the LEFT JOIN, so that we combine the 2 tables, and then we can filter the data down to newspaper only subscribers. In the exercise, try it both ways, and you’ll see that there are fewer newspaper only subscribers than total newspaper subscribers.

1 Like

Got it - makes total sense. Thank you for clarifying!

1 Like

Hello. When I run SELECT COUNT(*) FROM newspaper; I get 60 subscribers. When I run the LEFT JOIN, I get a count of 10.

If LEFT JOIN keeps all rows from newspaper regardless of a match, that means where newspaper.id and online.id match. The omitted rows (WHERE id IS NULL) would be online subscribers only. I don’t see how the final answer is only newspaper.

So isn’t the output, subscribers who are both newspaper and online?

Hello @cssninja00586. Welcome to the forum.
The WHERE clause should be WHERE online.id IS NULL; It does only return 10 rows. If you look at the schemas for both tables, newspaper and online, you’ll see that there are 65 newspaper subscribers, and 65 online subscribers. When we do the LEFT JOIN we keep all 60 newspaper subscribers, and include any online subscribers who are also newspaper subscribers. Then when we select only those online id is NULL, we are left with those who only subscribe to the actual printed newspaper. There are only 10 of those.

These are not online only subscribers. They are those who subscribe to both. Online only subscribers were not included when we ran the LEFT JOIN.

Hope this helps!

2 Likes

Remember we are only interest with the print newspaper subscribers only and there are subscribers who have subscribed to both print and online. So to get only those who have subscribed for the print only we have to left join the print and select all null online subscribers.

I hope this helps anyone:

  • The newspaper table contains 50 users with both physical and online subscriptions and 10 with only physical subscription for a total of 60. That conclusion I reached with this query:
SELECT count(*)
FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id;

SELECT count(*)
FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id
WHERE online.id IS NULL;

To my understanding, it works like this: newspaper merges all those id that match on the online table, which are 50 rows. Then the other 10 rows are those that are only contained in the newspaper table, that’s why we can identify them by calling IS NULL on the online table because those are not present in there, giving us the number of people exclusively subscribed to the physical format. This train of thought you can apply also to the next conclusion.

  • The online table contains 50 users with both physical and online subscriptions and 15 only online for a total of 65. This one I reached with this:
SELECT count(*)
FROM online
LEFT JOIN newspaper
ON newspaper.id = online.id;

SELECT count(*)
FROM online
LEFT JOIN newspaper
ON newspaper.id = online.id
WHERE newspaper.id IS NULL;
  • That leads us to the conclusion that there are 75 individual subscribers to the news agency.

I think that some of the confusion on this exercise may stem from the graphic that initially explains the concept of a left join; while the graphic combines the two columns specified in the ON condition, it seems this is not the case; rather, the ON condition determines how the rows of the two tables line up. The LEFT JOIN in step 2 does not result in a table that has only one ID colum; rather, it results in a table with two ID columns that have rows combining where the newspaper.id matches with the online.id. Otherwise, the online.id column for that row is rendered as NULL. Feedback is appreciated on this comment, I’m new to SQL but I spent 30 minutes scratching my head on this question.

If we are doing a left join of newspaper and online table, we are selecting:
ID which have both newspaper & online
and
ID with only newspaper but not in online

then how is is giving subscribers of print and not online