3

I have these tables:

calls: rows [ 'created_at', 'user_id']

users: rows ['id', 'username']

And I need to get all user without calls from date: How can I get it? Or what way is better and why?

I know three ways:

first:

SELECT id 
FROM users
WHERE id NOT IN (SELECT user_id FROM calls where calls.created_at >= date)

second(I think it is not right, but I am not sure):

SELECT id
FROM users
LEFT OUTER JOIN calls
   ON calls.user_id = users.id
WHERE calls.created_at >= date AND calls.user_id ISNULL

last:

SELECT id
FROM users
WHERE id NOT IN (
   SELECT user_id
   FROM calls
   INNER JOIN users AS call_users
      ON call_users.id = users.id
   WHERE calls.created_at >= date

DB: PostgreSQL

5
  • 4
    Better according to what criteria?
    – mustaccio
    Jul 10 '18 at 19:17
  • 1
    Those queries will return different results as you are using a >= for two of them and a <= for the third. That will mean users created on that date in the where clause will not br in two of the result sets but will be in the third.
    – Joe W
    Jul 10 '18 at 19:37
  • If you want to know which one is most efficient, check the execution plan Jul 10 '18 at 19:49
  • Not only your "negation" of >= is wrong, that'll be < not <=, the first query will also include users, that are not in calls at all (or with a creation time of null), whereas the second will only select users (I just guess that's the target for all three as the second and third also miss a FROM (the third even twice)), that are in calls with a not null creation time matching the inequality.
    – sticky bit
    Jul 10 '18 at 22:02
  • The join in the third query makes not much sense to me. It might reduce the set to the NOT IN a little if there are users, that are in calls but not in users but those won't be selected from users in the outer query either, as they don't exist. And I guess you might be best of with a NOT EXISTS after all.
    – sticky bit
    Jul 10 '18 at 22:02
1

SQL is specifically meant to express simple queries more or less like you would ask them as a question in English. For instance, your question is like:

"What are all the id from users that have no associated call in calls with a created_date greater or equal than date $1 ?"

SQL translation:

SELECT id FROM users
  WHERE NOT EXISTS (
    SELECT 1 FROM calls WHERE call.user_id=users.id AND created_date >= $1
  );

It's a rather direct translation except maybe for SELECTing 1, which is an arbitrary value.

There is no reason to assume that PostgreSQL would produce a non-optimal execution plan for this query. The optimizer is certainly smart enough for that kind of queries. In general if there's a straightforward way to express your query, there's no reason to pretend to be smarter than the optimizer.

When you doubt that the optimizer found the best possible plan (say for more complex queries), you can start from the output of EXPLAIN to figure out if/why it's not optimal and typically try to improve the query by trial and error.

If you want to compare how different variants of the same query are planned, compare the outputs of EXPLAIN ANALYZE of the queries.

https://wiki.postgresql.org/wiki/Slow_Query_Questions is a good start too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.