On: INNER JOIN ON vs WHERE clause (2009)

[santiagobasulto]

I did a quick test in Postgres using the sample Airlines database.

Here are the two tested queries:

Query 1:

    SELECT 
        t.passenger_name, 
        t.ticket_no, 
        bp.seat_no
    FROM 
        Flights f
    JOIN 
        Ticket_flights tf ON f.flight_id = tf.flight_id
    JOIN 
        Tickets t ON tf.ticket_no = t.ticket_no
    JOIN 
        Boarding_passes bp ON t.ticket_no = bp.ticket_no AND tf.flight_id = bp.flight_id
    WHERE 
        f.arrival_airport = 'OVB';

Query 2:

    SELECT 
        t.passenger_name, 
        t.ticket_no, 
        bp.seat_no
    FROM 
        Flights f
    JOIN 
        Ticket_flights tf ON (f.flight_id = tf.flight_id AND f.arrival_airport = 'OVB')
    JOIN 
        Tickets t ON tf.ticket_no = t.ticket_no
    JOIN 
        Boarding_passes bp ON t.ticket_no = bp.ticket_no AND tf.flight_id = bp.flight_id

Then I ran EXPLAIN for both of them and the query plan is THE same. So there's not a big difference at least in Postgres.

Here's the GPT conversation: https://i.imgur.com/dIzcfnc.jpeg

It doesn't let me share it because it contains an image

[shrx → santiagobasulto]

To me this is way more readable:

    SELECT 
        t.passenger_name, 
        t.ticket_no, 
        bp.seat_no
    FROM 
        Flights f, 
        Ticket_flights tf, 
        Tickets t, 
        Boarding_passes bp
    WHERE 
        f.flight_id = tf.flight_id 
        AND f.arrival_airport = 'OVB'
        AND tf.ticket_no = t.ticket_no
        AND t.ticket_no = bp.ticket_no
        AND tf.flight_id = bp.flight_id;

[wild_egg → shrx]

No love for JOIN ... USING in this thread eh

    SELECT 
        t.passenger_name, 
        t.ticket_no, 
        bp.seat_no
    FROM Flights f
    JOIN Ticket_flights tf USING (flight_id),
    JOIN Tickets t USING (ticket_no),
    JOIN Boarding_pass bp USING (ticket_no, flight_id)
    WHERE f.arrival_airport = 'OVB';

[goodlinks → wild_egg]

Never seen this before, always thought it would be tasty sugar though.. thanks for making me aware of it!

[yen223 → wild_egg]

`using` works really well, but only when the two column names are the same.

That's why it's not a bad idea to include the table name in the id column name: `flight.flight_id` instead of `flight.id`.

[santiagobasulto → wild_egg]

For me, idk why, it feels too "ORACLE-ly". I stopped using Oracle after administering an 9i until ~2010 and I never want to go back

But yes, `USING` is convenient and pleasant to the eyes.

[Suppafly → wild_egg]

I've never seen USING before, is that available in mssql or just the various open source ones?

[yen223 → Suppafly]

It doesn't look like mssql (aka sql server) supports USING.

[wild_egg → Suppafly]

I haven't used mssql since ~2013 but I think you're right that it's not available there.

Works great in MySQL, PostgreSQL, and SQLite though.

[dhc02 → wild_egg]

I like this and haven't used it before. Thanks for sharing.

[pophenat → shrx]

To me placing the join predicates immediately after the tables is more readable as I don’t have to switch between looking at the from and where clauses to figure out the columns on which the tables are joined.

[buttercraft → pophenat]

Yep, nothing is harder to read than joins scattered in random order throughout the where clause.

Additionally, putting joins in the where clause breaks the separation of concerns:

FROM: specify tables and their relationships

WHERE: filter rows

SELECT: filter columns

[Suppafly → shrx]

I guess as long as you're giving it some criteria to join on, I had a coworker do these sorts of joins but never specified any real criteria for joining and the queries were always a mess and returned tons of extra junk. Personally I prefer to explicitly do the joins first.

[mmcdermott → shrx]

I've usually found that this breaks down when there are a lot of filtering conditions besides the join condition, and multiple columns used in the joins. The WHERE clause gets long and jumbled and it is much easier to separate join conditions from filtering conditions.

[notachatbot123 → santiagobasulto]

> Then I ran EXPLAIN for both of them and the query plan is THE same.

*according to a LLM. Did you verify this?

[sgarland → notachatbot123]

My god, this is where we’re at? Asking an LLM to hallucinate a schema and the resultant EXPLAIN plans for given queries?

Postgres is incredibly easy to spin up in a container to test things like this, and the mentioned schema (Postgres Air) is also freely available.

[santiagobasulto → notachatbot123]

Guys, I ran an EXPLAIN in a dockerized postgres server. You can see it in the screenshot I shared. Why did you assume I was just trusting the LLM? Jeez, Hn.

[notachatbot123 → santiagobasulto]

I mean the actual EXPLAINs. Were they actually exactly 100% the same?

[jgalt212 → notachatbot123]

seriously. I'd never ask an LLM a question I had no idea what the answer was.

[0cf8612b2e1e → notachatbot123]

Glad you pointed this out. I was assuming author ran an actual explain. Not that the LLM made a guess.

[serpix → santiagobasulto]

Both examples are (to my delight) using aliased table names for all columns which is already a major step up in readability.

[Izkata → serpix]

I tend to find table aliases a step down in readability, and only use them as necessary, because now your eyes have to jump up and down to see where the columns come from.

[ausp → Izkata]

The aliases don't force you to follow each one through; eyes can do no jumping if you like in either case.

But if you can't infer from the column name which table they will come from, I find having the option to check far more preferable to that of having no way of knowing.

[rbanffy → ausp]

I think the aliases in the example are very intuitive - it's easy to correctly guess where they come from.

[Suppafly → Izkata]

>I tend to find table aliases a step down in readability

I suppose it depends on your database, one of the ones I work with all the time has crazy long table and view names and aliases make resulting SQL more readable.

[kragen → santiagobasulto]

That's a really impressive GPT conversation; I appreciate you sharing it!