can this be written with an outer join

Question

The requirement is to copy rows from Table B into Table A. Only rows with an id that doesn't already exist, need to be copied over:

INSERT INTO A(id, x, y)
SELECT id, x, y
FROM B b
WHERE b.id IS NOT IN (SELECT id FROM A WHERE x='t');
                                       ^^^^^^^^^^^

Now, I was trying to write this with an outer join to compare the explain paths, but I can't write this (efficiently at least).

Note that the sql highlighted with ^'s make this tricky.

Answer 1

try

INSERT INTO A(id, x, y)
SELECT id, x, y
FROM TableB b
  Left Join TableA a
     On a.Id = b.Id
        And a.x = 't'
Where a.Id Is Null

But I prefer the subquery representation as I think it more clearly expresses what you are doing.

Answer 2

Your query will perform better than the query with outer join.

I guess the following query will do the job:

INSERT INTO A(id, x, y)
SELECT id, x, y
FROM B b
LEFT JOIN A a
ON b.id = a.id AND NOT a.x='t'

Answer 3

Why are you not happy with what you have? If you check your explain plan, I promise you it says that an anti-join is performed, if the optimizer thinks that is the most efficient way (which it most likely will).

For everyone who reads this: SQL is not what actually is executed. SQL is a way of telling the database what you want, not what to do. All decent databases will be able to treat NOT EXISTS and NOT IN as equal (when they are, ie. there are no null values) and perform an anti-join. The trick with an outer join and an IS NULL condition doesn't work on SQL Server, though (SQL Server is not clever enough to transform it to an antijoin).