Replacing nested SELECT

Question

How can I make postgreSQL query like this:

SELECT event_id, user_id FROM public."point" 
WHERE user_id = (SELECT id FROM public."user" 
WHERE email='[email protected]')

with JOINstatement and without nested SELECT statement. Above works but I think it is not optimal. Thanks for your answers.

Answer 1

For your particular case, this should work:

SELECT p.event_id, p.user_id
FROM public."point" p JOIN
     public."user" u
     ON p.user_id = u.id 
WHERE u.email = '[email protected]';

In general, when switching between JOIN and IN, you need to be careful about duplicates. So the general solution would be:

SELECT p.event_id, p.user_id
FROM public."point" p JOIN
     (SELECT DISTINCT u.id
      FROM public."user" u
      WHERE u.email = '[email protected]'
     ) u
     ON p.user_id = u.id ;

But the id is probably already unique in user.