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?
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.
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.
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.
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.
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
This exercise feels somewhat poorly worded, it should say “We want to know which newspaper subscribers aren’t subscribed to the online version” or “Assuming all online users have an active online subscription…” .
While logically it does make sense that all users = news users + online users, and that the only ones without online sub can be found in the news users group. It can also be assumed that not all online users have active subscriptions in which case the way the table is solved is incorrect as they possibly wouldn’t be found in newspaper group. (god just typing this makes my head spin) .
Why do we need “WHERE online.id IS NULL”? Isn’t the purpose of using the LEFT JOIN that we’d exclude these values anyway, as they will not match what’s found in the newspaper table?
I’m confused about the second query, I did it right but I don’t understand the table. What I thought would happen is that all the null values from the second table (and corresponding total column values from first table) would be the only thing that showed up because of the where statement.
For context this is the query;
SELECT *
from newspaper
left join online
on newspaper.id = online.id
where online.id is null;
If someone can explain it to me I’d appreciate it. I did go through other responses and replies but still confused.
) below!
) to up-vote the contribution!