We help IT Professionals succeed at work.

question about use of null in SQL statement to insert rows

Can someone explain to me what this sql statement does and why it works?

insert into customers
(customer,
name)
select CustID,CustName from isc..tblArCust c
LEFT JOIN customers cu ON cu.customer = c.CustId
WHERE cu.customer is null

As I understand it, it inserts rows from the table tblArCust in database isc to rows in the customer table in the current database.  Somehow, the where clause causes only rows that do not already exist in the current database to be appended.  Why is that?  
Comment
Watch Question

The combination of the join type and the Where clause is limiting the records that get appended to those that do not already exist in the customer table.

This looks like a perfectly normal situaton.  You would not normally want duplicate records for the same customer in a Customers table.
The left join means that you get all the records from table tblArCust and if the are matching records in the Customer table, you will get information from there too. If the part of the generated row that comes from the Customer table contains a null id, you know that there was no corresponding Customer record and so you can safely select the tblArCustomer details and use them to create a new Customer record.
Top Expert 2011
Commented:
its basically doing this

insert into customers
(customer,
name)
select CustID,CustName from isc..tblArCust c
where not exists (select customer from customers as x ON x.customer = c.CustId)


the left join allow for the left hand side table to not actually exist which is represented as the columns from the
left hand side containing Null... the where clause then restrict the set of data to be inserted to those rows without a corresponding match...

which syntax is more efficient will depend on the statistics and sizes of the tables involved and whether customer is UNIQUE or not on the customers table...

Author

Commented:
Thanks all for helping clarify in my mind how/why this works.  I am fairly inexperienced in SQL and was only familiar with using not exists for this scenario.  

I'm not sure why I didn't get that the join created data with null values in the rows that did not exist in the left table but now I see it.  It makes perfect sense.

Peter57r, yes it is exactly the normal situation you imagine, I'm just learning why it works.

Lowfatspread, efficiency is important in this case, how would you determine which is better in  a given situation?
Top Expert 2011
Commented:
personally i tend to usually go with the not exists code myself  but as with every case ... you can only look at the actual plan produced and based on you knowledge of the likely growth factors decide if its ok, or needs to be tweaked in a certain direction.

Author

Commented:
Thanks all!