JIYIK CN >

NOT INThe inverts the result of NOTsimply using INthe operator. NOT INThe right side of the operator must have a subquery in which multiple columns are returned to check whether the expression matches the data.

NOT INTends to return true if the expression is not found in the returned subquery data .

NOT INLet us continue and try to understand some common issues and problems faced by users while using standard SQL in PostgreSQL .

NOT INUsing the operator with subqueries in PostgreSQL

In PostgreSQL, if you use NOT INto ensure that no expression matches a particular set of data, you must ensure that there are no NULL values ​​in the returned subquery data.

What does this mean?

Let us go ahead and try to understand this with the help of an example. We will create a table with two columns HORSE;ID颜色

CREATE TABLE horse (
    ID int PRIMARY KEY,
    Colour TEXT
);

Now let's insert some values ​​as well.

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white');

Let's go ahead and RIDERcreate another table for .

CREATE TABLE rider (
    ID int PRIMARY KEY,
    horse_id int
);

You can run any of the code given above in PGADMINand .PSQL

Let's insert some values ​​into both tables:

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white');

INSERT INTO rider values (1, 1), (2, 2), (3, 4)

Here you can see that RIDERthe id in 3has horse 4which HORSEdoes not exist in the table. We do this to ensure that it is used in our example NOT IN.

Suppose we want to delete this from the RIDER table RIDER 3. How do we do that?

select * from rider
where horse_id not in (select id from horse)

Output:

Now, HORSEwhat if we also have some null values ​​in the table instead of clean values. Let us modify the statement HORSEof the table INSERT.

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white'), (NULL, NULL);

As a side note, when inserting NULL values ​​in primary key columns, remove the primary key constraint from the table to allow inserting NULL.

Here, we NULL HORSEhave an NULL Color, so when we run a query for like above NOT IN, we get the following:

Output:

So what just happened? It shouldn't be returned ID 4because HORSEit doesn't exist in the table?

Well, let's understand NOT INhow the operator works. NOT INThe operator uses ANDthe operator. It will return true if all the rows you are searching for return true.

So something like this could be substituted NOT IN:

NOT IN (ROW 1) AND NOT IN (ROW 2) AND NOT IN (ROW 3) .....

In our example, NOT INreturns true for the first three data sets, and it will not return any value for NULL columns, as the PostgreSQL documentation quotes:

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

Returning NULL from NOT INwill cause all other true values ​​to be false, so our table returns nothing.

How do we solve this problem?

The first method is to prevent any NULL from being inserted into the table. However, this is also useless if we have already created the table in the database and now want to run a query to get the data.

Therefore, we must look for other solutions to effectively solve this problem.

Use NOT EXISTSthe operator as a better alternative

Let's use the following query:

select horse_id from rider r
where not exists
(select id from horse h
where h.id = r.horse_id)

This tends to eventually 4return a value to us even in the presence of null values, and is an effective strategy. Let's see how it works.

The clause returns true if the subquery returns anything, that is, any single row EXISTS, and false otherwise. Therefore, when we want to find a missing HORSE, we tend HORSEto return a value from the table that is equal to the value RIDERin the table .IDs

The subquery returns multiple rows, and EXISTSbecomes true, making NOT EXISTS FALSE.

Ultimately, our final query RIDERselects from HORSE_IDwhere is not equal to HORSEthe provided in the table IDs. In our case, that is 4, and so our query works perfectly.

However, it incurs a performance penalty NOT EXISTSwhen used with the operator.NOT IN

select horse_id from rider
full join horse on rider.horse_id = horse.ID
where horse.ID is null

Output:

So it returns a NULL and the value we didn't find; 4. How?

IDIt also tends to return dissimilar rows when we do a full join on the condition that two are similar. It will return HORSEthe set from (NULL, NULL)and from RIDERsince (3, 4)they are both mismatched.

Therefore, we can take advantage of this and write a NULL condition at the end to return these non-matching rows.

When we write Horse.ID is NULL, it will select from the riders that are NULL HORSE_ID. In this case, the first set (NULL, NULL)is included; (3,4)so is the set . Why?

This set does not contain NULL, but it also does not match, so our __register__ FULL JOINalso sets NULL values ​​in its return table.

So we get this value output to us as a result.

Today we looked at implementing the operator for values ​​containing NULL NOT IN. We hope this helps you and expands your knowledge base.