not exists vs except - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

not exists vs except

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 01:44:59

from not exists and except clause which is better and faster.... tell me with an example

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 02:07:30

from "not exists" ,"left join" and "except"

clause which is better and faster....

tell me with an example

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 02:28:09

from "not exists" ,"left join" , "except" and "not in"

clause which is better and faster....

tell me with an example

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 08:41:25

there is no "better" or "faster" in general. it all depends on the tables involved. give some specific instances and we can talk.

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 08:47:36

insert into tblSSAppsEmpMasterDiffer
( PersonId,
EID,
JobDescription,
BusinessTitle,
ProfitCenterCode,
CostCenterCode,
LegacyLocationId,
Grade,
DateOfHire,
DateOfTermination,
SupervisorEID,
SupervisorPersonId,
SalaryPlannerEID,
SalaryPlannerPersonId,
HrContactEID,
HrContactPersonId,
NamePrefix,
NameLast,
NameFirst,
NameMiddle,
ResidenceAddress1,
ResidenceAddress2,
ResidenceAddress3,
ResidenceAddress4,
--ResidenceCityId,
--ResidenceStateId,
ResidencePostalCode,
PermanentAddress1,
PermanentAddress2,
PermanentAddress3,
PermanentAddress4,
--PermanentCityId,
--PermanentStateId,
PermanentPostalCode,
PhoneMobile,
PhoneOffice,
PhoneResidence,
PhoneExtension,
EmailAddressOffice,
EmailAddressAlternate,
Sex,
BloodGroup,
DateOfBirth,
PAN,
LegacyEmpNo,
LegacyFunction,
HLoB,
HComments,
BankCode,
BankAccountNo,
CurrentLocationId,
CurrentBuildingId,
CurrentFloorId,
--CurrentSeatId,
CurrentAssetId,
EmployerId,
PassportNumber,
ExEmployer,
PastExperience,
Qualification1,
YearOfPassing1,
Qualification2,
YearOfPassing2,
Qualification3,
YearOfPassing3,
SBG,
SBU,
SBE,
CostCenterFunction,
BankIFSC_Code,
OrgEntity,
DateOfRehire)
select personid,
eid,
job_description,
job_business_title,
job_bu_id,
job_dept_id,
job_location_id,
job_grade,
date_of_hire,
date_of_termination,
supervisor_eid,
supervisor_personid,
salary_planner_eid,
salary_planner_personid,
hr_contact_eid,
hr_contact_personid,
name_prefix,
name_last,
name_first,
name_middle,
residence_address1,
residence_address2,
residence_address3,
residence_address4,
--residence_city,
--residence_state,
residence_postal,
permanent_address1,
permanent_address2,
permanent_address3,
permanent_address4,
--permanent_city,
--permanent_state,
permanent_postal,
phone_mobile,
phone_office,
phone_residence,
phone_extension,
email_address_office,
email_address_alternate,
sex,
blood_group,
date_of_birth,
pan,
legacy_empno,
legacy_function,
h_lob,
h_comments,
bank_id,
bank_account,
cast(current_location_id as int),
current_building_id,
current_floor_id,
--current_seat_id,
current_asset_id,
employerid,
passport_number,
ex_employer,
past_experience,
qualification1,
year_of_passing1,
qualification2,
year_of_passing2,
qualification3,
year_of_passing3,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8
from tblSSAppsEmpMasterTemp emt /* instead of Except caluse not exist is used here */
where not exists ( select PersonId
from custom.dbo.tblSSAppsEmpMaster ema
where ema.PersonId = emt.personid)

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 08:48:35

USE [CustomDataSync]
GO
/****** Object: StoredProcedure [dbo].[uspOracleToSql_UpdateEmpMaster] Script Date: 10/08/2014 18:18:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************
Procedure: uspOracleToSql_UpdateEmpMaster
Description: Update EmpMaster data from Oracle To SQLServer
*******************************************************************************
Change History
*******************************************************************************
Author Date Version Description
CA Keer 29-Jul-2014 1.0 Original
CA Keer 16-Sep-2014 1.1 Changes for running proc from CustomDataSync
******************************************************************************/
ALTER PROCEDURE [dbo].[uspOracleToSql_UpdateEmpMaster]
AS

