Using an array variable in WHERE NOT IN -like clause

Question

I have declared an array variable as

select array(select "account_id" from [some where clause]) as negateArray;

Note that I return a single column. Now I need to use it in another WHERE-clause that should mean: do NOT include data if account_id matches any of found in negateArray.

I write something like

select * from [some other where clause] WHERE (account_id NOT IN ANY(negateArray));

but I am getting a syntax error. What would be the correct way to write the condition for PostgreSQL?

select array(select "account_id" from [some where clause]) as negateArray; this not creates variable. — Oto Shavadze
– Oto Shavadze, Commented Dec 28, 2020 at 14:31
@OtoShavadze could you please suggest better syntax for storing the first select into something that I can use in second one? — onkami
– onkami, Commented Dec 28, 2020 at 14:34
Do you use procedure/function for this? or this is pure SQL ? — Oto Shavadze
– Oto Shavadze, Commented Dec 28, 2020 at 14:39
Then no need variable, you can do that using single sql statement, see EDIT in my answer — Oto Shavadze
– Oto Shavadze, Commented Dec 28, 2020 at 14:45

Stefanov.sm · Accepted Answer · 2020-12-28 18:42:46Z

2

In order to let the optimizer do its best - convert the not in condition to left [outer] join. Here is an equivalent SQL-only version rewrite:

   select  t2.*
     from  outer_table t2 
left join  (select account_id from inner_table where [some where clause on inner_table]) t1
       on  t2.account_id = t1.account_id 
    where  [some other where clause on outer_table]
      AND  t1.account_id IS NULL;

t1.account_id IS NULL does the not in job.

Edit
Equivalent but shorter (and probably more efficient) using [inner] join and inverted condition:

select  t2.*
  from  outer_table t2 
  join  (select account_id from inner_table where NOT [some where clause on inner_table]) t1
    on  t2.account_id = t1.account_id 
 where  [some other where clause on outer_table];

edited Dec 28, 2020 at 18:42

answered Dec 28, 2020 at 17:06

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

onkami Over a year ago

yes, this brings down the complexity a great deal. Excellent suggestion. Though I would be extra careful on formulating the condition on the Join in your 2nd example. I suspect that inverted condition edit can be tricky, at least the correct reply I got when using the first example, but it could be just me.

Oto Shavadze · Accepted Answer · 2020-12-28 14:48:26Z

0

If you want to use variables, then you need to do this using procedure/function.

First, define variable as negateArray int[]

Then try:

select array(select "account_id" from [some where clause]) INTO negateArray;

and then:

select * from [some other where clause] WHERE (account_id <> ANY(negateArray));

EDIT

For "pure" SQL, you don't need variable, just use query like this:

select * from [some other where clause] -- your second query
WHERE account_id NOT IN( 
        select "account_id" from [some where clause] -- your first query
);

edited Dec 28, 2020 at 14:48

answered Dec 28, 2020 at 14:38

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

3 Comments

onkami Over a year ago

I am trying to reduce complexity of the query that presently has that NOT IN clause. It shows some huge cost with Nested Loop Anti Join. I could however declare a function I guess.

Oto Shavadze Over a year ago

Is account_id indexed for outer query ?

Alex Yu Over a year ago

@AskarIbragimov NOT IN and <> ANY are equivalent in complexity. Look for adequate indexing to support such queries.

Collectives™ on Stack Overflow

Using an array variable in WHERE NOT IN -like clause

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related