patternsqlMinor

Always check with SELECT before INSERT?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

beforeinsertwithalwaysselectcheck

Problem

If I want to INSERT a row only if the primary key doesn't yet exist, is it more efficient/simpler to execute INSERT directly and ignore the error in the case of duplication, or should i always run SELECT to check if it already exists first?

Solution

You could use WHERE NOT EXISTS to check new values before insert a new record.

INSERT INTO 
(
    field1, field2, field3
)
SELECT value1, value2, value3
FROM dual
WHERE NOT EXISTS (SELECT 1
                  FROM 
                  WHERE  = );

CREATE TABLE foo(id int, v1 int, v2 int);
INSERT INTO foo VALUES (1, 100,100);
INSERT INTO foo VALUES (2, 200, 200);

This record exists and should not be inserted:

INSERT INTO foo (id, v1, v2)
SELECT 1, 101, 101
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=1);

This is a new record:

INSERT INTO foo (id, v1, v2)
SELECT 3, 300, 300
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=3);

The final result:

SELECT * FROM foo;

id | v1 | v2
-: | --: | --:
1 | 100 | 100
2 | 200 | 200
3 | 300 | 300

dbfiddle here

Code Snippets

INSERT INTO <table>
(
    field1, field2, field3
)
SELECT value1, value2, value3
FROM dual
WHERE NOT EXISTS (SELECT 1
                  FROM <table>
                  WHERE <pk fields> = <new values>);

CREATE TABLE foo(id int, v1 int, v2 int);
INSERT INTO foo VALUES (1, 100,100);
INSERT INTO foo VALUES (2, 200, 200);

INSERT INTO foo (id, v1, v2)
SELECT 1, 101, 101
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=1);

INSERT INTO foo (id, v1, v2)
SELECT 3, 300, 300
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=3);

SELECT * FROM foo;

Context

StackExchange Database Administrators Q#178018, answer score: 6

Revisions (0)

No revisions yet.