It looked very useful and intuitive at first, but on further thought, I think the only time that you'd truly benefit from it if you SELECT *. For other SELECT queries with explicit field name list, you'd need to know the table the key constraint links to anyway. SQL Views serve perfectly well for queries encouraged by the schema itself, and i think they are more sophisticated and practical way.
There is similar comment in the thread, suggesting both tables should always be specified.
Not sure what I prefer yet. The idea is the foreign key name is usually the same as the referenced table, so should be an infrequent problem.
Personally, I think SQLs verbose syntax is a good thing, and increases clarity/interpretability. It reduces cognitive load to have things explicitly listed out. Not having the table and column names in the query itself makes it much harder to read and understand queries without prior knowledge of the data model.
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
I had a similar suggestion the other day[1], except I chose what to me sounded more English-sounding:
JOIN films f USING FOREIGN KEY
Here the explicit foreign key, films_did_fkey in this case, could be specified after FOREIGN KEY. This would be similar in syntax to when you force an index for a select statement, at least in the DB we use.
At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself . Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "…
> Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. > So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of…
The person you are responding to was more direct/harsh than was necessary, but I think it would be good to step back for a moment and reflect on the feedback from this community. You said "If someone can convince me this is a bad idea, that would help me forget about all of this, so I would greatly appreciate your thoughts, no matter how negative or positive." I think there are enough valid objections here to at least consider the idea that this is not a clear improvement?
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).
Simple, effective, and really useful in a lot of situations. I like it!
Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.
Can't you use an alias in a group by?
Not by the SQL standard, since projection (which creates aliases) happens after aggregation. There are many databases that allow it as an extension, though.