Select count(*) from multiple tables

Count on the side, please!

Sometimes, you may prefer to display the count from each table in a separate column, organized neatly in a single row for easy comparison. Nested SELECT COUNT(*) statements make this possible, using AS for a more readable output. Besides, notice that FROM dual from the Oracle universe jumps in here to get things done:

SELECT 
  (SELECT COUNT(*) FROM table1) AS count1,  -- Counting apples on bucket 1 🍎
  (SELECT COUNT(*) FROM table2) AS count2,  -- Counting bananas on bucket 2 🍌
  (SELECT COUNT(*) FROM table3) AS count3   -- Counting grapes on bucket 3 🍇
FROM dual;  -- Apparently, 'dual' is Oracle's imaginary friend!


Nothing fits together like a well-organized count

Common table expressions (CTEs) simplify your queries by organizing sequential computations and enabling you to generate more comprehensive results. Let's peek at how CTEs count rows in multiple tables:

WITH TableCounts AS (
    SELECT 'Table1' AS TableName, COUNT(*) AS Total FROM table1  -- Counting apples again 🍎
    UNION ALL
    SELECT 'Table2', COUNT(*) FROM table2  -- Here come the bananas 🍌
    UNION ALL
    SELECT 'Table3', COUNT(*) FROM table3  -- Oh grapes, not you again! 🍇
)
SELECT TableName, Total FROM TableCounts;  -- The final showdown!


Now, we are getting relational

It often requires relational thinking and some JOINs to count rows when our tables share a common column or key. Bear in mind, the complexity of such a count can vary depending on the relationships between your tables. It's like a family reunion, you need to know who is related to whom.

Navigating syntax and performance

Given performance considerations and syntax compatibility, selecting a method for a count(*) operation is like choosing the appropriate wrench from your toolbox. You gotta pick the right tool for the job!

Remember, a subquery is a hungry beast and count(*) is its favorite meal. The faster you can feed it, the better. Options such as COUNT(1) or choosing a specific indexed column can have better performance depending on the specifics of your database environment.

Consolidating the picture

People often need to have all the counts in a single row for a better comparison or inclusion in a wider report. Nested SELECT statements or CTEs are your go-to tools here, depending on whether you want one row or multiple rows. In short, it's a stylish way of saying, 'Hey! Look at my buckets side by side!'