From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@lists.postgresql.org,  Pavel Stehule <pavel.stehule@gmail.com>
Subject: Re: select where not in () fails
In-Reply-To: <201809211620.16084.gary.stainburn@ringways.co.uk> (Gary
	Stainburn's message of "Fri, 21 Sep 2018 16:20:16 +0100")
Message-ID: <871s9lif94.fsf@news-spur.riddles.org.uk>
References: <201809211608.41086.gary.stainburn@ringways.co.uk>
	<CAFj8pRDz6x=59Y118YnKhMDvCGedt+Soj5-0dDFQMHb4AwMiCw@mail.gmail.com>
	<201809211620.16084.gary.stainburn@ringways.co.uk>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Sat, 22 Sep 2018 19:13:03 +0100
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "Gary" == Gary Stainburn <gary.stainburn@ringways.co.uk> writes:

 Gary> As I said in my description, some values will be NULL. I just
 Gary> thought that these would not be included in the select. I did not
 Gary> think that it would stop the subselect from working

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN

 Gary> users=# select count(u_id) from users where u_id not in (select
 Gary> distinct emp_u_id from employees where emp_u_id is not null);

Never use DISTINCT inside IN; the IN already implies it.

Always rewrite NOT IN (select ...) to use NOT EXISTS instead, like so:

select count(u_id) from users u
 where not exists (select 1 from employees e where u.u_id=e.emp_u_id);

(and always qualify every column reference in the query, especially when
using IN)

-- 
Andrew (irc:RhodiumToad)