martyna-mindsdb
8578740c7f
* restructured docs sections into tabs * restructured docs * changes to SQL API * fixed some links
69 lines
1.9 KiB
Plaintext
69 lines
1.9 KiB
Plaintext
---
|
|
title: Query a Table
|
|
sidebarTitle: Query a Table
|
|
---
|
|
|
|
## Description
|
|
|
|
The `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](/sql/api/select-predictions).
|
|
|
|
## Syntax
|
|
|
|
## 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).
|
|
|
|
```sql
|
|
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](/sql/native-queries) to integration that use syntax native to a given integration. It is useful when a query can not be parsed as SQL.
|
|
|
|
```sql
|
|
SELECT ... FROM integration_name ( native query goes here );
|
|
```
|
|
|
|
Here is an example of selecting from a Mongo integration using Mongo-QL syntax:
|
|
|
|
```sql
|
|
SELECT * FROM mongo (
|
|
db.house_sales2.find().limit(1)
|
|
);
|
|
```
|
|
|
|
## Complex queries
|
|
|
|
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.
|
|
|
|
```sql
|
|
SELECT type, max(bedrooms), last(MA)
|
|
FROM mongo (
|
|
db.house_sales2.find().limit(300)
|
|
) GROUP BY 1
|
|
```
|
|
|
|
2. Unions
|
|
|
|
It is possible to use `UNION` and `UNION ALL` operators. It this case, every subselect from union will be fetched and merged to one result-set on MindsDB side.
|
|
|
|
```sql
|
|
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';
|
|
```
|