Akshay Khetrapal Akshay Khetrapal - 4 months ago 10
SQL Question

Count for distinct values in union of table one and table two using a subquery

Objective:
I would like to find out the number of unique users who have posted or liked the posts in a particular category.

I have two tables

posts
and
likes
with the following fields

posts:

id,
user_id,
category


likes:

id,
user_id,
post_id


Now, I'd like to find distinct user_id values in
posts
based on a particular category
And distinct user_id values in
likes
but also the union of both these result sets.

I am using the following Mysql query to achieve this:

SELECT user_id FROM posts WHERE category = 0 union SELECT user_id from likes where likes.post_id in (select id from posts where category = 0)


I'd like to find out if I am getting the desired result AND if there's a better way to achieve this without having to use the sub-query.

Before you downvote it or something, do mention the reason for the same in the comments.

Answer

You can only speed up the performance by replacing the subquery by INNER JOIN.

SELECT
  user_id
FROM posts
WHERE category = 0

UNION

SELECT
  user_id
FROM likes 
INNER JOIN posts 
ON likes.post_id = posts.id 
WHERE posts.category = 0;

It will be much faster if likes.post_id is indexed properly and posts.id is PK.

Comments