susanhibbard
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all!
ASKER
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?