To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: DELETE using an outer join
Date: Thu, 19 Jul 2012 14:43:46 +0200
Lines: 38
Message-ID: <ju8veb$dkn$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666

Hi,

(this is not a real world problem, just something I'm playing around with).

Lately I had some queries of the form:

    select t.*
    from some_table t
    where t.id not in (select some_id from some_other_table);

I could improve the performance of them drastically by changing the NOT NULL into an outer join:

    select t.*
    from some_table t
       left join some_other_table ot on ot.id = t.id
    where ot.id is null;


Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Something like:

    delete from some_table
    where id not in (select min(id)
                      from some_table
                     group by col1, col2
                     having count(*) > 1);

(It's the usual - at least for me - "get rid of duplicates" statement)


The DELETE .. USING seems to only allow inner joins because it requires the join to be done in the WHERE clause.
So I can't think of a way to turn that NOT IN from the DELETE into an outer join with a derived table.

Am I right that this kind of transformation is not possible or am I missing something?

Regards
Thomas