# SQL Guide
ApexBase supports a practical SQL dialect for embedded HTAP workloads: DDL, DML, analytical SELECT queries, joins, CTEs, set operations, transactions, table functions, full-text search, and vector search.
## Running SQL
```python
from apexbase import ApexClient
with ApexClient("./data") as client:
client.execute("CREATE TABLE IF NOT EXISTS users")
client.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
result = client.execute("SELECT * FROM users WHERE age >= 18")
```
## DDL
```sql
CREATE TABLE IF NOT EXISTS users;
ALTER TABLE users ADD COLUMN name STRING;
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE IF EXISTS old_users;
```
Use qualified names for named databases:
```sql
CREATE TABLE analytics.events;
DROP TABLE IF EXISTS analytics.old_events;
```
## DML
```sql
INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25), ('Charlie', 35);
UPDATE users SET age = 31 WHERE name = 'Alice';
DELETE FROM users WHERE age < 18;
```
## Analytical Queries
```sql
SELECT city, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE age BETWEEN 18 AND 65
GROUP BY city
HAVING users > 10
ORDER BY avg_age DESC
LIMIT 20;
```
## Joins
```sql
SELECT u.name, e.event, e.ts
FROM users u
JOIN events e ON u.id = e.user_id
WHERE e.event = 'purchase';
```
Supported join forms include `INNER`, `LEFT`, `RIGHT`, `FULL`, and `CROSS`.
## CTEs And Subqueries
```sql
WITH active_users AS (
SELECT user_id, COUNT(*) AS events
FROM events
GROUP BY user_id
)
SELECT u.name, a.events
FROM users u
JOIN active_users a ON u.id = a.user_id
WHERE a.events > 5;
```
```sql
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM events WHERE event = 'signup');
```
## Window Functions
```sql
SELECT
user_id,
ts,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) AS event_rank,
COUNT(*) OVER (PARTITION BY user_id) AS user_events
FROM events;
```
## Set Operations
```sql
SELECT user_id FROM web_events
UNION
SELECT user_id FROM mobile_events;
SELECT user_id FROM paid_users
EXCEPT
SELECT user_id FROM refunded_users;
```
`UNION`, `UNION ALL`, `INTERSECT`, and `EXCEPT` are supported.
## Transactions
```sql
BEGIN;
INSERT INTO orders (order_id, total) VALUES (1001, 39.9);
SAVEPOINT before_adjustment;
UPDATE orders SET total = 35.9 WHERE order_id = 1001;
RELEASE before_adjustment;
COMMIT;
```
Use `ROLLBACK` to cancel a transaction, or `ROLLBACK TO savepoint_name` to undo part of one.
## File Table Functions
Query files directly:
```sql
SELECT city, COUNT(*) AS rows
FROM read_csv('events.csv')
GROUP BY city;
```
Supported functions:
- `read_csv(path)`
- `read_parquet(path)`
- `read_json(path)`
For repeated queries over a file, register it as a temporary table from Python. See [Data Import](data-import.md).
## Full-Text Search
```sql
CREATE FTS INDEX ON articles(title, content);
SELECT title
FROM articles
WHERE MATCH('rust database');
```
For fuzzy matching, lifecycle commands, and configuration, see the [Full-Text Search Guide](../FTS_GUIDE.md).
## Vector Search
```sql
SELECT *
FROM explode_rename(
topk_distance('items', 'embedding', '[0.1, 0.2, 0.3]', 10, 'cosine'),
'_id,dist'
);
```
For Python batch search and float16 vector storage, see the [Float16 Vector Guide](../FLOAT16_VECTOR_GUIDE.md).
## Explain
```sql
EXPLAIN SELECT * FROM users WHERE age > 30;
EXPLAIN ANALYZE SELECT city, COUNT(*) FROM users GROUP BY city;
```
Use `EXPLAIN` when you are checking whether a query is taking a fast path, using an index, or falling back to the full planner.