Search code examples
sqlsqlitesqlalchemysubquery

subquery equivalent for a join solution

We have two tables, User, Emails. They have relationship on User.id == Emails.user_id. If we are interested in those users who have more than one different emails, using join we can accomplish by this JOIN:

SELECT * FROM User 
  JOIN Emails as EM1 ON User.id = Emails.user_id
  JOIN Emails as EM2 ON User.id = Emails.user_id
   Where EM1.address_line != EM2.address_line;

How can we achieve the same result using subquery?

Solution

  • You can use a correlated subquery:

    select u.*
from users u
where (select count(*)
       from emails e
       where e.user_id = u.id
      ) >= 2;