SQL exists in subquery with where clause

Question

I don't really understand why do we receive here as a result the list of employees from SF and which exists in offices table.

1. Exists goes through the subquery and the result is 0 or 1 - there is a row with the criteria in subquery where or not.
2. If the result is 0 or 1 then WHERE in the main query receives just 1 or 0, WHERE 1 - meaning retrieve all rows.
3. The query works fine, but I don't understand why

SELECT 
    employeenumber, 
    firstname, 
    lastname, 
    extension
FROM
    employees
WHERE

    EXISTS( 
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco' AND 
           offices.officeCode = employees.officeCode)

;

Answer 1

This subquery is a correlated subquery in that each row relates back to the main query's row by way of the officeCode.

It's saying "For this officeCode of this employee record, find that officeCode in offices and if you find that city for that office is in "San Francisco" then return something" that something in this case is a 1 but it could be the string "butthead" and it would work just fine. If something is returned, then the record makes it into the result set of the query. If nothing is returned, then the record does not make it into the result set.

It will return the same as:

SELECT 
    employee.employeenumber, 
    employee.firstname, 
    employee.lastname, 
    employee.extension
FROM employee
    INNER JOIN offices
        ON employee.officeCode = offices.officeCode
WHERE offices.city = 'San Francisco';

The difference being that in this query, the columns from offices are candidates for being returned in the SELECT clause.