6 ms·

Is a query not a relation?

naasking 4y ago
Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate < '2000-01-01'
- noloblo 4y ago
  Exactly since it declarative the style Lends itself using stored procedure calls to become composable
  - naasking 4y ago
    You cannot abstract over stored procedures either, so that's still not composable.
    - dventimi 4y ago
      No idea what this means
      - naasking 4y ago
        Think of first-class functions. Can't do that with stored procedures, just like you can't do that with queries or tables in SQL.
  - alphanumeric0 4y ago
    Furthermore, stored procedures/functions are not queries.
    - dventimi 4y ago
      Stored procedures can be relations. Queries are relations. Ergo, stored procedures can be queries.
- Lemaxoxo 4y ago
  Isn't that the point of common table expressions (CTEs)?
  - naasking 4y ago
    See: https://news.ycombinator.com/item?id=35058927 https://news.ycombinator.com/item?id=35058927
- datan3rd 4y ago
  with persons as (select * from Person) select Name from persons where Birthdate < '2000-01-01
  - naasking 4y ago
    Where is the assignment to a variable? Where can you construct a query using a variable in table/query position? That's the whole point of being first class and composable, a query becomes like any other value so you should be able to parameterize any query by another query assigned to a variable that may have been set inside an if-statement, or accepted as a parameter to a stored procedure. You know, the same kinds of composition we see in ordinary programming languages.
    - chrstr 4y ago
      create table x as (select * from person); select name from x where ...; there you go, just configure your editor to display "create table x" as "declare x = " ;) or even a version with lazy evaluation: create view x as (select * from person); select name from x where ...;
      - alphanumeric0 4y ago
        What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?
        dventimi 4y ago
        If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.
        alphanumeric0 4y ago
        That's a good point. This would rule out SQLite for me.
- Foobar8568 4y ago
  You can do composition through CTE or Table-Valued Function.
  - naasking 4y ago
    CTE and TVF still treat tables as second class citizens, so while they enable some forms of composition, they're still very restricted. This has been the consistent story with SQL, 15 ways to use queries and tables, all to work around the limitation that they are not first class values.

Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate < '2000-01-01'

noloblo 4y ago
Exactly since it declarative the style Lends itself using stored procedure calls to become composable
- naasking 4y ago
  You cannot abstract over stored procedures either, so that's still not composable.
  - dventimi 4y ago
    No idea what this means
    - naasking 4y ago
      Think of first-class functions. Can't do that with stored procedures, just like you can't do that with queries or tables in SQL.
- alphanumeric0 4y ago
  Furthermore, stored procedures/functions are not queries.
  - dventimi 4y ago
    Stored procedures can be relations. Queries are relations. Ergo, stored procedures can be queries.
Lemaxoxo 4y ago
Isn't that the point of common table expressions (CTEs)?
- naasking 4y ago
  See: https://news.ycombinator.com/item?id=35058927 https://news.ycombinator.com/item?id=35058927
datan3rd 4y ago
with persons as (select * from Person) select Name from persons where Birthdate < '2000-01-01
- naasking 4y ago
  Where is the assignment to a variable? Where can you construct a query using a variable in table/query position? That's the whole point of being first class and composable, a query becomes like any other value so you should be able to parameterize any query by another query assigned to a variable that may have been set inside an if-statement, or accepted as a parameter to a stored procedure. You know, the same kinds of composition we see in ordinary programming languages.
  - chrstr 4y ago
    create table x as (select * from person); select name from x where ...; there you go, just configure your editor to display "create table x" as "declare x = " ;) or even a version with lazy evaluation: create view x as (select * from person); select name from x where ...;
    - alphanumeric0 4y ago
      What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?
      - dventimi 4y ago
        If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.
        alphanumeric0 4y ago
        That's a good point. This would rule out SQLite for me.
Foobar8568 4y ago
You can do composition through CTE or Table-Valued Function.
- naasking 4y ago
  CTE and TVF still treat tables as second class citizens, so while they enable some forms of composition, they're still very restricted. This has been the consistent story with SQL, 15 ways to use queries and tables, all to work around the limitation that they are not first class values.