If you exploring a set of tables you have never touched before, it really neat if you could just type in:
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]
In SSMS and almost certainly many other SQL editors Intellisense works on the select list as soon as you have a working from clause, the only caveat being you can’t autocomplete columns reading when writing a statement purely left to right.
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.
DESCRIBE TABLE is a command that pretty much does exactly this (explain what a table contains) and it's a part of MySQL.
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).
> 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.
It's well tested, but that's still not a guarantee that there are no security issues. Latest acknowledged CVE bug is a month old: https://www.sqlite.org/cves.html
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".
You're missing the point: until you have typed the FROM TableName t in your IDE, SELECT t.<press tab> can not do autocompletion. That has nothing to do with whether SQL supports querying metadata (the ANSI portable way would be through INFORMATION_SCHEMA btw, not DESCRIBE). It's a consequence of the brain not responding to think-ahead queries by the IDE.
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]
[1]. https://github.com/prql/prql