I will try to give a more canonical answer for beginners and to serve as a reference:

How an SQL query works

The following is a high-level description of how relational database queries work.

This means that the queries give a result as if they were following this process, although in practice the BD engines can do things differently internally.

Cartesian product

Of the tables involved in the query, the Cartesian product is made (all possible combinations)

Tabla A: 
   Registro 1: r1, d1, d2
   Registro 2: r2, d3, d4
   Registro 3: r3, d5, d6
Tabla B:
   Registro 1: s1, e1, r1
   Registro 2: s2, e2, r3

select * from tablaA, tablaB;

r1, d1, d2, s1, e1, r1
r2, d3, d4, s1, e1, r1
r3, d5, d6, s1, e1, r1
r1, d1, d2, s2, e2, r3
r2, d3, d4, s2, e2, r3
r3, d5, d6, s2, e2, r3

Record selection

With the WHERE clauses, or the ON clauses in the JOIN , you filter the records that meet a certain condition:

select * from tablaA, tablaB WHERE tablaA.campo1 = 'r1';

r1, d1, d2, s1, e1, r1
r1, d1, d2, s2, e2, r3

select * from tablaA join tablaB On tablaA.campo1 = tablaB.campo3;

r1, d1, d2, s1, e1, r1
r3, d5, d6, s2, e2, r3

Outer joins

A special type of joins are the outer join , in that if the records of one of the tables are not going to appear because no record of the other table does not meet the condition ON , the records appear without being "combined" "with no value

select * from tablaA left outer join tablaB On tablaA.campo1 = tablaB.campo3; // left, los registros de A siempre aparecerán

r1, d1, d2, s1, e1, r1
r2, d3, d4, null, null, null
r3, d5, d6, s2, e2, r3

Field selection

select tablaA.campo1, tablaA.campo2, tablaB.campo1 from tablaA, tablaB;

r1, d1, r1
r2, d3, r1
r3, d5, r1
r1, d1, r3
r2, d3, r3
r3, d5, r3

Aggregation functions

select count(*) from tablaA, tablaB;

6

select tablaA.campo1, count(*) from tablaA, tablaB;

r1, 2  // En el producto cartesiano hay dos filas con tablaA.campo1 = 'r1'
r2, 2
r3, 2

Example of the question.

You need a left outer join so that patients without "hours" appear. Patients without hours will appear with the fields of "hours" in null; so if you put a filter (in the WHERE or in the JOIN) that eliminates records, you can lose the rows that do not meet the condition and will no longer appear in the select.

The solution is not to filter rows (by doctor or state) and to do the checks in the aggregation functions:

select count(if(hr.medico_id=1 and hr.estado=4, 1, null)),
   p.nombre_completo
from paciente p
   left outer join horas hr on p.id = hr.paciente_id 
where hr.medico_id = 1 
   and hr.estado = 4
group by p.id
order by p.nombre_completo;

Try this solution to include a select within another select:

select count(hr.paciente_id) as total,
(select max(hr.fecha_hora) from horas hr where p.id = hr.paciente_id AND hr.medico_id = 1 AND hr.estado = 4) as fecha_hora,
p.nombre_completo
from pacientes p
left join horas hr on p.id = hr.paciente_id 
where hr.medico_id = 1  
group by p.id
order by p.nombre_completo;