SQL: What is 'WHERE NOT EXISTS' actually checking?

Question

Dong a SQL INSERT INTO ... SELECT FROM ... WHERE NOT EXISTS

What is that clause actually checking? I hope to be inserting the results of col1 from a table into a different table's 'col1', where it isn't already in that table (i.e. col1 is a primary key)

INSERT INTO <table> (<col1>)
SELECT DISTINCT N.<col1>
FROM N
WHERE NOT EXISTS (
    SELECT 1
    FROM <table> C
    WHERE
        N.<col1> = C.<col1> 
)

it seems to me that the SELECT statement in the NOT EXISTS clause will either return a row (or more than one row), and that NOT EXISTS will look for either 0 rows or more than zero rows.

is that correct?

Answer 1

Just to add to this, the Not Exist clause can be used for selects or updates in your WHERE clause to EXCLUDE records based on what you define in the supporting query.

In your case MERGE is better, but in some cases, like the example below it is also useful:

If Not Exists ( Select 1 From dbo.Activities Where ActivityId = 107 AND Activity = 'Facility Updated' )
  Begin
    Insert Into Activities (ActivityId, Activity, CreateDt_GMT, CreatedBy, TimeZoneOffsetMins, ActionTypeId, IsTracked, IgnoreChildren, ProcessExtraInfomation, IgnoreNotChangedData, ApplicationID)
    Values(107, 'Blah', GetDate(), 'SYSTEM', -300, 3, 1, 0, 1, 1, 1 )
  End