SQL Interview Question - Difference between Count(*), Count(1), Count(colname) | Which is fastest

Поделиться
HTML-код
  • Опубликовано: 26 апр 2021
  • This tutorial discusses the difference between Count(*), count(1) and count(colname) in SQL and explains which of them is the fastest
    How to install SQL Server for practice?
    • How to install SQL Ser...
    Check out the complete list of SQL Query Interview Questions -
    • SQL Query Interview Qu...
    Sign up for a free trial of Coupler.io - The No code data integration tool
    app.coupler.io/register/sign_...
    Get USD 100 off Coursera Plus Annual Subscription
    imp.i384100.net/Yg6nxR
    Get 50% off Skillshare Annual Subscription with code AFF50.
    Dates: 11/24 at midnight - 11/28 at midnight
    skillshare.eqcm.net/5b6Z3N
    Best Data Science / Analytics / SQL courses
    Learn SQL Basics for Data Science Specialization
    imp.i384100.net/qnXYk5
    Data Science Fundamentals with Python and SQL Specialization
    imp.i384100.net/mgVYre
    IBM Data Science Professional Certificate
    imp.i384100.net/LPQvg3
    Python for Everybody Specialization
    imp.i384100.net/DVz7Aj
    Google Data Analytics Professional Certificate
    imp.i384100.net/OR37oQ
    Coursera Plus - Data Science Career Skills
    imp.i384100.net/c/3299742/132...
    Please do not forget to like, subscribe and share.
    For enrolling and enquiries, please contact us at
    Website - knowstar.org/
    Facebook - / knowstartrainings
    Linkedin - www.linkedin.com/company/know...
    Email - learn@knowstar.org

