How to select records from a table whose id's are not in another table?

Question

SQL

The first table NEWS has two fields id and user_id the second table has two fields (this is a composite PrymaryKey) news_id and user_id
How to select all records from the first table, provided that the second table does not have a composite key (news_id and user_id)

Found only this

SELECT *
  FROM 'users'
 WHERE 'users'.'user_id' NOT IN(
     SELECT 'member_id' 
       FROM 'members')

But this only works if there is a check for one id, how to do this with two id?

Answer 1

You can use the WHERE NOT EXISTS (...) construct :

select *
from news
where not exists (
  select 1 from user_news 
  	where user_news.news_id = news.id and  user_news.user_id = news.user_id
);

SQL fiddle (MariaDB)

Answer 2

LEFT JOIN, WHERE IS NULL