EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.
FROM tablename t SELECT t.<press tab>
and some form of autocomplete mechanism, either prefills all the column names from table "t" or suggests the list of columns and/or types associated with it.
This is much better than having to: 1. Run a SELECT with LIMIT statement just to get an idea of the layout. 2. Point and click through the IDE treeview.
Honestly, I don't think it helps a whole lot beyond this functionality, but I can see why folks who are accustomed to thinking in functional pipelines (from -> select -> map -> filter -> collect) can prefer this way of querying.
I think PRQL is one attempt at building something this way.[1]
But I think that’s not a big deal at all, and the SQL approach has a certain advantage in putting the type of action (select/update/delete/drop etc) front and centre, which is really quite helpful.
If you use PostgreSQL then you can use \d instead.
I'm sure the other RDBMSes have their own equivalent (except for maybe SQLite but if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong).
.schema
> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong
That's an uninformed statement. SQLite is extremely solid production quality code. Of course it's not the universally applicable database solution, nothing is. Sometimes you need Cassandra or its kind, often MySQL|Postgres, other times SQLite is the correct answer.
https://www.sqlite.org/mostdeployed.html
> Every Android device
> Every iPhone and iOS device
> Every Mac
> Every Windows 10 machine
> Every Firefox, Chrome, and Safari web browser
> Every instance of Skype
> Every instance of iTunes
> Every Dropbox client
> Every TurboTax and QuickBooks
> PHP and Python
> Most television sets and set-top cable boxes
> Most automotive multimedia systems
> Countless millions of other applications
Look at all those toys.
If you ponder the importance of proper (robust, reliable, dependable) data management for data that keeps nuclear plants going, for farmaceutical research data, for anything happening on the financial markets, for medical records, for data concerning payroll and the like, etc. etc. then you might appreciate that all the stuff mentioned in the list is indeed really "just toys".
FROM table SELECT
And when done switch the code to be correct?
WITH (combine a whole bunch of stuff) as dataset SELECT a, b, c FROM dataset
This approach just says:
FROM tables... WHERE ... SELECT a, b, c
To me, it does make a lot of sense. This is also the paradigm that some of the graph databases use.
Exactly! This is where SQL hurts me the most: not being able to store (partial) query expressions in variables for later reuse. The only way to do this is by creating explicit views (requires DDL permissions) or executing the partial query into a temporary table (which is woefully inefficient for obvious reasons).
This is what I'd what to do if common table expression really were common:
SELECT c1, c2
FROM DifficultJoinStructure
AS myCte;
WITH myCte
SELECT c1, c2
WHERE SomeCondition(c1);
WITH myCte
SELECT c1, c2
WHERE SomeCondition(c2);Granted most environments effectively treat views as DBA/Sysadmin owned objects, especially where end users/apps are effectively sharing one, or a small number, of user accounts.
But given user=schema aspect several of the traditional databases, I get the impression the original intent might have been a little more laissez fair?
Of course the same can be said for tables, and that was perhaps a little idealistic!
Views aren't always quite as composable as you'd like either, or maybe I'm just scarred by the particular DB engines I use most.
So I actually agree with you, but unfortunately SQL requires that the "WELL AKSHWELLY" be followed by one or more "BUT" clauses.
No, is fundamental issue to the language!
The relational model is clear. You START with a relation and then compose with relational operators that return relations.
ie:
rel | project
And this fundamental issue with the language goes deeper. The rules are ad-hoc for each sub-operator despite the fact using relational model MUST make it simply to compose.
So, you have rules for HAVING, GROUP BY, ORDER BY, WHERE, SELECT and so on and none are like the others, are different in small but annoy ways...
Also when reading a statement, you're mostly interested in what the returned fields are rather details like where they came from or how they're ordered. It kind of makes sense to put it at the start.
Maybe other syntax forms have their benefits, specially when writing, but I don't think SQL's choice is completely senseless either.
Only *IN SQL*.
You don't need it on the relational model, heck, no even in any other paradigm:
1
So this:
SELECT * FROM foo
foo
foo WHERE .id = 1
foo ORDER BY .id
foo ORDER BY .id WHERE .id = 1 //Note this is not valid in SQL, but because SQL is wrong!
But now, when you see it this way, you note how MUCH nicer and simpler it could have been, because then each "fragment" of a SQL query could become *composable*.
But not on SQL, where the only "composition" is string concatenation, that is bad as you get.
It's actually useful to the person reading the code. It clearly defines where a statement starts, what it does and makes reading a query close to reading English. Show a SELECT FROM WHERE query to someone who does not know SQL and the person will understand it. It might be a bit harder if you remove the SELECT.
And this is another example of the ad-hoc problems of SQL: query terminators (;) are optional. If they weren't, there would be no abiguity where a statement would start: it's the first word after the previous terminator.
Oh! Is super-ambiguous! Make the parser and enjoy it!
Lets make this more concrete:
city SELECT id
city ORDER id
city id <-- Order or select???
city city city
id FROM city ORDER BY id
id FROM (city WHERE elevation > 1000)Following their stupid English syntax, but using the proper verbs it should rightfully be "PROJECT x, y FROM foo SELECT WHERE a = b"
`FooId, name, date, favorite_color, active`
Now, you want to pull the ID for the latest `foo` for a specific date, but you don't know any of the column names.
The modern workflow looks like this
You write
`SELECT * FROM Foo`
then you say, "Ok, now I can get autocomplete"
`SELECT FooId FROM Foo`
"Ok, now I can write the where clause"
`Select FooId From Foo WHERE date=?`
It becomes an exercise in moving the cursor around just to get the autocomplete going.
If you are really familiar with the schema, not a problem. But if you just remember a few details about it, then you are stuck in this weird back and forth cursor moving thing.
That's why it'd be more ergonomic to have something like
`From Foo Select FooId where date=?`
Because you never need to move your cursor and you could get all the autocomplete you need at the right times.
This becomes especially true when writing joining statements
FROM Foo f
JOIN Bar b ON f.FooId = b.FooId
SELECT f.FooId
WHERE b.active = 1you start to type SELECT some, columns and can't get autocomplete until you add FROM afterwards, so you either type the query inside out (SELECT FROM table and go back to after SELECT) or just give up.
It is fundamental to the language. The evaluation order is from,where,group by, having, select, order by, limit.
Everything in perfect order is, select except.