Crazy that there are no in-depth answers with some EXPLAINs and profiling. Just mentions to ANSI standards.
EDIT: I realized I completely misread the question. I thought it was asking the difference of using a WHERE clause or putting conditionals in the JOIN (see my reply above for an example). The original SO questions is about an old-school CROSS JOIN `table1,table2` (which is a cartesian product in relational algebra) and a JOIN.
Edit 2: "old school" in the sense of joining tables before JOINs existed. I think it was System R the first engine to introduce efficient JOINs vs cross products.
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.
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;
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';
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.
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.
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.
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.
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.
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.
>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.
EDIT: I realized I completely misread the question. I thought it was asking the difference of using a WHERE clause or putting conditionals in the JOIN (see my reply above for an example). The original SO questions is about an old-school CROSS JOIN `table1,table2` (which is a cartesian product in relational algebra) and a JOIN.
Edit 2: "old school" in the sense of joining tables before JOINs existed. I think it was System R the first engine to introduce efficient JOINs vs cross products.