I'm bad at pictures, so how about a step-by-step description of the query evaluation?

(Note that this describes the LOGICAL queury evaluation - the physicial processing order may be completely different, as long as the same end result is achieved)

Query evaluation always starts with the FROM clause, and unless there are parentheses or nested joins, always works left to right. So the first step is the join of service and doc_serv. That's a standard inner join, with this internal table as intermediate result:

service_id name price doctor_id service_id

1 serv1 1000 2 1

2 serv2 500 3 2

2 serv2 500 1 2

3 serv3 700 1 3

3 serv3 700 3 3

4 serv4 1200 2 4

The next step is the join with doctors. This is a right outer join, so the right table (doctors) is preserved. If any row from the doctors table would not survive the result of an inner join on the same condition, it will still be there, but with a NULL in all the columns from the left table (the intermediate result above). Let's first look at how an inner join with the same conditions would look:

service_id name price doctor_id service_id doctor_id name

2 serv2 500 3 2 3 doc3

2 serv2 500 1 2 1 doc1

3 serv3 700 1 3 1 doc1

3 serv3 700 3 3 3 doc3

All rows in the intermediate table have a matching row in the doctors table, so no rows are eliminated because of that requirement. But rows in the intermediate table with a price of 1000 or more are lost. And as a result, we only have rows for two of the four doctors. The outer join says that all doctors have to be retained, so rows for the missing doctors are added back in:

service_id name price doctor_id service_id doctor_id name

2 serv2 500 3 2 3 doc3

2 serv2 500 1 2 1 doc1

3 serv3 700 1 3 1 doc1

3 serv3 700 3 3 3 doc3

NULL NULL NULL NULL NULL 2 doc2

NULL NULL NULL NULL NULL 4 doc4

This would be the result if the query would ask for SELECT *. There are no WHERE, GROUP BY, or HAVING clauses, so the next step is the SELECT list. This asks for an aggregate. Without a GROUP BY, that means aggregate over the whole result set. The rows are counted and the result (6) is returned.