> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/consume/activate/mysql-clients/mysql/query.md).

# Query

## Querying semantic models with SQL

Once you're connected via the db client, you can query your semantic models.

## How it works

The SQL API sits in front of the semantic layer. When you submit a query, the API translates it into the semantic layer's native query format, resolves joins, applies pre-defined aggregations, and enforces data policies before fetching results.

> Want to know more about the semantic lifecycle? [Learn more.](/concepts/resources/vulcan/guides/semantic_query_lifecycle.md)

You don't write raw database SQL. You write SQL against semantic model tables. The API handles everything else.

> **Please note that the query format described here is the only supported one.** Ad-hoc queries that use native MySQL functions or syntax outside this format may only work partially or silently return incorrect results, so we recommend avoiding them.

***

## Semantic model mapping

Every semantic model and metric in your data product is exposed as a **table**. Its measures and dimensions become **columns**.

<table><thead><tr><th width="235.9364013671875">Semantic layer concept</th><th>SQL representation</th></tr></thead><tbody><tr><td>Semantic model or metric</td><td>Table</td></tr><tr><td>Dimension</td><td>Column. Select directly.</td></tr><tr><td>Measure</td><td>Column. Wrap with <code>MEASURE()</code> or a matching aggregate.</td></tr></tbody></table>

***

## Querying a model

Treat a semantic model or metric like any table:

```sql
SELECT 
  <dimension>,
  <measure> 
FROM <table> 
where <filter_by_dimension>
group by <dimension>
having <filter_by_measure>
order by <dimension>,<measure>
limit
offset;
```

***

## Dimensions

Dimensions are plain columns. Reference them in `SELECT`, `WHERE`, `GROUP BY`, or `ORDER BY`:

```sql
SELECT status
FROM orders;
```

Filter by a dimension:

```sql
SELECT status
FROM orders
WHERE status = 'completed';
```

***

## Measures

Wrap measures with the `MEASURE()` aggregate, or with an aggregate that matches the measure type (see below).

```sql
SELECT MEASURE(count)
FROM orders;
```

> **Use `MEASURE()` by default.** It works for every measure type and avoids type mismatch errors.

***

## Aggregate functions

`MEASURE()` works with every measure type. You can also use a type-specific aggregate, but it has to match exactly. Otherwise the API throws `Measure aggregation type doesn't match`.

<table><thead><tr><th width="166.43231201171875">Measure type</th><th>Supported aggregates</th></tr></thead><tbody><tr><td><code>avg</code></td><td><code>MEASURE</code> or <code>AVG</code></td></tr><tr><td><code>boolean</code></td><td><code>MEASURE</code></td></tr><tr><td><code>count</code></td><td><code>MEASURE</code> or <code>COUNT</code></td></tr><tr><td><code>count_distinct</code></td><td><code>MEASURE</code> or <code>COUNT(DISTINCT ...)</code></td></tr><tr><td><code>max</code></td><td><code>MEASURE</code> or <code>MAX</code></td></tr><tr><td><code>min</code></td><td><code>MEASURE</code> or <code>MIN</code></td></tr><tr><td><code>number</code></td><td><code>MEASURE</code> or any function from this table</td></tr><tr><td><code>string</code></td><td><code>MEASURE</code> or <code>STRING_AGG</code></td></tr><tr><td><code>sum</code></td><td><code>MEASURE</code> or <code>SUM</code></td></tr><tr><td><code>time</code></td><td><code>MEASURE</code>, <code>MAX</code>, or <code>MIN</code></td></tr></tbody></table>

***

## Joins

The semantic layer already defines relationships between models. To query across models, use one of three forms: `CROSS JOIN`, comma-separated tables, or `LEFT JOIN` with the `__joinField` key.

`__joinField` is a virtual column on every model. It carries the join key the semantic layer uses. You don't define the join condition; you reference `__joinField` on both sides.

`LEFT JOIN` using `__joinField`:

```sql
SELECT p.name, SUM(o.count)
FROM orders o
LEFT JOIN products p ON o.__joinField = p.__joinField
GROUP BY 1
LIMIT 5;
```

`CROSS JOIN`:

```sql
SELECT p.name, MEASURE(o.count)
FROM orders o
CROSS JOIN products p
GROUP BY 1
LIMIT 5;
```

Comma-separated (implicit join):

```sql
SELECT p.name, MEASURE(o.count)
FROM orders o, products p
GROUP BY 1
LIMIT 5;
```

All three are equivalent. Pick whichever reads most clearly.

***

## Supported Filters Operators

