What is the difference between COUNT(*) and COUNT(columnname) after joining tables?-CodePudding

The first code like this

SELECT s.name, w.channel, COUNT(*) num_events
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name, w.channel
ORDER BY num_events DESC;

The second code like this

SELECT s.name, w.channel, COUNT(w.channel) 
FROM web_events w
JOIN accounts a
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name, w.channel
ORDER BY COUNT(w.channel) DESC

Both codes have the same results. But why? What is different from COUNT(*) num_events to COUNT(w.channel)?

CodePudding user response：

If you are using MySQL,then we can check MySQL official document for function_count,and will found below

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

COUNT(DISTINCT expr,[expr...]) Returns a count of the number of rows with different non-NULL expr values.

So COUNT(w.channel) will get total records the query fetch and w.channel is not null

CodePudding user response：

COUNT (*) counts all the row
COUNT (field) counts rows that field value is not NULL

When i say NULL, i strictly mean NULL. Check below sql

You can see COUNT function counts empty string on postgrsql (empty string is not same as NULL). But in Oracle database empty strings is same as NULL

WITH temp AS (SELECT 1 AS a, 'x' AS b, 1 AS c
              UNION ALL
              SELECT 2 AS a, 'x' AS b, 1 AS c
              UNION ALL
              SELECT 3 AS a, 'y' AS b, 1 AS c
              UNION ALL
              SELECT 4 AS a, 'y' AS b, 1 AS c
              UNION ALL
              SELECT 5 AS a, '' AS b, NULL AS c)
SELECT count( * ) AS "count(*)"
     , count( DISTINCT b ) AS "count( distinct something )"
     , count( c ) AS "count (something)"
FROM temp

Result on postgresql

count(*)       count( distinct something )           count (something)
--------       --------------------------            -----------------
 5                      3                                4

Result on oracle

count(*)       count( distinct something )           count (something)
--------       --------------------------            -----------------
 5                      2                                4