What Does "Select Count(1) from Table_Name" on Any Database Tables Mean

What does select count(1) from table_name on any database tables mean?

The parameter to the COUNT function is an expression that is to be evaluated for each row. The COUNT function returns the number of rows for which the expression evaluates to a non-null value. ( * is a special expression that is not evaluated, it simply returns the number of rows.)

There are two additional modifiers for the expression: ALL and DISTINCT. These determine whether duplicates are discarded. Since ALL is the default, your example is the same as count(ALL 1), which means that duplicates are retained.

Since the expression "1" evaluates to non-null for every row, and since you are not removing duplicates, COUNT(1) should always return the same number as COUNT(*).

What does it mean by select 1 from table?

SELECT 1 FROM TABLE_NAME means, "Return 1 from the table". It is pretty unremarkable on its own, so normally it will be used with WHERE and often EXISTS (as @gbn notes, this is not necessarily best practice, it is, however, common enough to be noted, even if it isn't really meaningful (that said, I will use it because others use it and it is "more obvious" immediately. Of course, that might be a viscous chicken vs. egg issue, but I don't generally dwell)).

 SELECT * FROM TABLE1 T1 WHERE EXISTS (
     SELECT 1 FROM TABLE2 T2 WHERE T1.ID= T2.ID
 );

Basically, the above will return everything from table 1 which has a corresponding ID from table 2. (This is a contrived example, obviously, but I believe it conveys the idea. Personally, I would probably do the above as SELECT * FROM TABLE1 T1 WHERE ID IN (SELECT ID FROM TABLE2); as I view that as FAR more explicit to the reader unless there were a circumstantially compelling reason not to).

EDIT

There actually is one case which I forgot about until just now. In the case where you are trying to determine existence of a value in the database from an outside language, sometimes SELECT 1 FROM TABLE_NAME will be used. This does not offer significant benefit over selecting an individual column, but, depending on implementation, it may offer substantial gains over doing a SELECT *, simply because it is often the case that the more columns that the DB returns to a language, the larger the data structure, which in turn mean that more time will be taken.

SQL Query: Which one should i use? count(columnname) or count(1)

There can be differences between count(*) and count(column). count(*) is often fastest for reasons discussed here. Basically, with count(column) the database has to check if column is null or not in each row. With count(column) it just returns the total number of rows in the table which is probably has on hand. The exact details may depend on the database and the version of the database.

Short answer: use count(*) or count(1). Hell, forget the count and select userid.

You should also make sure the where clause is performing well and that its using an index. Look into EXPLAIN.

Get record counts for all tables in MySQL database

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).

Extracting number of records in each table

Extracting number of records in each table

Then you are completely in a wrong direction while querying DBA_TABLES. It will give you the count of tables owned by a user, but not the count of rows in each table.

There are three ways:

• In pure SQL, the table name must be static. i.e. you will have to list down the table names explicitly and get the count.

SELECT COUNT(*) FROM table_1;
SELECT COUNT(*) FROM table_2;
SELECT COUNT(*) FROM table_3;
...
and so on...

Using a good text editor, you could do it quickly.

• If the statistics are collected up to date, then you could rely on NUM_ROWS in DBA_TABLES. In any case, better gather the statistics first using DBMS_STATS.

SQL> SELECT table_name, num_rows FROM dba_tables WHERE owner='SCOTT';

TABLE_NAME   NUM_ROWS
---------- ----------
CLASS               0
SALGRADE            5
BONUS               0
DEPT                4
EMP                14

• In PL/SQL, loop through all the table names, create dynamic sql and execute (ab)using EXECUTE IMMEDIATE.

Select count(*) from multiple tables

SELECT  (
        SELECT COUNT(*)
        FROM   tab1
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   tab2
        ) AS count2
FROM    dual

Does a SELECT COUNT(*) query have to do a full table scan?

The server will always read all records (if there's an index then it will scan the entire index) to count the rows. You can't escape this as long as you are doing SELECT COUNT(*) FROM Table.

If your table has a clustered index, you can change your query to an "under the hood" query to retrieve the count without actually fetching the records with:

SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count]
FROM sys.sysindexes i WITH (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rowcnt desc

if you are looking for an approximate count of the records, you can also use the following query:

SELECT 
    TableName = t.NAME,
    SchemaName = s.Name,
    [RowCount] = p.rows,
    TotalSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.total_pages) * 8 / 1024.0), 
    UsedSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.used_pages) * 8 / 1024.0),
    UnusedSpaceMB = CONVERT(DECIMAL(18,2), (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0)
FROM 
    sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, 
    s.Name, 
    p.Rows
ORDER BY 
    TotalSpaceMB DESC

This will show non-system tables with their calculated (not exact) row count and the sum of the sizes of their data (with any index they might have), relatively fast without retrieving the records.

SQL count rows in a table

Yes, SELECT COUNT(*) FROM TableName

Fastest way to count exact number of rows in a very large table?

Simple answer:

• Database vendor independent solution = use the standard = COUNT(*)
• There are approximate SQL Server solutions but don't use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)

---

Related Topics