6 ms·

The thing about #2, is poorly designed in comparison to what? Chances are high the application layer is written in JavaScript, PHP, Ruby, or Python. We don't e

The thing about #2, is poorly designed in comparison to what? Chances are high the application layer is written in JavaScript, PHP, Ruby, or Python. We don't even talk about nasty edge cases in those languages because they are uncountable.

jasonhansel 4y ago
Those languages all have first-class functions and OOP-style encapsulation. In SQL stored procedures (at least in Postgres), you can't have a variable that holds multiple records. You can't even define a variable inside of a block.
- ttfkam 4y ago
  DECLARE foo record[];
- nhinck2 4y ago
  Temp tables?
lmm 4y ago
None of those languages is as unpleasant as SQL. Not even PHP.
- billythemaniam 4y ago
  For me, I would much rather SQL especially when using DBT too to manage the queries like any other language. I thought the same way as you and SQL certainly has its warts, but I have grown to appreciate it's elegance. I think over the long-term it is easier to maintain because it is so concise and the most common second language among programmers.
- ttfkam 4y ago
  Once you start thinking in sets instead of collections of independent statements as per PHP et al, SQL starts looking a lot better. If you try to fit SQL into the general purpose programming language box, you're gonna have a bad time. A really bad time. SQL is a DSL for set theory. Nothing more. Nothing less.
  - lmm 4y ago
    The things that are bad about SQL have nothing to do with set theory. The non-orthogonal syntax. The terribly limited and opaque type system. Ternary logic that fails silently. The utter lack of composability or testability.
    - ttfkam 4y ago
      CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there. Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an unknown value make sense in that context? Many times not, and the column should either not be nullable or should be referenced in a different table by a foreign key. 3 = NULL "Is 3 equal to this unknown value?" Maybe yes. Maybe no. It's unknown. Therefore the answer to "3 = NULL" is NULL. The answer is also unknown. Not true. Not false. Unknown. IS NULL or IS NOT NULL, but never = NULL or <> NULL. It may be unusual to someone coming from a general purpose programming language's notion of null as a (known) missing value, but that doesn't make it wrong. It means you need to reorient your mind toward set theory, where NULL means "unknown" if you're going to work with SQL and relational databases in general. Folks often speak of the impedance mismatch between relational models and in-memory object models. NULL is one of those mismatches.
      - fomine3 4y ago
        CTEs are quite composable. -- on SQL land. I wish SQL is more composable
      - lmm 4y ago
        > CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there. > Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an unknown value make sense in that context? Many times not, and the column should either not be nullable or should be referenced in a different table by a foreign key. Cool, now how do I do these two incredibly basic things at the same time and make something not nullable or referencing another table in a CTE?

jasonhansel 4y ago

Those languages all have first-class functions and OOP-style encapsulation. In SQL stored procedures (at least in Postgres), you can't have a variable that holds multiple records. You can't even define a variable inside of a block.

ttfkam 4y ago
DECLARE foo record[];
nhinck2 4y ago
Temp tables?

lmm 4y ago

None of those languages is as unpleasant as SQL. Not even PHP.

billythemaniam 4y ago
For me, I would much rather SQL especially when using DBT too to manage the queries like any other language. I thought the same way as you and SQL certainly has its warts, but I have grown to appreciate it's elegance. I think over the long-term it is easier to maintain because it is so concise and the most common second language among programmers.
ttfkam 4y ago
Once you start thinking in sets instead of collections of independent statements as per PHP et al, SQL starts looking a lot better. If you try to fit SQL into the general purpose programming language box, you're gonna have a bad time. A really bad time. SQL is a DSL for set theory. Nothing more. Nothing less.
- lmm 4y ago
  The things that are bad about SQL have nothing to do with set theory. The non-orthogonal syntax. The terribly limited and opaque type system. Ternary logic that fails silently. The utter lack of composability or testability.
  - ttfkam 4y ago
    CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there. Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an unknown value make sense in that context? Many times not, and the column should either not be nullable or should be referenced in a different table by a foreign key. 3 = NULL "Is 3 equal to this unknown value?" Maybe yes. Maybe no. It's unknown. Therefore the answer to "3 = NULL" is NULL. The answer is also unknown. Not true. Not false. Unknown. IS NULL or IS NOT NULL, but never = NULL or <> NULL. It may be unusual to someone coming from a general purpose programming language's notion of null as a (known) missing value, but that doesn't make it wrong. It means you need to reorient your mind toward set theory, where NULL means "unknown" if you're going to work with SQL and relational databases in general. Folks often speak of the impedance mismatch between relational models and in-memory object models. NULL is one of those mismatches.
    - fomine3 4y ago
      CTEs are quite composable. -- on SQL land. I wish SQL is more composable
    - lmm 4y ago
      > CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there. > Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an unknown value make sense in that context? Many times not, and the column should either not be nullable or should be referenced in a different table by a foreign key. Cool, now how do I do these two incredibly basic things at the same time and make something not nullable or referencing another table in a CTE?