declare @v_LinkedServer varchar(100)
declare @v_SqlStatement varchar(max)
declare @v_SqlQuery varchar(max)
declare @v_IsError int
declare @v_InTran int
declare @v_Inserted int
declare @v_Updated int

BEGIN

/* Test Code
declare @v_Return int
exec @v_Return = uspOracleToSql_UpdateEmpMaster
print '@v_Return: ' + convert(varchar,@v_Return)
*/

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

set @v_IsError = 0
set @v_InTran = 0
set @v_LinkedServer = (select CH_Value
from custom.dbo.tblSSAppsConfigs
where config = 'LinkedProdTechteam'
and active = 1)/*'linked_testprod_custom'*/

set @v_SqlQuery = 'select * from custom.hail_ba_ess_emp_master'
set @v_SqlStatement = 'select * from openquery(' + @v_LinkedServer + ',''' + @v_SqlQuery + ''')'
--print @v_SqlStatement

truncate table tblSSAppsEmpMasterTemp
truncate table tblSSAppsEmpMasterDiffer

begin tran
set @v_InTran = 1

insert into tblSSAppsEmpMasterTemp exec(@v_SqlStatement)

if (@@error <> 0)
begin
set @v_IsError = 1
goto ExitRoutine
end
/* */
insert into tblSSAppsEmpMasterDiffer
( PersonId,
EID,
JobDescription,
BusinessTitle,
ProfitCenterCode,
CostCenterCode,
LegacyLocationId,
Grade,
DateOfHire,
DateOfTermination,
SupervisorEID,
SupervisorPersonId,
SalaryPlannerEID,
SalaryPlannerPersonId,
HrContactEID,
HrContactPersonId,
NamePrefix,
NameLast,
NameFirst,
NameMiddle,
ResidenceAddress1,
ResidenceAddress2,
ResidenceAddress3,
ResidenceAddress4,
--ResidenceCityId,
--ResidenceStateId,
ResidencePostalCode,
PermanentAddress1,
PermanentAddress2,
PermanentAddress3,
PermanentAddress4,
--PermanentCityId,
--PermanentStateId,
PermanentPostalCode,
PhoneMobile,
PhoneOffice,
PhoneResidence,
PhoneExtension,
EmailAddressOffice,
EmailAddressAlternate,
Sex,
BloodGroup,
DateOfBirth,
PAN,
LegacyEmpNo,
LegacyFunction,
HLoB,
HComments,
BankCode,
BankAccountNo,
CurrentLocationId,
CurrentBuildingId,
CurrentFloorId,
--CurrentSeatId,
CurrentAssetId,
EmployerId,
PassportNumber,
ExEmployer,
PastExperience,
Qualification1,
YearOfPassing1,
Qualification2,
YearOfPassing2,
Qualification3,
YearOfPassing3,
SBG,
SBU,
SBE,
CostCenterFunction,
BankIFSC_Code,
OrgEntity,
DateOfRehire)
select personid,
eid,
job_description,
job_business_title,
job_bu_id,
job_dept_id,
job_location_id,
job_grade,
date_of_hire,
date_of_termination,
supervisor_eid,
supervisor_personid,
salary_planner_eid,
salary_planner_personid,
hr_contact_eid,
hr_contact_personid,
name_prefix,
name_last,
name_first,
name_middle,
residence_address1,
residence_address2,
residence_address3,
residence_address4,
--residence_city,
--residence_state,
residence_postal,
permanent_address1,
permanent_address2,
permanent_address3,
permanent_address4,
--permanent_city,
--permanent_state,
permanent_postal,
phone_mobile,
phone_office,
phone_residence,
phone_extension,
email_address_office,
email_address_alternate,
sex,
blood_group,
date_of_birth,
pan,
legacy_empno,
legacy_function,
h_lob,
h_comments,
bank_id,
bank_account,
cast(current_location_id as int),
current_building_id,
current_floor_id,
--current_seat_id,
current_asset_id,
employerid,
passport_number,
ex_employer,
past_experience,
qualification1,
year_of_passing1,
qualification2,
year_of_passing2,
qualification3,
year_of_passing3,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8
from tblSSAppsEmpMasterTemp
except
select PersonId,
EID,
JobDescription,
BusinessTitle,
ProfitCenterCode,
CostCenterCode,
LegacyLocationId,
Grade,
DateOfHire,
DateOfTermination,
SupervisorEID,
SupervisorPersonId,
SalaryPlannerEID,
SalaryPlannerPersonId,
HrContactEID,
HrContactPersonId,
NamePrefix,
NameLast,
NameFirst,
NameMiddle,
ResidenceAddress1,
ResidenceAddress2,
ResidenceAddress3,
ResidenceAddress4,
--ResidenceCityId,
--ResidenceStateId,
ResidencePostalCode,
PermanentAddress1,
PermanentAddress2,
PermanentAddress3,
PermanentAddress4,
--PermanentCityId,
--PermanentStateId,
PermanentPostalCode,
PhoneMobile,
PhoneOffice,
PhoneResidence,
PhoneExtension,
EmailAddressOffice,
EmailAddressAlternate,
Sex,
BloodGroup,
DateOfBirth,
PAN,
LegacyEmpNo,
LegacyFunction,
HLoB,
HComments,
BankCode,
BankAccountNo,
CurrentLocationId,
CurrentBuildingId,
CurrentFloorId,
--CurrentSeatId,
CurrentAssetId,
EmployerId,
PassportNumber,
ExEmployer,
PastExperience,
Qualification1,
YearOfPassing1,
Qualification2,
YearOfPassing2,
Qualification3,
YearOfPassing3,
SBG,
SBU,
SBE,
CostCenterFunction,
BankIFSC_Code,
OrgEntity,
DateOfRehire
from custom.dbo.tblSSAppsEmpMaster

if (@@error <> 0)
begin
set @v_IsError = 2
goto ExitRoutine
end

--update changed records (irrespective of which column value has change)
update ema
set ema.PersonId = emd.PersonId,
ema.EID = emd.EID,
ema.JobDescription = emd.JobDescription,
ema.BusinessTitle = emd.BusinessTitle,
ema.ProfitCenterCode = emd.ProfitCenterCode,
ema.CostCenterCode = emd.CostCenterCode,
ema.LegacyLocationId = emd.LegacyLocationId,
ema.Grade = emd.Grade,
ema.DateOfHire = emd.DateOfHire,
ema.DateOfTermination = emd.DateOfTermination,
ema.SupervisorEID = emd.SupervisorEID,
ema.SupervisorPersonId = emd.SupervisorPersonId,
ema.SalaryPlannerEID = emd.SalaryPlannerEID,
ema.SalaryPlannerPersonId = emd.SalaryPlannerPersonId,
ema.HrContactEID = emd.HrContactEID,
ema.HrContactPersonId = emd.HrContactPersonId,
ema.NamePrefix = emd.NamePrefix,
ema.NameLast = emd.NameLast,
ema.NameFirst = emd.NameFirst,
ema.NameMiddle = emd.NameMiddle,
ema.ResidenceAddress1 = emd.ResidenceAddress1,
ema.ResidenceAddress2 = emd.ResidenceAddress2,
ema.ResidenceAddress3 = emd.ResidenceAddress3,
ema.ResidenceAddress4 = emd.ResidenceAddress4,
--ema.ResidenceCityId = emd.ResidenceCityId,
--ema.ResidenceStateId = emd.ResidenceStateId,
ema.ResidencePostalCode = emd.ResidencePostalCode,
ema.PermanentAddress1 = emd.PermanentAddress1,
ema.PermanentAddress2 = emd.PermanentAddress2,
ema.PermanentAddress3 = emd.PermanentAddress3,
ema.PermanentAddress4 = emd.PermanentAddress4,
--ema.PermanentCityId = emd.PermanentCityId,
--ema.PermanentStateId = emd.PermanentStateId,
ema.PermanentPostalCode = emd.PermanentPostalCode,
ema.PhoneMobile = emd.PhoneMobile,
ema.PhoneOffice = emd.PhoneOffice,
ema.PhoneResidence = emd.PhoneResidence,
ema.PhoneExtension = emd.PhoneExtension,
ema.EmailAddressOffice = emd.EmailAddressOffice,
ema.EmailAddressAlternate = emd.EmailAddressAlternate,
ema.Sex = emd.Sex,
ema.BloodGroup = emd.BloodGroup,
ema.DateOfBirth = emd.DateOfBirth,
ema.PAN = emd.PAN,
ema.LegacyEmpNo = emd.LegacyEmpNo,
ema.LegacyFunction = emd.LegacyFunction,
ema.HLoB = emd.HLoB,
ema.HComments = emd.HComments,
ema.BankCode = emd.BankCode,
ema.BankAccountNo = emd.BankAccountNo,
ema.CurrentLocationId = emd.CurrentLocationId,
ema.CurrentBuildingId = emd.CurrentBuildingId,
ema.CurrentFloorId = emd.CurrentFloorId,
--ema.CurrentSeatId = emd.CurrentSeatId,
ema.CurrentAssetId = emd.CurrentAssetId,
ema.EmployerId = emd.EmployerId,
ema.PassportNumber = emd.PassportNumber,
ema.ExEmployer = emd.ExEmployer,
ema.PastExperience = emd.PastExperience,
ema.Qualification1 = emd.Qualification1,
ema.YearOfPassing1 = emd.YearOfPassing1,
ema.Qualification2 = emd.Qualification2,
ema.YearOfPassing2 = emd.YearOfPassing2,
ema.Qualification3 = emd.Qualification3,
ema.YearOfPassing3 = emd.YearOfPassing3,
ema.SBG = emd.SBG,
ema.SBU = emd.SBU,
ema.SBE = emd.SBE,
ema.CostCenterFunction = emd.CostCenterFunction,
ema.BankIFSC_Code = emd.BankIFSC_Code,
ema.OrgEntity = emd.OrgEntity,
ema.DateOfRehire = emd.DateOfRehire
from custom.dbo.tblSSAppsEmpMaster ema
join tblSSAppsEmpMasterDiffer emd on (ema.PersonId = emd.PersonId)

set @v_Updated = @@rowcount

if (@@error <> 0)
begin
set @v_IsError = 3
goto ExitRoutine
end

--add new records
insert into custom.dbo.tblSSAppsEmpMaster
( PersonId,
EID,
JobDescription,
BusinessTitle,
ProfitCenterCode,
CostCenterCode,
LegacyLocationId,
Grade,
DateOfHire,
DateOfTermination,
SupervisorEID,
SupervisorPersonId,
SalaryPlannerEID,
SalaryPlannerPersonId,
HrContactEID,
HrContactPersonId,
NamePrefix,
NameLast,
NameFirst,
NameMiddle,
ResidenceAddress1,
ResidenceAddress2,
ResidenceAddress3,
ResidenceAddress4,
--ResidenceCityId,
--ResidenceStateId,
ResidencePostalCode,
PermanentAddress1,
PermanentAddress2,
PermanentAddress3,
PermanentAddress4,
--PermanentCityId,
--PermanentStateId,
PermanentPostalCode,
PhoneMobile,
PhoneOffice,
PhoneResidence,
PhoneExtension,
EmailAddressOffice,
EmailAddressAlternate,
Sex,
BloodGroup,
DateOfBirth,
PAN,
LegacyEmpNo,
LegacyFunction,
HLoB,
HComments,
BankCode,
BankAccountNo,
CurrentLocationId,
CurrentBuildingId,
CurrentFloorId,
--CurrentSeatId,
CurrentAssetId,
EmployerId,
PassportNumber,
ExEmployer,
PastExperience,
Qualification1,
YearOfPassing1,
Qualification2,
YearOfPassing2,
Qualification3,
YearOfPassing3,
SBG,
SBU,
SBE,
CostCenterFunction,
BankIFSC_Code,
OrgEntity,
DateOfRehire)
select PersonId,
EID,
JobDescription,
BusinessTitle,
ProfitCenterCode,
CostCenterCode,
LegacyLocationId,
Grade,
DateOfHire,
DateOfTermination,
SupervisorEID,
SupervisorPersonId,
SalaryPlannerEID,
SalaryPlannerPersonId,
HrContactEID,
HrContactPersonId,
NamePrefix,
NameLast,
NameFirst,
NameMiddle,
ResidenceAddress1,
ResidenceAddress2,
ResidenceAddress3,
ResidenceAddress4,
--ResidenceCityId,
--ResidenceStateId,
ResidencePostalCode,
PermanentAddress1,
PermanentAddress2,
PermanentAddress3,
PermanentAddress4,
--PermanentCityId,
--PermanentStateId,
PermanentPostalCode,
PhoneMobile,
PhoneOffice,
PhoneResidence,
PhoneExtension,
EmailAddressOffice,
EmailAddressAlternate,
Sex,
BloodGroup,
DateOfBirth,
PAN,
LegacyEmpNo,
LegacyFunction,
HLoB,
HComments,
BankCode,
BankAccountNo,
CurrentLocationId,
CurrentBuildingId,
CurrentFloorId,
--CurrentSeatId,
CurrentAssetId,
EmployerId,
PassportNumber,
ExEmployer,
PastExperience,
Qualification1,
YearOfPassing1,
Qualification2,
YearOfPassing2,
Qualification3,
YearOfPassing3,
SBG,
SBU,
SBE,
CostCenterFunction,
BankIFSC_Code,
OrgEntity,
DateOfRehire
from tblSSAppsEmpMasterDiffer emd
where not exists (select PersonId
from custom.dbo.tblSSAppsEmpMaster ema
where ema.PersonId = emd.PersonId)

set @v_Inserted = @@rowcount

if (@@error <> 0)
begin
set @v_IsError = 4
goto ExitRoutine
end
END

ExitRoutine:

if (@v_IsError = 0)
begin
if (@v_InTran = 1)
begin
commit transaction
--print 'Updated: ' + convert(varchar,@v_Updated) + ', Inserted: ' + convert(varchar,@v_Inserted)
end
end
else
begin
if (@v_InTran = 1)
begin
rollback transaction
end
end
--print '@v_IsError = ' + convert(varchar,@v_IsError)
set nocount off
return @v_IsError

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 08:49:46

which one is efficient from above 2 procedures.... if there are many records in database

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 09:33:15

have you compared the io and runtimes of the two queries? Have you studied the execution plans? What do they tell you?

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-08 : 09:41:05

as per execution plan
it shows
not exists is efficient

what is your opinion
as per functionality and any other issues that may be missing by me or anything

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 10:29:27

For the insert, I would go with NOT EXISTS -- shorter, easier to read, usually efficient if the column in the WHERE clause is indexed). For the update, WHERE EXISTS might be easier to read. I suspect that in your case, the execution plans for the JOIN and WHERE EXISTS (on the update) might be the same. Can you verify?

Given equivalent ex. plans, the choice comes down to style. I find WHERE EXISTS more explicit but have no problem with the JOIN approach, which gives the same result.

I wouldn't use EXCEPT in your example -- too long to read, possibly worse exe. plan (though you'd have to check)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 10:37:09

btw, you might want to get a sql formatter (like Apex Refactor or Poor Man's Sql formatter) to make your code easier to read

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources