Member Avatar for Acute

Hi, I need to create a sql query that will return the sum of the total number of records in the t1 and t2 tables. Any ideas how to do it?

Thnx in advance

  • 3 Contributors
  • 4 Replies
  • 85 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Acute

Dani AI

Generated

A brief follow-up to the thread: 's UNION ALL solution is a fine, simple way to get the total row count and correctly described the role of the constant column. Below are two alternate approaches and practical notes that help when accuracy or performance matters.

Use two scalar COUNTs and add them in one result (avoids building a derived table):

SELECT
  (SELECT COUNT(*) FROM t1) + (SELECT COUNT(*) FROM t2) AS total_rows;

This returns an exact total. Each COUNT still scans its table (no magic), so performance is driven by table size, storage engine, and indexes.

For a fast, approximate total that avoids full scans, query information_schema (engine-dependent):

SELECT SUM(table_rows) AS approx_total
FROM information_schema.tables
WHERE table_schema = 'your_db'
  AND table_name IN ('t1', 't2');

Note: table_rows is an estimate for InnoDB and typically exact for MyISAM. For frequently requested totals in high-traffic systems, consider maintaining a lightweight counter table updated by the application or triggers, or refresh counts on a schedule. Also remember engine semantics: InnoDB uses MVCC snapshots for consistent reads while MyISAM keeps stored row counts.

Recommended Answers

All 4 Replies

Member Avatar for d5e5 
select count(*) from (select 1 from t1 UNION ALL select 1 from t2) as both_tables;
Member Avatar for Acute

Thank you very much! And can you explain what "1" does mean in your select?

Member Avatar for griswolf

It provides the constant 1 for each row that matches the where clause. There is no where clause, so it gives a 1 for each row in the table.

Member Avatar for Acute

Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.