On 2017/09/05 10:21 PM, Cecil Westerhof wrote:
I want to know the number of teas I have in stock. For this I use:
SELECT COUNT(Tea)
FROM   teaInStock

Tea cannot be NULL, so this is the same as:
SELECT COUNT(*)
FROM   teaInStock

​But I find the first more clear.
I almost always see the second variant. Is this because it is more
efficient, or are people just ‘lazy’?​


Nothing to do with laziness - depends on what is wanted:
COUNT(*) counts the rows in the DB that matches the filter (WHERE clause).
COUNT(Tea) counts the Tea column only and will return only the number of values that are not NULL.

A short script to demonstrate the differences:

  -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed version 2.0.2.4.   -- ================================================================================================

CREATE TABLE t(ID INT, Tea TEXT);

INSERT INTO t(ID, Tea) VALUES
(1, 'Ceylon'),
(2, Null),
(3, 'Earl Grey'),
(4, NULL),
(5, 'Jasmine')
;

SELECT * FROM t;


  --      ID      | Tea
  -- ------------ | ---------
  --       1      | Ceylon
  --       2      | NULL
  --       3      | Earl Grey
  --       4      | NULL
  --       5      | Jasmine

SELECT COUNT(*) FROM t;


  --   COUNT(*)
  -- ------------
  --       5

SELECT COUNT(Tea) FROM t;


  --  COUNT(Tea)
  -- ------------
  --       3

SELECT COUNT(*) FROM t WHERE Tea IS NULL;


  --   COUNT(*)
  -- ------------
  --       2

DROP TABLE t;

  -- ================================================================================================



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to