Whenever you think you know everything about SQL you will soon find out that you don't. That's what I experienced recently when learning how you can turn any query, of any complexity, into a count-query that gives you just the number of records the original query would deliver.
In real life you need a count-query for pagination, i.e. for finding out how many database records are available in total. Only by means of such a count you can calculate the number of pages that a user can select from. The problem arises when the query is a huge join that may return a different result when you simply replace the attributes inside the select clause by a count(*). Besides, you don't want to duplicate a big complex query into a count-sister, do you?
So how to do this? It is a trick. But it works.
→ You make your big complex query a sub-select, that means you enclose it in parentheses. Then you prepend a select count(*) from, and append an arbitrary alias name, like e.g. X: select count(*) from (
-- any complex query goes here
) X -- the alias used to count
Assuming you have the following query ....
select e.name, c.name
from employee e inner join company c on c.employee_id = e.id
where c.deleted is null and c.state_id in
(select s.id from state s where s.reachable = 1)
.... and you want to know the total count of its possible result.
Then your count query looks like this (don't forget the trailing alias, it is needed):
select count(*) from (
select e.name, c.name
from employee e inner join company c on c.employee_id = e.id
where c.deleted is null and c.state_id in
(select s.id from state s where s.reachable = 1)
) X
This is now counting eggs instead of delivering records :-)
ɔ⃝ Fritz Ritzberger, 2019-05-16