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
