postgresql: several tables in from statement

user_3014866 0
Viewed 50

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?

subquery inner-join where-clause postgresql sql
1 Answers

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
)
user_106767160
Related