How to show only the data that is missing in the relationship?

Question

PostgreSQL

There is a problem, I create a custom module with news and notification of new news, respectively. I work with PostgreSQL database.
Created 2 tables:

news
id - AI Int
title	- Short String
description - Text
shortDescription - String
status - Int (Enum 1\2\3)
createdAt - Timestamp
updatedAt - Timestamp
groupId - Int
teamId - Int
blockId - Int
lang - Short String

And the second

id - AI Int
newsId - Index News.id
userId - Index User.id
readedAt - Timestamp

Having created 10 news under different conditions, I make a request to receive:

SELECT 
"news"."id" AS "news_id", 
"news"."title" AS "news_title", 
"news"."description" AS "news_description", 
"news"."shortDescription" AS "news_shortDescription", 
"news"."status" AS "news_status", 
"news"."createdAt" AS "news_createdAt", 
"news"."updatedAt" AS "news_updatedAt", 
"news"."teamId" AS "news_teamId", 
"news"."groupId" AS "news_groupId", 
"news"."blockId" AS "news_blockId", 
"news"."lang" AS "news_lang" 
FROM 
"news" "news" 
LEFT JOIN 
"readed_news" "rn" 
ON 
"rn"."userId" = 5 
WHERE 
"news"."id" != "rn"."newsId" AND 
"news"."status" = 2 AND 
"news"."lang" = 'ru' AND 
"news"."blockId" = 0 AND 
"news"."groupId" = 0 AND 
"news"."teamId" = 0

But the result of this query is all the news that have a connection with the second table and that do not exist, that have the value status = 2, other conditions work fine, except for checking the absence of the connection itself with the second table

. Exports to understand the situation:

spoiler

INSERT INTO "news" ("id", "title", "description", "shortDescription", "status", "createdAt", "updatedAt", "groupId", "teamId", "blockId", "lang") VALUES
(1,	'Forward Security Officer',	'Central',	'Product',	2,	'2020-11-30 12:27:18.047',	'2020-11-30 12:27:18.047',	0,	0,	0,	'ru'),
(4,	'National Infrastructure Representative',	'Investor',	'Investor',	1,	'2020-11-30 13:59:11.584078',	'2020-11-30 13:59:11.584078',	1,	0,	0,	'ru'),
(3,	'Customer Accountability Administrator',	'Human',	'Central',	2,	'2020-11-30 13:57:38.231',	'2020-11-30 13:57:38.231',	0,	1,	0,	'ru'),
(5,	'International Intranet Manager',	'Global',	'National',	2,	'2020-11-30 14:01:30.907',	'2020-11-30 14:01:30.907',	0,	0,	1	'ru'),
(6,	'Dynamic Infrastructure Planner',	'National',	'Principal',	2,	'2020-11-30 14:01:45.809',	'2020-11-30 14:01:45.809',	0,	0,	0,	'ru'),
(7,	'Forward Paradigm Administrator',	'District',	'Dynamic',	2,	'2020-11-30 14:02:01.172',	'2020-11-30 14:02:01.172',	0,	0,	0,	'ru'),
(8,	'Forward Assurance Orchestrator',	'Regional',	'Dynamic',	2,	'2020-11-30 14:02:13.705',	'2020-11-30 14:02:13.705',	0,	0,	0,	'ru'),
(9,	'National Integration Director',	'Customer',	'Investor',	2,	'2020-11-30 14:02:25.933',	'2020-11-30 14:02:25.933',	0,	0,	0,	'ru');

INSERT INTO "readed_news" ("id", "newsId", "userId", "readedAt") VALUES
(1,	1,	5,	'2020-11-30 12:39:40.445662'),
(2,	2,	5,	'2020-11-30 13:46:27.55037');

Answer 1

LEFT JOIN is done like this:

...
LEFT JOIN "readed_news" "rn" 
ON ("rn"."newsId" = "news"."id" )
...

Then, if there is no news in the readed_news link table, the rn.newsId column will be NULL (can be added to WHERE: AND rn.newsId IS NULL).
Or do without the join and do in WHERE:

...
AND id NOT IN (
  SELECT newsId FROM readed_news WHERE userId = 5
)

(under the hood, of course, there will still be a join).

Answer 2

Use not in or not exists
if you don't understand - write, I'll add select