CODEWITHSUNDEEP
sqlleft-joinunion
kk-dev11
kk-dev11

Reputation: 2674

Simplify SQL UNION Query

I have actually three tables. They are categories, users and userCategories. As you can see a user can be assigned to multiple categories through the m-n userCategories table.

I want to select all categories for a specific user. If the category is assigned to that given user, the userFk-column should be the userId (for example 5), otherwise NULL.

I've got a similar result with this query. But is there a way to simplify this query?

select * 
from (
  SELECT `categoryId`, `category`, userFk FROM `category` c
  left join usercategories uc on c.categoryId = uc.catFk
  where userFk = 5
 union
  SELECT `categoryId`, `category`, userFk FROM `category` c
  left join usercategories uc on c.categoryId = uc.catFk
  where userFk != 5 OR userFk is NULL
) as result
group by categoryId

Upvotes: 0

Views: 169

Answers (3)

Swagata
Swagata

Reputation: 622

In the subquery the two parts of the union are the same apart from the where clause. All records of the subquery will fall in either of the two conditions, so isn't

SELECT `categoryId`, `category`, userFk FROM `category` c
left join usercategories uc on c.categoryId = uc.catFk
where userFk = 5
union
SELECT `categoryId`, `category`, userFk FROM `category` c
left join usercategories uc on c.categoryId = uc.catFk
where userFk != 5 OR userFk is NULL

equivalent to just

SELECT `categoryId`, `category`, userFk FROM `category` c
left join usercategories uc on c.categoryId = uc.catFk

Am I missing anything?

Upvotes: 0

Koormo
Koormo

Reputation: 166

If you want the categories of a user you could use:

SELECT `categoryId`, `category`, userFk FROM `category` c
left join usercategories uc on c.categoryId = uc.catFk
where userFk = 5

I suppose userCategories has no null values, a user without categories should not be in userCategories table.

Upvotes: 0

ffflabs
ffflabs

Reputation: 17481

If you put the userid in the left join clause, it would save you the union

SELECT `categoryId`, `category`, userFk 
FROM `category` c
LEFT JOIN usercategories uc on c.categoryId = uc.catFk AND userFk = 5

That way, the only possible outcome of the query would be userFK=5 or NULL, thus saving you the WHERE clause too.

Since the categories your user doesn't have can only show up once in a userFK = NULL tuple, you don't need the group by either, unless there's some repetition I'm not seeing.

Upvotes: 2

Related Questions