3 ms·
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
by santiagobasulto 2y ago
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 https://i.imgur.com/dIzcfnc.jpeg
It doesn't let me share it because it contains an image
- serpix 2y agoBoth examples are (to my delight) using aliased table names for all columns which is already a major step up in readability.
- Izkata 2y agoI 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 2y agoThe 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 2y agoI think the aliases in the example are very intuitive - it's easy to correctly guess where they come from.
- Suppafly 2y ago>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.
- shrx 2y agoTo 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 2y agoNo 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';
- dhc02 2y agoI like this and haven't used it before. Thanks for sharing.
- goodlinks 2y agoNever seen this before, always thought it would be tasty sugar though.. thanks for making me aware of it!
- Suppafly 2y agoI've never seen USING before, is that available in mssql or just the various open source ones?
- kragen 2y agoThat's a really impressive GPT conversation; I appreciate you sharing it!
- notachatbot123 2y ago> Then I ran EXPLAIN for both of them and the query plan is THE same. *according to a LLM. Did you verify this?
- sgarland 2y agoMy 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.
- deleted 2y ago[deleted]
- jgalt212 2y agoseriously. I'd never ask an LLM a question I had no idea what the answer was.
- 0cf8612b2e1e 2y agoGlad you pointed this out. I was assuming author ran an actual explain. Not that the LLM made a guess.
- santiagobasulto 2y agoGuys, 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 2y agoI mean the actual EXPLAINs. Were they actually exactly 100% the same?