Conditional table update. Left join vs NOT IN

Started by Allan Kamauover 15 years ago1 messagesgeneral
Jump to latest
#1Allan Kamau
kamauallan@gmail.com

Greetings,
I have a table which I would like to (conditionally and efficiently)
populate if the would be new records do not already exist in that
table.
So far I see that I may use either a left join with a WHERE right
table key field is NULL. Or I could use a sub query and a NOT IN
clause. Perhaps there another better way of doing this.
Which of these options will more likely be more efficient than the other(s)?

1)
INSERT INTO foo
(key_field1,field2)
SELECT
key_fieldA,fieldB
FROM foo2 a
LEFT JOIN
foo b
ON
b.key_fieldA=a.key_field1
WHERE
b.key_fieldA IS NULL
;

2)
INSERT INTO foo
(key_field1,field2)
SELECT
key_fieldA,fieldB
FROM foo2 a
WHERE
a.key_fieldA NOT IN
(
SELECT
a.key_field1
FROM
foo a
)
;

Allan.