Skip to main content
Bumped by Community user
Tweeted twitter.com/StackDBAs/status/1017014764038508544
add from
Source Link

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

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
LEFT OUTER JOIN calls
   ON calls.user_id = users.id
WHERE calls.created_at <= date

last:

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

DB: PostgreSQL

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

edited tags
Link
user1822
user1822
Source Link

What is better join, in or in with join

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
LEFT OUTER JOIN calls
   ON calls.user_id = users.id
WHERE calls.created_at <= date

last:

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

DB: PostgreSQL