Queries

DataVo queries use familiar SQL clauses: choose columns, choose a table, filter rows, sort the result, and limit how much comes back.

The examples on this page use the following tables.

CREATE TABLE Departments (
  Id INT PRIMARY KEY,
  Name VARCHAR(80)
);

CREATE TABLE Users (
  Id INT PRIMARY KEY,
  Name VARCHAR(80),
  DepartmentId INT,
  Score FLOAT,
  IsActive BIT
);

CREATE TABLE ArchivedUsers (
  Id INT PRIMARY KEY,
  Name VARCHAR(80),
  IsActive BIT
);

CREATE TABLE Embeddings (
  Id INT PRIMARY KEY,
  Label VARCHAR(80),
  Emb VECTOR(3)
);

The smallest useful query selects a few columns from a table.

SELECT Id, Name
FROM Users;

Use WHERE to filter rows.

SELECT Id, Name, Score
FROM Users
WHERE Score > 90;

Combine predicates when the application needs a narrower result set.

SELECT Id, Name, Score
FROM Users
WHERE IsActive = true AND Score BETWEEN 90 AND 100;

Use LIKE for simple string matching.

SELECT Id, Name
FROM Users
WHERE Name LIKE 'A%';

Use IN when a column can match one of several values.

SELECT Id, Name
FROM Users
WHERE Id IN (1, 3, 5);

Sort and page results with ORDER BY, LIMIT, and OFFSET.

SELECT Id, Name, Score
FROM Users
ORDER BY Score DESC, Id ASC
LIMIT 10 OFFSET 0;

Aggregate rows with COUNT, SUM, MIN, and MAX.

SELECT IsActive, COUNT(*) AS UserCount, MAX(Score) AS HighestScore
FROM Users
GROUP BY IsActive
HAVING COUNT(*) > 0;

Join tables when a query needs fields from both sides.

SELECT u.Name, d.Name AS Department
FROM Users u
INNER JOIN Departments d ON u.DepartmentId = d.Id;

Use LEFT JOIN when unmatched left rows should remain in the result.

SELECT u.Name, d.Name AS Department
FROM Users u
LEFT JOIN Departments d ON u.DepartmentId = d.Id;

Use subqueries for membership checks.

SELECT Id, Name
FROM Users
WHERE DepartmentId IN (
  SELECT Id
  FROM Departments
  WHERE Name LIKE 'Eng%'
);

Combine compatible result sets with UNION ALL.

SELECT Name FROM Users
WHERE IsActive = true
UNION ALL
SELECT Name FROM ArchivedUsers
WHERE IsActive = true;

Rank vectors with distance expressions. Lower distances sort first.

SELECT Id, Label, Emb <=> '[1,0,0]' AS distance
FROM Embeddings
ORDER BY distance ASC
LIMIT 5;

Query Support Summary

Supported query features: projection and aliases; WHERE predicates including equality, comparisons, IN, BETWEEN, LIKE, and vector-distance predicates; ORDER BY (including vector ranking); LIMIT/OFFSET; aggregates (COUNT, SUM, MIN, MAX); GROUP BY/HAVING; all major joins; subqueries (IN and EXISTS shapes); UNION/UNION ALL; and vector distance ranking (<=> for cosine, <-> for L2). The planner is early and configuration-controlled — it is not at cost-based-optimizer parity with mature RDBMSs. See the SQL compatibility matrix for the authoritative feature status.