3 Replies - 200 Views - Last Post: 12 March 2019 - 08:17 AM

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,082
  • Joined: 12-December 12

existence of rows

Posted 05 March 2019 - 04:05 AM

A trivial question probably. I want to check whether there are any rows in a table before inserting them.

Can someone describe the behaviour of EXISTS, which "Specifies a subquery to test for the existence of rows." source

I originally thought to use Count(*) but this fails:

IF NOT EXISTS (SELECT COUNT(*) FROM dbo.Countries)
BEGIN
	PRINT 'Insert all countries'
	INSERT INTO dbo.Countries (CountryCode, CountryName, SortPriority) 
		SELECT CountryCode, CountryName, SortPriority FROM #tempCountries
END
GO

I can see that count(*) is 0, which is "a value".

It works with SELECT 1 or SELECT *, which I can understand, because these "values" would be associated with existing rows.

But the linked page (above) states that SELECT NULL also equates to True.

Maybe my brain is clouded this morning, but can someone clarify the behaviour?
Also, do you have a preferred way to check for rows?

Is This A Good Question/Topic? 0
  • +

Replies To: existence of rows

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7433
  • View blog
  • Posts: 15,409
  • Joined: 16-October 07

Re: existence of rows

Posted 05 March 2019 - 05:36 AM

Exists probably exists so that you needn't do count.

This quick bit of test code works:
create table #foo (
  Id int identity(1,1) primary key,
  [Name] varchar(30) unique
)
go

if not exists (select * from #foo)
  begin
    print 'It''s empty, let''s do this thing!';
    insert into #foo([Name]) values ('Alice'),('Bob'),('Chuck');
  end
else
  print 'Skipping exists'
go

if (select count(*) from #foo)=0
  begin
    print 'It''s empty, let''s do this thing!';
    insert into #foo([Name]) values ('Alice'),('Bob'),('Chuck');
  end
else
  print 'Skipping count'
go

select * from #foo
go



However, for your current case, I'd prefer:
INSERT INTO dbo.Countries (CountryCode, CountryName, SortPriority) 
  SELECT CountryCode, CountryName, SortPriority
    FROM #tempCountries a
      left join dbo.Countries b on a.CountryCode=b.CountryCode and a.CountryName=b.CountryName and a.SortPriority=b.SortPriority
    where b.CountryCode is null



This allows for a repeated use of the call, filling in the missing bits. e.g.
insert into #foo([Name]) 
  select a.[Name]
    from (select 'Alice' as [Name] union select 'Bob' union select 'Chuck') a
      left join #foo b on a.[Name]=b.[Name]
    where b.Id is null
print cast(@@ROWCOUNT as varchar) + ' rows inserted'
go

insert into #foo([Name]) 
  select a.[Name]
    from (select 'Chuck' as [Name] union select 'Diane') a
      left join #foo b on a.[Name]=b.[Name]
    where b.Id is null
print cast(@@ROWCOUNT as varchar) + ' rows inserted'
go


Was This Post Helpful? 2
  • +
  • -

#3 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,082
  • Joined: 12-December 12

Re: existence of rows

Posted 05 March 2019 - 05:58 AM

You're a star
Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 466
  • View blog
  • Posts: 2,208
  • Joined: 07-April 08

Re: existence of rows

Posted 12 March 2019 - 08:17 AM

I want to expand on this a bit as I understand it.

Exists looks at the Row Count of the sub query being returned. If the row count would be 1 or more, then it's true. Records exist. If it's 0 then it's false. No records Exist. It's not looking at the data directly, but the returned result, so this: IF NOT EXISTS(SELECT COUNT(*) FROM Table) would return a row regardless of the data in the table (0 to mean no records meet the conditions, 1 or more if there are). So Exists would evaluate to true in every respect because COUNT Always returns a value.

Using a Literal such as SELECT 1 FROM... SELECT 'I EXIST' FROM... says to select those only if the conditions are met. So if Table had let's say 5 records by doing SELECT 1 from Table you'd get 5 rows all containing a single column with the value 1. While if TABLE has 0 rows, then no rows are returned. the Result set is Empty.

it's the same with SELECT * FROM. Basically return any result that matches my criteria. If the table is empty, nothing is returned, Row Count is 0. If it's not then something is returned.

In the case of SELECT NULL, you're telling it to Just output NULL with no conditions, so it will always return null. Row Count then is always at least 1 making EXIST true.

This post has been edited by rgfirefly24: 12 March 2019 - 08:19 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1