3 ms·

> you join tables A to B to C to D -> SQL engine has to create AxBxCxD number rows this isn't really true. not every join is a cross join, and not every join e

by gfody 6y ago

> you join tables A to B to C to D -> SQL engine has to create AxBxCxD number rows this isn't really true. not every join is a cross join, and not every join even changes the grain of the result set. it is important to pay attention when your joins do change the grain of the result set, especially when they grow it by some factor - then it's more like a product as you say.

slt2021 6y ago
all queries with JOINs can be rewritten as cartesians: select * from a join B on col1=col2 will be rewritten by query optimizer into: select * from A, B /* this is cartesian that slows down your query */ WHERE a.col1=B.col2 in fact both queries produce the same execution plan if you check yourself
- gfody 6y ago
  the query is not the only input into the plan generator, the other inputs are the schema (with constraints and indexes) and their statistics - so you can't really say "check the plan for yourself" without all the inputs. and the only time the plan for "select * from A, B" is going to be the same as "select * from A, B where A.col1=B.col2" is when col1 and col2 are both neither primary keys nor foreign keys and that would be a weird condition under which to run such a query.
  - slt2021 6y ago
    pls reread my post, I wrote that query select * from A join B on col1=col2 is the the same plan as for select * from a, b where col1=col2 this is true regardless of schema and statistics, it is simply how query planner works under the hood and you can check it yourself on any database
    - iaabtpbtpnn 6y ago
      Those are both inner joins, just a different syntax for writing them.
      - ohlookabird 6y ago
        They might be logically equivalent, but they are not identical if more than two tables are joined. For instance, the current Postgres docs say [1]: > This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second. [1] https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN https://www.postgresql.org/docs/current/queries-table-expres...

by gfody 6y ago

slt2021 6y ago

all queries with JOINs can be rewritten as cartesians: select * from a join B on col1=col2 will be rewritten by query optimizer into: select * from A, B /* this is cartesian that slows down your query */ WHERE a.col1=B.col2 in fact both queries produce the same execution plan if you check yourself

gfody 6y ago
the query is not the only input into the plan generator, the other inputs are the schema (with constraints and indexes) and their statistics - so you can't really say "check the plan for yourself" without all the inputs. and the only time the plan for "select * from A, B" is going to be the same as "select * from A, B where A.col1=B.col2" is when col1 and col2 are both neither primary keys nor foreign keys and that would be a weird condition under which to run such a query.
- slt2021 6y ago
  pls reread my post, I wrote that query select * from A join B on col1=col2 is the the same plan as for select * from a, b where col1=col2 this is true regardless of schema and statistics, it is simply how query planner works under the hood and you can check it yourself on any database
  - iaabtpbtpnn 6y ago
    Those are both inner joins, just a different syntax for writing them.
    - ohlookabird 6y ago
      They might be logically equivalent, but they are not identical if more than two tables are joined. For instance, the current Postgres docs say [1]: > This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second. [1] https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN https://www.postgresql.org/docs/current/queries-table-expres...