SELECT statement fetches data from a table and predictions from a model.
Here we go over example of selecting data from tables of connected data sources. To learn how to select predictions from a model, visit this page.
Simple SELECT FROM an integration
In this example, query contains only tables from one integration. This query will be executed on this integration database (where integration name will be cut from the table name).
SELECT location, max(sqft) FROM example_db.demo_data.home_rentals GROUP BY location LIMIT 5;
Raw SELECT FROM an integration
It is also possible to send native queries to integration that use syntax native to a given integration. It is useful when a query can not be parsed as SQL.
SELECT ... FROM integration_name ( native query goes here );
Here is an example of selecting from a Mongo integration using Mongo-QL syntax:
SELECT * FROM mongo ( db.house_sales2.find().limit(1) );
- Subselect on data from integration.
It can be useful in cases when integration engine doesn’t support some functions, for example, grouping, as shown below. In this case, all data from raw select are passed to MindsDB and then subselect performs operations on them inside MindsDB.
SELECT type, max(bedrooms), last(MA) FROM mongo ( db.house_sales2.find().limit(300) ) GROUP BY 1
It is possible to use
UNION ALL operators. It this case, every subselect from union will be fetched and merged to one result-set on MindsDB side.
SELECT data.time as date, data.target FROM datasource.table_name as data UNION ALL SELECT model.time as date, model.target as target FROM mindsdb.model as model JOIN datasource.table_name as t WHERE t.time > LATEST AND t.group = 'value';