4 ms·

1. Why indent the joins? Are table2 and table3 less important than table1? Is table1 special? Is that why it enjoys privileged status in the from-clause? 2. Wh

1. Why indent the joins? Are table2 and table3 less important than table1? Is table1 special? Is that why it enjoys privileged status in the from-clause? 2. Why place some predicates in the where-clause and others in the join-clauses? What's the thinking here? Why not put all predicates up in the join-clauses, nearer to the tables that they affect?

83457 10y ago
I think of joins as operators and from as the block similar to and/or in the where clause. He is being inconsistent with line breaks for select, from and where blocks. Personally I want to be able to visually pick out the blocks of the query and the easiest way to do that is with indention imo.
- 1wd 10y ago
  If "joins" are operators like "and", and you put "and" at the end of the line, shouldn't you put "join" at the end too? from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else
  - 83457 10y ago
    well I don't put and/or at the end of lines so there is that... but I have seen people but joins at the end of lines
    - 1wd 10y ago
      sorry, mistook you for the top-level poster.
combatentropy 10y ago
> Why indent the joins? > Are table2 and table3 less important than table1? It's often arbitrary which tables are joined and which one is from'd. But the joins are part of the from-clause. They all join together into one big from. > Why place some predicates in the where-clause and > others in the join-clauses? What's the thinking here? > Why not put all predicates up in the join-clauses, > nearer to the tables that they affect? The join conditions are just to line up the rows of the different tables with each other, to avoid a cartesian product, to form one big table. This giant table is then filtered through the where-clause, like a funnel. You can put the filters in the joins, and I have in the past, but putting them in the where-clause better reflects the picture in my head. Tangentially, it would have been better if SQL had the select-clause after the from- and where-clauses: from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 <> t1.col4 select t1.col1, t2.col2, t3.col3 To understand the select-clause, I always first have to jump down to the from-clause anyway. This would also mirror the other statements: insert, update, and delete, which begin with the table names. This better reflects the flow of data. First you decide the source of data (which tables). Then you filter down to which records (which rows, the where-clause). Finally you determine which fields to get (which columns, the select-clause).

83457 10y ago

I think of joins as operators and from as the block similar to and/or in the where clause. He is being inconsistent with line breaks for select, from and where blocks. Personally I want to be able to visually pick out the blocks of the query and the easiest way to do that is with indention imo.

1wd 10y ago
If "joins" are operators like "and", and you put "and" at the end of the line, shouldn't you put "join" at the end too? from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else
- 83457 10y ago
  well I don't put and/or at the end of lines so there is that... but I have seen people but joins at the end of lines
  - 1wd 10y ago
    sorry, mistook you for the top-level poster.

combatentropy 10y ago

> Why indent the joins? > Are table2 and table3 less important than table1? It's often arbitrary which tables are joined and which one is from'd. But the joins are part of the from-clause. They all join together into one big from. > Why place some predicates in the where-clause and > others in the join-clauses? What's the thinking here? > Why not put all predicates up in the join-clauses, > nearer to the tables that they affect? The join conditions are just to line up the rows of the different tables with each other, to avoid a cartesian product, to form one big table. This giant table is then filtered through the where-clause, like a funnel. You can put the filters in the joins, and I have in the past, but putting them in the where-clause better reflects the picture in my head. Tangentially, it would have been better if SQL had the select-clause after the from- and where-clauses: from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 <> t1.col4 select t1.col1, t2.col2, t3.col3 To understand the select-clause, I always first have to jump down to the from-clause anyway. This would also mirror the other statements: insert, update, and delete, which begin with the table names. This better reflects the flow of data. First you decide the source of data (which tables). Then you filter down to which records (which rows, the where-clause). Finally you determine which fields to get (which columns, the select-clause).