CODEWITHSUNDEEP
sqljoinin-clause
Meenakshi
Meenakshi

Reputation: 1

Can I not use joins with in clause in SQL

Assume you're given two tables containing data about Facebook Pages and their respective likes (as in "Like a Facebook Page").

Write a query to return the IDs of the Facebook pages that have zero likes. The output should be sorted in ascending order based on the page IDs.

Pages table:

page_id page_name
--------------------------------
20001   SQL Solutions
20045   Brain Exercises
20701   Tips for Data Analysts

page_likes example input:

user_id page_id liked_date
-------------------------------------
  111   20001   04/08/2022 00:00:00
  121   20045   03/12/2022 00:00:00
  156   20001   07/25/2022 00:00:00

Query:

SELECT p.page_id 
FROM pages AS p 
LEFT JOIN page_likes AS l ON p.page_id = l.page_id
WHERE p.page_id NOT IN (l.page_id);

Why am I getting output as no data to display?

Upvotes: 0

Views: 106

Answers (4)

Tushar Sharma
Tushar Sharma

Reputation: 9

The reason you're not getting any data is because of the way your query is structured. You're trying to find pages with zero likes, but the combination of a left join and a "not in" clause isn't working as expected. Instead, try using a subquery to filter out the pages with likes, and you should get the desired result.

One of the subquery I thought:

SELECT p.page_id 
FROM pages AS p 
LEFT JOIN (
    SELECT DISTINCT page_id
    FROM page_likes
) AS l ON p.page_id = l.page_id
WHERE l.page_id IS NULL;

Upvotes: 0

Jon Waterhouse
Jon Waterhouse

Reputation: 96

You are getting nothing to display because you are asking for rows where your two tables join on the page_id (your join condition) but where the page_ID is not the same in both tables -- your where clause. They can't both be true!

The second answer will work for you, as would

SELECT P.PAGE_ID
FROM PAGES AS P
WHERE p.page_id not in
(
     SELECT pl.page_id FROM page_likes 
)
ORDER BY P.PAGE_ID ASC

You don't actually want to join pages and page_likes, becasue then you will get one row per page-like, so you would get two rows for page_id 20001

Upvotes: 0

gbjbaanb
gbjbaanb

Reputation: 52659

Hint: remove the where clause and see what data you get back.

A left join returns data that exists in both tables. So obviously you will get no data in the result by looking at data that's not in the result set!

So what you might want is an outer join, this returns a dataset that contains all the joined data, but some columns will be null. You can then query the resaults that are null for the rows you want.

Upvotes: 0

Sergey
Sergey

Reputation: 5217

SELECT P.PAGE_ID
FROM PAGES AS P
WHERE NOT EXISTS
(
     SELECT 1 FROM page_likes AS PL WHERE PL.PAGE_ID=P.PAGE_ID
)
ORDER BY P.PAGE_ID ASC

Upvotes: 2

Related Questions