Sadly, this is an area where Harbour is not as featured as VFP.
Let me first explain the concept of pseudo in-memory tables and local SQL syntax.
An in-memory table, also called "cursor" in VFP is basically the equivalent of a DBF table, but with the ability to have long field names, that only exist while there is at least one work area using it.
In VFP, cursors are mainly in memory, but if they are too large, will overflow to disk. Any commands that can be executed against a regular DBF table can also work on a cursor, including APPEND, REPLACE, DELETE, creating indexes, LOCATE, SEEK, etc.
Cursor can be created in 3 different ways.
Method 1. Calling the "CREATE CURSOR" command which is similar to "CREATE TABLE". A list of fields with their attributes can be specified in the command, or an array can be used as input. Even extended attributes, like support to nulls, autoincrement, long field names can be added. Using this method always create read/write tables.
Method 2. Calling the ODBC SQLConnect functions to fetch data from any ODBC backend, like PostgreSQL, MySQL, Advantage Database, even Microsoft SQL Server. By default, the cursor will be read only, but it is possible to make it also writable.
Method 3. Calling the VFP native SQL commands against DBF tables.
For example: SELECT fname,lname,dob,UPPER(lname+fname) AS tag1 FROM TABLE contacts ORDER BY tag1 INTO CURSOR ListOfContacts READWRITE
This is one of the most powerful features in VFP. The resulting cursor (in-memory table), ListOfContacts, can be used as any tables. You can create indexes on it, or even use it again in additional SELECT statements.
Imagine you use Method 2 getting some data from a PostgreSQL backend, then again Method 2 from a MySQL backend, than use Method 3, on the resulting cursors from the two previous Method 2 to refine or combine data into another cursor. In just a few line of code, you can compare data from multiple backends.
In Harbour, you could do Method 2 against an Advantage database, but the cursor is read-only, and you cannot reuse the table in more than one area, or execute additional SQL statements against it. Also, this requires a commercial, non-royalty-free license. In VFP, it is royalty-free, and that is most likely the reason why Microsoft killed VFP. The downfall of VFP was that it was too powerful and it was royalty-free.
But if we can add these features to Harbour, it would be better than all the other open source languages!
So now let's look into the SQL commands of VFP.
You could think of the SQL support of VFP as having SQLite backed in to the VFP language. Virtually all the SQL options are supported by VFP, like all type of JOINs, UNIONs, WHERE, HAVING, ORDER BY and more.
SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [, ...]
FROM [FORCE] Table_List_Item [, ...]
[[JoinType] JOIN DatabaseName!]Table [[AS] Local_Alias]
[ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...]
[WITH (BUFFERING = lExpr)]
[WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...]
[GROUP BY Column_List_Item [, ...]] [HAVING FilterCondition [AND | OR ...]]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, ...]]
[INTO StorageDestination | TO DisplayDestination]
[PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT]
But one of the most powerful options is the INTO clause. The result can be sent into a 2-dimensional array, a local physical table (dbf), or ideally in a cursor (in-memory table).
The array output has the major disadvantage that we lose the concept of field names, and that we cannot use any commands like "INDEX", "LOCATE", and SCAN (a while loop on the records).
The table output has the major disadvantage of restricting the fields names to 10 characters. This is especially an issue if you join multiple tables, and want to call the resulting field something like tablename_fieldname.
The best and most practical output are cursors. If the result is small enough, they can fit in-memory, if too big, will be cached to disk. The field names can be long, and you can do all the usual post-processing commands like indexing, searching, data replaces, and most importantly, you can use as input for more local SQL commands.
Also, any local functions you would create in your program, or any methods of any objects in scope, can be used in any of the expressions used to build fields in the JOIN, WHERE, ORDER BY, HAVING clauses.
This is also one of the major advantages of having native SQL command support in the language. This is like have access to STORED FUNCTIONS (SQL Server concept), created in your own language, locally, and not on a remote server. More and more developers are not using STORED PROCEDURES and STORED FUNCTIONS in backend database, since they are hard to maintain, platform-specific, and not supported by most ORMs (see note below).
Realistically, for us to implement this in Harbour, we will probably have to rely on SQLite and the very powerful RDD (Replaceable Database Driver) engine that Harbour already has. But we may have difficulties in supporting local functions in FIELD, WHERE, JOIN, HAVING clauses.
As a side note, it would be nice to add the SCAN/ENDSCAN syntax that VFP has to Harbour, natively to the language, and not simply via some precompiler #COMMAND tricks. The SCAN/ENDSCAN loop ensures that whenever the loop is processed again, that the current work area is on the one that started the SCAN commands. So whenever the LOOP command is called, the work area is also reset to the scope of the SCAN/ENDSCAN command.
What about the RDD that Harbour has? If you are a VFP developer, this is completely new. RDD stands for "Replaceable Database Driver". This is incredibly powerful! You can switch the database engine you rely on. In VFP, we are limited to DBF (FPT, CDX, NDX) tables, or old FoxBase files. In Harbour, you can switch to SQLite, MySQL, Advantage Database (commercial), PostgreSQL (not fully implemented, unless commercial), and in theory you could create your own drivers.
Many other languages decided not to use this approach, and virtualize data access via ORMs (Object-Relational Mapping). With this method, the result of SQL commands are always arrays or a collection of objects. The problem here is that you cannot really use the result as source for follow-up SELECT commands (FROM or JOINS). As a side note, Alaska-Software, to my knowledge, implemented some support to SQL in their Xbase++ commercial product, but they failed to finish sending output to cursors.
Please contact me, the author, if you would like to add to this subject.
ERRATA December 2019:
Harbour does support in-memory tables, but the entire content of the table must be in-memory, no spanning to disk for really large result. But Harbour can be compiled as a 64-bit app, so as long as we have enough ram, we can hold a big result. Still having issues with nullable fields and no "SQL on DBF" for now.
Also VFP Pseudo in-memory tables can handle field names longer than 10 characters, which is a must if fetching data from a SQL backend.