11–20 of 206 posts
SQL Views serve perfectly well for queries encouraged by the schema itself, and i think they are more sophisticated and practical way.
This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. I…
yeah, this saves like 10 characters of text in exchange for massive confusion.
The syntax proposal i O(1) syntax wise compared to O(2n) for a JOIN ON where n is the number of columns.
I dig it! I prefer defining tables like this: CREATE TABLE category ( id int GENERATED ALWAYS AS IDENTITY, name text ); CREATE TABLE post ( id int GENERATED ALWAYS AS IDENTITY, category_id int REFERENCES category (id) ON DELETE CASCADE ); That is, category.id rather than category.category_id. But the USING clause doesn't work with that style, as far as I understand. This would make my queries nicer.
What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).
- BQ https://cloud.google.com/bigquery/docs/reference/standard-sq...
- CH https://clickhouse.com/docs/en/sql-reference/statements/sele...
This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. I…
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM films f
JOIN distributors d ON FOREIGN f.films_did_fkey
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d
JOIN films f ON FOREIGN films_did_fkeyThis syntax change means that this solution can't be used because you have no idea what random queries out there might rely on the specific existence of a foreign key constraint for the definition of the query. Thereby meaning that if a foreign key constraint becomes a performance problem, we're stuck with it rather than having a solution.
Features have consequences. And I don't like the consequences of making business rules that are now explicit in the query, be instead implicit in the table design.
This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. I…
Agreed. Maybe something like this instead: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN distributors d ON FOREIGN f.films_did_fkey and SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d JOIN films f ON FOREIGN films_did_fkey
This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. I…
I think your concern is addressed by the idea further down in proposal; giving the foreign keys the same names as the referenced tables. This example was provided: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.distributors d
If you got rid of the syntax that uses the underlying "magic" of needing to know which table the foreign key points to, I'd be more amenable.
Earlier quoted context omitted.
yeah, this saves like 10 characters of text in exchange for massive confusion.
In this simple example yes, but there can be multiple columns in a join. The syntax proposal i O(1) syntax wise compared to O(2n) for a JOIN ON where n is the number of columns.