Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h1>Bottom Line</h1> <p>Use either <code>COUNT(field)</code> or <code>COUNT(*)</code>, and stick with it consistently, and if your database allows <code>COUNT(tableHere)</code> or <code>COUNT(tableHere.*)</code>, use that. </p> <p>In short, don't use <code>COUNT(1)</code> for anything. It's a one-trick pony, which rarely does what you want, and in those rare cases is equivalent to <code>count(*)</code></p> <h1>Use <code>count(*)</code> for counting</h1> <p>Use <code>*</code> for all your queries that need to count everything, even for joins, use <code>*</code></p> <pre><code>SELECT boss.boss_id, COUNT(subordinate.*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>But don't use <code>COUNT(*)</code> for LEFT joins, as that will return 1 even if the subordinate table doesn't match anything from parent table</p> <pre><code>SELECT boss.boss_id, COUNT(*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>Don't be fooled by those advising that when using <code>*</code> in COUNT, it fetches entire row from your table, saying that <code>*</code> is slow. The <code>*</code> on <code>SELECT COUNT(*)</code> and <code>SELECT *</code> has no bearing to each other, they are entirely different thing, they just share a common token, i.e. <code>*</code>.</p> <h2>An alternate syntax</h2> <p>In fact, if it is not permitted to name a field as same as its table name, RDBMS language designer could give <code>COUNT(tableNameHere)</code> the same semantics as <code>COUNT(*)</code>. Example:</p> <p>For counting rows we could have this:</p> <pre><code>SELECT COUNT(emp) FROM emp </code></pre> <p>And they could make it simpler:</p> <pre><code>SELECT COUNT() FROM emp </code></pre> <p>And for LEFT JOINs, we could have this:</p> <pre><code>SELECT boss.boss_id, COUNT(subordinate) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>But they cannot do that (<code>COUNT(tableNameHere)</code>) since SQL standard permits naming a field with the same name as its table name:</p> <pre><code>CREATE TABLE fruit -- ORM-friendly name ( fruit_id int NOT NULL, fruit varchar(50), /* same name as table name, and let's say, someone forgot to put NOT NULL */ shape varchar(50) NOT NULL, color varchar(50) NOT NULL ) </code></pre> <h3>Counting with null</h3> <p>And also, it is not a good practice to make a field nullable if its name matches the table name. Say you have values 'Banana', 'Apple', NULL, 'Pears' on <code>fruit</code> field. This will not count all rows, it will only yield 3, not 4</p> <pre><code>SELECT count(fruit) FROM fruit </code></pre> <p>Though some RDBMS do that sort of principle (for counting the table's rows, it accepts table name as COUNT's parameter), this will work in Postgresql (if there is no <code>subordinate</code> field in any of the two tables below, i.e. as long as there is no name conflict between field name and table name):</p> <pre><code>SELECT boss.boss_id, COUNT(subordinate) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>But that could cause confusion later if we will add a <code>subordinate</code> field in the table, as it will count the field(which could be nullable), not the table rows.</p> <p>So to be on the safe side, use:</p> <pre><code>SELECT boss.boss_id, COUNT(subordinate.*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <h1><code>count(1)</code>: The one-trick pony</h1> <p>In particular to <code>COUNT(1)</code>, it is a <strong>one-trick pony</strong>, it works well only on one table query:</p> <pre><code>SELECT COUNT(1) FROM tbl </code></pre> <p>But when you use joins, that trick won't work on multi-table queries without its semantics being confused, and in particular you cannot write:</p> <pre><code>-- count the subordinates that belongs to boss SELECT boss.boss_id, COUNT(subordinate.1) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>So what's the meaning of COUNT(1) here?</p> <pre><code>SELECT boss.boss_id, COUNT(1) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>Is it this...?</p> <pre><code>-- counting all the subordinates only SELECT boss.boss_id, COUNT(subordinate.boss_id) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>Or this...?</p> <pre><code>-- or is that COUNT(1) will also count 1 for boss regardless if boss has a subordinate SELECT boss.boss_id, COUNT(*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>By careful thought, you can infer that <code>COUNT(1)</code> is the same as <code>COUNT(*)</code>, regardless of type of join. But for LEFT JOINs result, we cannot mold <code>COUNT(1)</code> to work as: <code>COUNT(subordinate.boss_id)</code>, <code>COUNT(subordinate.*)</code></p> <p>So just use either of the following:</p> <pre><code>-- count the subordinates that belongs to boss SELECT boss.boss_id, COUNT(subordinate.boss_id) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>Works on Postgresql, it's clear that you want to count the cardinality of the set</p> <pre><code>-- count the subordinates that belongs to boss SELECT boss.boss_id, COUNT(subordinate.*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id </code></pre> <p>Another way to count the cardinality of the set, very English-like (just don't make a column with a name same as its table name) : <a href="http://www.sqlfiddle.com/#!1/98515/7" rel="noreferrer">http://www.sqlfiddle.com/#!1/98515/7</a></p> <pre><code>select boss.boss_name, count(subordinate) from boss left join subordinate on subordinate.boss_code = boss.boss_code group by boss.boss_name </code></pre> <p>You cannot do this: <a href="http://www.sqlfiddle.com/#!1/98515/8" rel="noreferrer">http://www.sqlfiddle.com/#!1/98515/8</a></p> <pre><code>select boss.boss_name, count(subordinate.1) from boss left join subordinate on subordinate.boss_code = boss.boss_code group by boss.boss_name </code></pre> <p>You can do this, but this produces wrong result: <a href="http://www.sqlfiddle.com/#!1/98515/9" rel="noreferrer">http://www.sqlfiddle.com/#!1/98515/9</a></p> <pre><code>select boss.boss_name, count(1) from boss left join subordinate on subordinate.boss_code = boss.boss_code group by boss.boss_name </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCOUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USjpaugh
    1. USMichael Buen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCOUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the advice. Are there reasons behind those imperatives?
      singulars
      1. PO
      1. USzneak
    2. COImperatives regarding using `*` consistently? Yes there is, to make things simpler and consistent. In fact if SQL standard ruled that a field name could not have the same name as its table name, there could be only two forms of COUNT, one is `COUNT()` and the other is `COUNT(tableOrAliasedTablenameHere)`, life could be simpler. If we think about it, we don't count field, we count rows. And if SQL standard ruled that there could be no nullable fields in the database, we really won't need `COUNT(fieldnameHere)` construct at all.
      singulars
      1. PO
      1. USMichael Buen
    3. COIf it's only for the sake of consistency, then you could also use COUNT(1) instead of COUNT(*). Is one of them better than the other?
      singulars
      1. PO
      1. USzneak
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload