postgresql: several tables in from statement

Question

I have seen such query:

SELECT a.city
FROM airports a
EXCEPT
SELECT arr.city
FROM airports dep, airports arr, flights f
WHERE dep.city = 'Москва'
AND f.departure_airport = dep.airport_code
AND f.arrival_airport = arr.airport_code;

I don't understand part FROM airports dep, airports arr, flights f - is it some kind of syntax sugar for full join?

Answer 1

These are implicit inner joins. The table names are enumerated in the from clause, and the join conditions are placed in the where clause.

This is much simpler expressed with standard, explicit joins:

SELECT arr.city
FROM airports dep
INNER JOIN airports arr ON f.arrival_airport = arr.airport_code
INNER JOIN flights f ON f.departure_airport = dep.airport_code
WHERE dep.city = 'Москва'

Side note: as I understand your question, you could express the whole query with not exists rather than except:

select a.city
from airports a
where not exists (
    select 1
    from flights f
    inner join airports adep on f.departure_airport = adep.airport_code
    where adep.city = 'Москва' and f.arrival_airport = a.airport_code
)