SQL query: how to select those who have authorization ONLY from the application?

Question

SQL

There is a table enter in which all user authorizations are written. One of the columns in it is enter_source, which is written in 4 options (landing, web. ios, android).

How to select only those users who had authorization from the application, but not from the web. When using NOT IN () AND IN () and JOIN, exclude those who still had authorization and cannot be accessed from the web.

Answer 1

SELECT DISTINCT user_id
FROM enter t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM enter t2
                   WHERE enter_source = 'web' 
                     AND t1.user_id = t2.user_id )
  AND EXISTS ( SELECT NULL
               FROM enter t3
               WHERE enter_source IN ('ios', 'android')
                 AND t1.user_id = t3.user_id );

Answer 2

SELECT *
  FROM `users`
  WHERE `id` IN (
    SELECT `user_id`
      FROM `enter`
      WHERE `enter_source` = 'ios' OR `enter_source` = 'android'
  ) AND `id` NOT IN (
    SELECT `user_id`
      FROM `enter`
      WHERE `enter_source` = 'landing' OR `enter_source` = 'web'
  )

Answer 3

select user_id
from enter
group by user_id
having sum(case when enter_source in ('ios', 'android') then 1 else 0 end) > 0
  and sum(case when enter_source in ('web', 'landing') then 1 else 0 end) = 0

select
    app.user_id
from (
    select distinct user_id from enter where enter_source in ('ios', 'android')
) app
left outer join (
    select distinct user_id from enter where enter_source in ('web', 'landing')
) web on app.user_id = web.user_id
where web.user_id is null

Just two more versions of the query with slightly different execution plans.