How to select correctly in SQL?-CodePudding

I have a table containing a list of image related tags.

See the details here in this screenshot:

enter image description here

What I tried:

SELECT a.* 
FROM wallpaper_tag as a, wallpaper_tag as b 
WHERE a.tag = 1 
  AND b.tag = 2 
  AND a.wallpaper = b.wallpaper 
ORDER BY wallpaper 
LIMIT 10000

This SQL query works fine for me. But is there a better option?

I want to get a list of wallpapers if two tags match at the same time.

CodePudding user response：

I'd use an explicit join:

SELECT a.*
FROM wallpaper_tag as a
INNER JOIN wallpaper_tag as b
ON a.wallpaper = b.wallpaper
WHERE a.tag = 1 AND b.tag = 2
ORDER BY a.wallpaper
LIMIT 10000

I prefer this syntax as it makes it clearer what tables are joined and how they are joined.

CodePudding user response：

If what you actually want is all the wallpapers with tags 1 and 2, you can do it with aggregation:

SELECT wallpaper 
FROM wallpaper_tag
WHERE tag IN (1, 2)
GROUP BY wallpaper 
HAVING COUNT(DISTINCT tag) = 2
ORDER BY wallpaper 
LIMIT 10000

If the combination of wallpaper and tag is unique, the HAVING clause may be simplified to just:

HAVING COUNT(*) = 2