Find Records Where Join Doesn't Exist

Find records where join doesn't exist

Use an EXISTS expression:

WHERE NOT EXISTS (
   SELECT FROM votes v  -- SELECT list can be empty
   WHERE  v.some_id = base_table.some_id
   AND    v.user_id = ?
   )

The difference

... between NOT EXISTS() (Ⓔ) and NOT IN() (Ⓘ) is twofold:

Performance
Ⓔ is generally faster. It stops processing the subquery as soon as the first match is found. The manual:

The subquery will generally only be executed long enough to determine
whether at least one row is returned, not all the way to completion.
Ⓘ can also be optimized by the query planner, but to a lesser extent since NULL handling makes it more complex.
Correctness
If one of the resulting values in the subquery expression is NULL, the result of Ⓘ is NULL, while common logic would expect TRUE - and Ⓔ will return TRUE. The manual:

If all the per-row results are either unequal or null, with at least
one null, then the result of NOT IN is null.

Essentially, (NOT) EXISTS is the better choice in most cases.

Example

Your query can look like this:

SELECT *
FROM   questions q
WHERE  NOT EXISTS (
    SELECT FROM votes v 
    WHERE  v.question_id = q.id
    AND    v.user_id = ?
    );

Do not join to votes in the base query. That would void the effort.

Besides NOT EXISTS and NOT IN there are additional syntax options with LEFT JOIN / IS NULL and EXCEPT. See:

Select rows which are not present in other table

How do I find records that are not joined?

select * from a where id not in (select a_id from b)

Or like some other people on this thread says:

select a.* from a
left outer join b on a.id = b.a_id
where b.a_id is null

SQL Server join where not exist on other table

The simplest I can think of:

select * from Service
where Id not in (
    select ServiceId
    from AssetService 
    where AssetId = 1);

SQLFiddle link

I don't think it's possible using inner join, because that would only retrieve records that match some criteria and you are looking for records that do not match.

It is, however, possible to do it with left join as Ctznkane525 shows in his answer.

Edit

As jarlh pointed out in the comments, not in might lead to surprising results when there are nulls in the subquery. So, here is the not exists version:

select Id, Name
from Service s
where not exists (
    select *
    from AssetService a
    where AssetId = 1
    and ServiceId = s.Id);

SQLFiddle link

SQL Inner join where record does not exist

As written, change your INNER JOIN to a RIGHT OUTER JOIN. But I prefer LEFT JOIN myself; it's easier to follow. I.e.:

SELECT departments.deptID
     , departments.deptName
     , COUNT(departmentMemberships.deptID) AS employeeCount
FROM departments
LEFT OUTER JOIN departmentMemberships
ON departmentMemberships.deptID = departments.deptID
WHERE departments.companyID = 1
GROUP BY departments.deptID, departments.deptName
ORDER BY departments.deptName ASC

SELECT JOIN WHERE a record does not exist

Just a left join would do

    SELECT t1.name as name
     FROM table1 as t1
LEFT JOIN table2 as t2 
       ON t1.id = t2.parent_id
      AND t2.key = 'active'
    WHERE t2.key IS NULL

Showing results from Mysql Query that don't exist in LEFT JOIN

select u.*
from users u
left join interested i
  on  i.p_interest_target = u.id
  and i.p_interest_source = 1204
where i.p_interest_target is null

The JOIN will search for matches of the user ID in the p_interest_target column, but only with rows, where p_interest_source = 1204 (Note that this condition must be in the ON clause). With where i.p_interest_target is null only unmatched rows from the users table are returned.

You can also get the same with a NOT EXISTS subquery:

select u.*
from users u
where not exists (
  select *
  from interested i
  where i.p_interest_target = u.id
    and i.p_interest_source = 1204
)

db-fiddle demo

Find records from one table which don't exist in another

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

This is the shortest statement, and may be quickest if your phone book is very short:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatively (thanks to Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

or (thanks to WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

rails get record that is not exist on right join

You could try from different angle:

date = Date.parse('2021-01-24')
user_ids = Email.where(date: date).pluck(:user_id)
User.where.not(id: user_ids)

Active Record where join table record doesn't exist

That should do it. Returns all games that have not been marked by the current user.

Game.where('id not in (select game_id from marked_games where user_id = ?)', current_user.id)

Find Records Where Join Doesn't Exist