I have created two tables and populated them:

CREATE TABLE FirstTab (
          id integer, 
          name VARCHAR(10) );
     
INSERT INTO FirstTab VALUES (5,'Pawan'), (6,'Sharlee'), (7,'Krish'),
     (NULL,'Avtaar');
     
CREATE TABLE SecondTab (
         id integer  );
    
INSERT INTO SecondTab VALUES (5), (NULL);

I am trying to find the records from FirstTab that are not in SecondTab (I guess like a custom outer join?)

But this query returns nothing:

SELECT * FROM FirstTab AS ft WHERE ft.id NOT IN ( SELECT id FROM SecondTab );

What is wrong with it? I should get 2 records, not 0.

I'm using Postgres on PgAdmin 4

CodePudding user response：

As well as the comments from @BillKarwin, which explain the behaviour and the fundamental issues with your data, there are alternative ways to express your logic that will work as you desire.

For example;

SELECT
  *
FROM
  FirstTab   F
WHERE
  NOT EXISTS (
    SELECT
      *
    FROM
      SecondTab   S
    WHERE
          S.id           = F.id
      OR (S.id IS NULL AND F.id IS NULL)
  )

That said, pretending NULL equals NULL is a bad idea. NULLs don't equal each other for a reason; NULL isn't a value, it's the absence of a value.

Your query is returning the correct results. Your desire to have your query return two rows is mistaken.

Also, forcing it (such as this answer) introduces an OR condition which has a significant performance hit, dramatically undermining the ability to use indexes effectively.

Don't do it. Just use id 0 or -1 or something. Then you can ensure the value is unique, gain real equality, have shorter cleaner code, and more effectively utilise indexes.

Demo: https://dbfiddle.uk/x1YJ3kug

CodePudding user response：

First Way

SELECT * FROM FirstTab AS ft WHERE ft.id NOT IN ( SELECT id FROM SecondTab where id is not null );

Second Way (Recommended)

select FirstTab.* from FirstTab inner join SecondTab on FirstTab.id <> SecondTab.id