Комментарии • 94

  • @LearnatKnowstar
    @LearnatKnowstar  Год назад +1

    Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
    The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
    imp.i384100.net/Ke51on

  • @jfletcher12
    @jfletcher12 2 года назад +44

    "There is no difference" - effectively demonstrates live that count(1) took 40x longer to execute than count(*)

    • @TimGautier
      @TimGautier 2 года назад +7

      Doing a count on a table with only 10 rows is so trivial that you'll see wild variations in speed just due to other processes running on the system.

    • @VictorSzk
      @VictorSzk 2 года назад

      The tables I work isually have millions of rows, its good to know these things

  • @marcosbarcellos1601
    @marcosbarcellos1601 2 года назад +2

    Thank you for the explanation. I'm sharing the video with a friend. He wanted to know the syntax difference of COUNT. 😁☺️

  • @marcelositonio2495
    @marcelositonio2495 Месяц назад

    Great and clear explanation on Count(1), * plus ColumnName. Thanks!

  • @Shinnra
    @Shinnra 2 года назад

    Yestarday I had some clases and a question similar to this one was asked to the professor, I understood your answer way better than his, thank you!

  • @saleembasha2918
    @saleembasha2918 2 года назад +6

    It's like soo coool explanations, you are making everything so easy by perfect usage of all clauses, functions etc., of SQ language

  • @RajatKumar-rf4xi
    @RajatKumar-rf4xi 3 года назад +4

    Same count questions has been asked in 24th April 2021 in tiger analytics

  • @praveenshetty7688
    @praveenshetty7688 Год назад

    Hi, Can you make video on long running stored procedures. Daily its running fyn but one day its taking long time so how we can debug it

  • @rprasad8271
    @rprasad8271 2 года назад +3

    Good Stuff, All are very useful. Plz provide more videos...

  • @avishekdas9557
    @avishekdas9557 2 года назад

    Very good explanation, thank you!!

  • @USA_to_Asia_in_2024
    @USA_to_Asia_in_2024 3 дня назад

    Thank you.

  • @designanddecor8964
    @designanddecor8964 Год назад

    Thank you very much for the wonderful explanation.

  • @jacksparrowcapitan3232
    @jacksparrowcapitan3232 3 года назад +5

    Great content !!!!

  • @karunakaranr2473
    @karunakaranr2473 Год назад +1

    Nice explanation... Thank you for your time and effort.

  • @mohitgupta7145
    @mohitgupta7145 2 года назад

    When do we use count(1) in our queries and what's it's purpose

  • @danieljust295
    @danieljust295 2 года назад +2

    It all depends on the table size and it’s structure and number of CPUs.
    On my PC , table with one column and 500k rows count(*) and count(1) take approximately the same time, count(column) takes longer.
    Table with 3 columns and 500k rows - count(*) is slightly faster than count(1) , count(column) takes double time.
    The timing may be different for different version of database. I am sure we would see significant difference in the old versions such as SQL 2008. Count(1), count(*) and count(column) fetched rows differently and this caused the execution variation.

  • @miguelpetrarca5540
    @miguelpetrarca5540 2 года назад +6

    I have seen/heard select count(*) from tableA returns count of all rows in table regardless of whether a row has all null or some null values, on the other hard select count(columnA) from tableA returns the number of records that do not have null in columnA. But what if we use a group by? I have seen no info on this. are they equivalent?

    • @RakeshKumar-dq3db
      @RakeshKumar-dq3db 2 года назад +1

      I have the same doubt

    • @borus_editz1259
      @borus_editz1259 2 года назад +1

      @@RakeshKumar-dq3db yes me too

    • @nareshsmily226
      @nareshsmily226 2 года назад

      Group by will elimates the duplicates it will fetch only unique records from coloumns

    • @JoaoPinela
      @JoaoPinela 2 года назад +2

      @miguel Petrarca , if you use a group by with a count (columnname) , the resulting aggregation Will only account for non-null records

  • @gpcgpc810
    @gpcgpc810 Год назад

    Thanks for sharing, but I don't see you show Count(colname) cost how many sec.
    Did I miss something?

  • @ashutoshpandey1452
    @ashutoshpandey1452 Год назад

    You're an amazing teacher.

  • @tugrularslan542
    @tugrularslan542 Год назад

    So great explaination. Thank a lot

  • @arpangupta2162
    @arpangupta2162 Год назад

    count(*) was 40 times faster than count(1) in the example.
    this is a big difference when we work in production.
    just like hbase and hive

  • @AsifHussain-ul4ss
    @AsifHussain-ul4ss 2 года назад

    Thank you for the class ❤️

  • @Foodie-Techie
    @Foodie-Techie Год назад

    Well explained.Appreciate

  • @tlozwarlock
    @tlozwarlock 2 года назад +50

    As a 10 year+ SQL DBA and Dev... a difference of a quarter second (0.250) versus a 7 thousandth of a second (0.007) process time in an execution plan means that count(*) for raw row count is indeed faster when you start scaling. Even running against a clustered index, count(1) is going to be marginally slower. count(*) is your winner. Imagine multiple millions of rows with a count(1) with just a PK index. I shudder at the thought.

    • @user-rr3sb8hj8b
      @user-rr3sb8hj8b 2 года назад

      I have learnt count(1) is quicker than count(*), you mean that is wrong?

    • @betamvmt
      @betamvmt 2 года назад

      make your own test, you'll be surprised.

    • @garylee867
      @garylee867 2 года назад +4

      The time difference is just due to some fluctuation.
      The query optimizer is smart enough to figure out COUNT(*) and COUNT(1) are equivalent and execute them in exactly the same way. Besides, Taking 0.25s to scan a 60000 rows table is ridiculously slow. (I tried it on my machine as well)

    • @cyfrowymuza
      @cyfrowymuza 2 года назад +6

      that's ridiculous, the plans are the same, meaning the same computations are done, meaning the time difference is because of you running it on your laptop where you run multiple different programs e.g. you are recording this video, so in essence - count(1) or count(*) perform exactly the same !

    • @user-pl9ec3ns6f
      @user-pl9ec3ns6f 2 года назад +4

      Seems like due to cache was cold for count(1)

  • @sanc7753
    @sanc7753 3 года назад +2

    Waiting for another interesting video

  • @madhuchowdarytalluri7739
    @madhuchowdarytalluri7739 3 года назад +3

    very good explanation, everyone can easily understand.

  • @visaalakshiselvaraj5572
    @visaalakshiselvaraj5572 3 года назад +1

    Wonderful ❤️

  • @kunalkawale4829
    @kunalkawale4829 2 года назад

    If we say select distinct(column )will the null value row will also be shown? Since it is distinct.

    • @MrGaurav331
      @MrGaurav331 11 месяцев назад

      yes it would show you NULL also

  • @kovalensue
    @kovalensue 2 года назад +6

    It's funny that in the Oracle database is count(1) and count(*) exactly the same... They have the same execution plan. 🙂

  • @tableaututorialstips71
    @tableaututorialstips71 Год назад

    Thanks for sharing valueble videos mam :),Liked,shared,subscribed :)

  • @sbharathiraja8671
    @sbharathiraja8671 Год назад

    Its helpful for me

  • @mohammadyounos2011
    @mohammadyounos2011 2 года назад

    The way ur explanation is good. I need a query which explain sales aging by 6 months qty totals from table. Could u please help.

    • @LearnatKnowstar
      @LearnatKnowstar  2 года назад +1

      Thank you

    • @LearnatKnowstar
      @LearnatKnowstar  2 года назад +1

      These are a few videos on sales. Please see if these help.
      ruclips.net/video/rGapnGwEWIU/видео.html

    • @LearnatKnowstar
      @LearnatKnowstar  2 года назад +1

      ruclips.net/video/CDGwVXknZXI/видео.html

  • @Mike-mc5ll
    @Mike-mc5ll 2 года назад

    Would there be any difference in prequel?

    • @jeisonsanchez4842
      @jeisonsanchez4842 2 года назад

      In prequel, the time is the same but the count is negative.

  • @joseignaciodiazgonzalez6769
    @joseignaciodiazgonzalez6769 2 года назад +4

    wow... the execution performance depends of many many things... on a local database so small as that one table youre working on you will never have a good sample... on remote databases with millions of rows those queries will differ significantly

  • @nicolas3palacios805
    @nicolas3palacios805 2 года назад

    Thanks

  • @scottt1412
    @scottt1412 2 года назад

    my interview question for count would be:. How would I perform a count on a table with 30 billion rows? (Hint, bigint comes into play).

    • @LearnatKnowstar
      @LearnatKnowstar  2 года назад

      That is a great question. Please post your approach as well for this scenario.

    • @TimGautier
      @TimGautier 2 года назад +3

      I deal with tables that big on a regular basis. If I need to know the unfiltered count (roughly), I'll check the table stats, not run a count query. :)

    • @scottt1412
      @scottt1412 2 года назад

      @@TimGautier that's a good one.. the stats will be ultra fast too. The one issue is if your stats are somewhat stale they could be considerably off, in which case count_big works well on those very large tables.
      Side comment, If you're doing something with columnstore indexes then your counts and aggregates will be much much faster too, albeit CPU intensive sometimes.

  • @aritrabanerjee645
    @aritrabanerjee645 Год назад

    if we have NULL in all columns and do COUNT(*), will that row be counted?

  • @souravsubham8575
    @souravsubham8575 2 года назад +1

    If count (1) and count (*) are same in every aspect.. what is the need of count(1)?

    • @LearnatKnowstar
      @LearnatKnowstar  2 года назад

      You are right. Both serve the same purpose. It is just a way of writing.

    • @omkarbobade1115
      @omkarbobade1115 2 года назад +1

      Person who have lot of time can use the count(1) 😅

  • @MalClarke
    @MalClarke 2 года назад +5

    This video is misleading. If you add a value between the parentheses it will assign that values to every row and return the count. It will ALWAYS be slower than count *. They will return the same result but are NOT THE SAME. Your timing demonstrates that

  • @kierasher1
    @kierasher1 2 года назад +2

    I've been writing SQL for a long time, and I like to think I'm very good at it. Never in my life have I even considered typing "count(1)", and I clicked on this in a big WTF moment. Not gonna lie. My time was wasted.

  • @data_home
    @data_home 2 года назад +1

    SQL-in sadə yolla izahı

  • @badprogrammerr162
    @badprogrammerr162 2 года назад

    Now compare SUM(1) vs COUNT(4) 😂

  • @shirishnamdeo1775
    @shirishnamdeo1775 2 года назад +1

    Wrong, You executed then count(*) two times, intially it took 5 seconds.. check your video yourself.

    • @newsun9382
      @newsun9382 2 года назад

      You could have put it in a softer way. BTW, what's you channel? Do you have one? Indians should learn gratitude.

    • @shirishnamdeo1775
      @shirishnamdeo1775 2 года назад +4

      @@newsun9382 No i dont have channels.
      And btw dont bring this to off topic, with me being Indian.
      We are commenting on the technical topic.
      Keep Nation, And other topics off the thread.

    • @newsun9382
      @newsun9382 2 года назад

      @@shirishnamdeo1775 No I will not keep nation, nationality out of any thing. Indians, thought they form bulk of the IT world, are net consumers of such videos and articles. They are very good at criticising though.
      If you take out the nationality part, all I said was to be more polite and encouraging. We dont have to be @$$ #0l#$ all the time. Take a break sometimes.

  • @shabareesharyan
    @shabareesharyan Год назад

    please charge your laptop its giving my anxiety

  • @HansenTandi
    @HansenTandi 2 года назад +1

    Count(1) can be faster than count(*) if column 1 is a primary key column. Cmiiw

  • @ltorres321
    @ltorres321 2 года назад +7

    Is no here going to call this fraud video? She says no difference between * and 1 yet the * had .007 and 1 had .275 ? That is a huge significance. I would take this course for free 0 trust…

  • @sylvesterkamau2051
    @sylvesterkamau2051 2 года назад +1

    Count(*) is always faster than count(1)

  • @adreskorespondencyjny1551
    @adreskorespondencyjny1551 2 года назад

    calculating efficiency on 10rows database? aha, what pronouns do you use?