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?  
susanhibbardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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.
0
cmgarnettCommented:
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.
0
LowfatspreadCommented:
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...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

susanhibbardAuthor 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?
0
LowfatspreadCommented:
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.

0
susanhibbardAuthor Commented:
Thanks all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.