Know your UNION(s), NULL(s), COUNT(s) ?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Todd Carrier » Know your UNION(s), NULL(s), COUNT(s) ?

Add to briefcase

25 posts, Page 1 of 3

Know your UNION(s), NULL(s), COUNT(s) ?

Expand / Collapse

Author

Message

Posted Tuesday, June 03, 2008 8:09 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:54 PM
Points: 1,049, Visits: 633

Comments posted to this topic are about the item Know your UNION(s), NULL(s), COUNT(s) ?

Todd Carrier
MCITP - Database Administrator

Post #511082

Posted Wednesday, June 04, 2008 1:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, April 14, 2013 8:55 AM
Points: 1,383, Visits: 1,212

Fun question!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

Post #511170

Posted Wednesday, June 04, 2008 2:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:40 AM
Points: 1,078, Visits: 847

Before answering I was wondering if missing ALL on final union was intentional or a typo.
Count(*) counts all rows, Count([ALL] column) counts rows where column is not null, so it doesn't matter whether final union is just "union" or "union all".
Fun question anyway. :)

Post #511195

Posted Wednesday, June 04, 2008 6:03 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:50 AM
Points: 4,785, Visits: 1,334

Refreshing one ..........:)

Post #511285

Jamie Longstreet-481950

Posted Wednesday, June 04, 2008 6:41 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:57 PM
Points: 2,440, Visits: 712

I agree - great question!

Jamie

Post #511308

Posted Wednesday, June 04, 2008 6:53 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 3,547, Visits: 920

That one made me slow down and think for a while.

Post #511317

Christopher Stobbs

Posted Wednesday, June 04, 2008 7:05 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232

I also had to sit down for a bit on this one Wink

Wink

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t

w00t

Posting Best Practices
Numbers / Tally Tables

SQL-4-Life

Post #511327

Posted Wednesday, June 04, 2008 7:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 08, 2011 8:52 AM
Points: 26, Visits: 79

At first I was wondering if it was a typo as well with the missing UNION ALL. However, it still got me. Keep up the fun questions, I look forward to them each day.

Post #511338

Posted Wednesday, June 04, 2008 8:03 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:22 AM
Points: 551, Visits: 1,150

Ya, great question.

I didn't realize the subtle different between Count(*), Count(1) and Count(expression)

Count(*) - Includes Nulls
Count(1) - Includes Nulls
Count(expression) - Does not include nulls ie Count(ColumnName)

Post #511393

Posted Wednesday, June 04, 2008 8:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:54 PM
Points: 1,049, Visits: 633

It had been a while since I wrote the question, and I MISSED IT! The subtle but important difference between count (*) and count (columnname) is something I will have to continue to drill into my think skull!

Todd Carrier
MCITP - Database Administrator

Post #511424

« Prev Topic | Next Topic »

Add to briefcase

25 posts, Page 1 of 3

Permissions

Expand / Collapse