| Operator        | Description                                                                                  | Types                      | MySQL Syntax                                                                           |
| --------------- | -------------------------------------------------------------------------------------------- | -------------------------- | -------------------------------------------------------------------------------------- |
| `IN`            | Use it when you need an exact match. It supports multiple values.                            | `string`, `number`, `time` | `WHERE customer.country IN ('US', 'Germany', 'Israel')`                                |
| `NOT IN`        | The opposite operator of `IN`. It supports multiple values.                                  | `string`, `number`, `time` | `WHERE customer.country NOT IN ('France')`                                             |
| `LIKE`          | Acts as a wildcard case-insensitive match. Supports multiple values.                         | `string`                   | `WHERE product.brand LIKE '%casamigos%' OR product.brand LIKE '%grey goose%'`          |
| `NOT LIKE`      | The opposite operator of `LIKE`. Supports multiple values.                                   | `string`                   | `WHERE product.brand NOT LIKE '%casamigos%' AND product.brand NOT LIKE '%grey goose%'` |
| `LIKE 'x%'`     | Acts as a case-insensitive match with a wildcard at the end. Supports multiple values.       | `string`                   | `WHERE product.brand LIKE 'grey%'`                                                     |
| `NOT LIKE 'x%'` | The opposite operator of `LIKE 'x%'`.                                                        | `string`                   | `WHERE product.brand NOT LIKE 'grey%'`                                                 |
| `LIKE '%x'`     | Acts as a case-insensitive match with a wildcard at the beginning. Supports multiple values. | `string`                   | `WHERE product.brand LIKE '%goose'`                                                    |
| `NOT LIKE '%x'` | The opposite operator of `LIKE '%x'`.                                                        | `string`                   | `WHERE product.brand NOT LIKE '%goose'`                                                |
| `>`             | Means **greater than**.                                                                      | `number`                   | `WHERE sales.total_orders > 100`                                                       |
| `>=`            | Means **greater than or equal to**.                                                          | `number`                   | `WHERE sales.total_orders >= 100`                                                      |
| `<`             | Means **less than**.                                                                         | `number`                   | `WHERE sales.total_orders < 100`                                                       |
| `<=`            | Means **less than or equal to**.                                                             | `number`                   | `WHERE sales.total_orders <= 100`                                                      |
| `IS NOT NULL`   | Checks whether the value of the member **is not** `NULL`.                                    | `number`, `string`, `time` | `WHERE account.phone_number IS NOT NULL`                                               |
| `IS NULL`       | The opposite of `IS NOT NULL`. Checks whether the value of the member **is** `NULL`.         | `number`, `string`, `time` | `WHERE account.phone_number IS NULL`                                                   |
| `BETWEEN`       | Used to filter a time dimension into a specific date range.                                  | `time`                     | `WHERE sales.order_date BETWEEN '2015-01-01' AND '2015-12-31'`                         |
| `NOT BETWEEN`   | The opposite operator of `BETWEEN`.                                                          | `time`                     | `WHERE sales.order_date NOT BETWEEN '2015-01-01' AND '2015-12-31'`                     |
| `<` (date)      | Used to retrieve all results before a specific date.                                         | `time`                     | `WHERE sales.order_date < '2015-01-01'`                                                |
| `>` (date)      | Used to get all results after a specific date.                                               | `time`                     | `WHERE sales.order_date > '2015-01-01'`                                                |

***

### Scenario 1: One dimension, one measure, one filter

Total order count per status, completed orders only:

```sql
SELECT
  status,
  MEASURE(count)
FROM orders
WHERE status = 'completed'
GROUP BY status
ORDER BY MEASURE(count) DESC
LIMIT 10;
```

***

### Scenario 2: One dimension, multiple measures, with a filter

Order count, total revenue, and average order value per region, current year only:

```sql
SELECT
  region,
  MEASURE(count)        AS total_orders,
  MEASURE(revenue)      AS total_revenue,
  MEASURE(avg_order_value) AS avg_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY region
ORDER BY MEASURE(revenue) DESC
LIMIT 20;
```

***

### Scenario 3: Two models with a cross-model measure

Total order count per product name, across `orders` and `products`, limited to 2025:

```sql
SELECT
  p.name               AS product_name,
  MEASURE(o.count)     AS total_orders
FROM orders o, products p
WHERE o.order_date >= '2025-01-01'
GROUP BY p.name
ORDER BY MEASURE(o.count) DESC
LIMIT 10;
```

***

### Scenario 4: Two models with `WHERE` and `HAVING`

Product name and total revenue for products with more than 500 completed orders:

```sql
SELECT
  p.name               AS product_name,
  MEASURE(o.count)     AS total_orders,
  MEASURE(o.revenue)   AS total_revenue
FROM orders o
LEFT JOIN products p ON o.__joinField = p.__joinField
WHERE o.status = 'completed'
GROUP BY p.name
HAVING MEASURE(o.count) > 500
ORDER BY MEASURE(o.revenue) DESC
LIMIT 25;
```

***

## Quick reference

### Introspection

Use these to see what's available before writing queries:

```sql
-- List all semantic models in the connected data product
SHOW TABLES;

-- Distinguish between metric and semantic model
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'public';;

-- Column details for a model (dimensions and measures)
DESCRIBE orders;
DESC orders;



-- Columns for a specific model
SHOW COLUMNS FROM orders;

-- Columns with full metadata (Distinguish between measure and dimesnion.)
SHOW FULL COLUMNS FROM orders;
```

### Common patterns

```sql
-- Dimension and measure
SELECT status, MEASURE(count) FROM orders GROUP BY status;

-- Cross-model
SELECT p.name, MEASURE(o.revenue) FROM orders o, products p GROUP BY p.name;

-- Filter, sort by measure
SELECT region, MEASURE(revenue) FROM orders
WHERE region = 'APAC'
GROUP BY region
ORDER BY MEASURE(revenue) DESC
LIMIT 10;
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/consume/activate/mysql-clients/mysql/query.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.