Question 114578 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query returns nothing when looking for records that are not in another table

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    First Way

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

    OR

    Second Way (Recommended)

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search