How do I select all the lifters who have never done running and swimming?

activity_log

SELECT a.user 
FROM activity_log a
WHERE a.activity = 'lifting' AND a.user NOT IN (
 SELECT DISTINCT b.user 
 FROM activity_log b 
 WHERE b.activity IN ('running', 'swimming'))

so far here is my take but is there a better way for this?

CodePudding user response：

Do a GROUP BY. Use the HAVING clause to avoid runners and swimmers.

SELECT a.user 
FROM activity_log a
GROUP BY a.user
HAVING SUM(when A.activity IN ('running', 'swimming') then 1 else 0 end) = 0

Alternatively, use EXCEPT:

SELECT a.user FROM activity_log a
EXCEPT
SELECT a.user FROM activity_log a WHERE A.activity IN ('running', 'swimming')

(The GROUP BY query will probably perform better.)

CodePudding user response：

Of course there is a bit faster way to do it.

You can use JOIN instead of WHERE as JOINS are faster than WHERE. Here is how you query may look:

SELECT a.user 
FROM activity_log a
LEFT JOIN 
(
 SELECT DISTINCT b.user 
 FROM activity_log b 
 WHERE b.activity IN ('running', 'swimming')
) as c
ON a.user=c.user
WHERE a.activity = 'lifting' AND c.user IS NULL 

As you might know, while using LEFT JOIN, we get NULL for user if they don't have activity as running or swimming. Hence taking IS NULL to filter.