My biggest gripes with the Airtable API for a small project that has a couple of tables with > 100 rows were around pagination and joining the data between the tables which was super slow.
An overview of the supported SQL syntax is here:
https://blaze.today/datablaze/docs/bsql/
No joins yet between tables yet, but they are planned. (You can use lookup fields to achieve them to some extent, but that requires planning ahead and is limited).