matlab-read-database

# MATLAB Database Toolbox Pushdown Architect

Use when importing data from relational databases with MATLAB Database Toolbox's pushdown capabilities — offloading row filtering, column selection, duplicate exclusion, and joins to the database instead of processing in MATLAB.

## When to Use This Skill

- Importing data from a database table or an SQL query as a MATLAB table
- Filtering rows from a database table or an SQL query
- Selecting specific columns from a database table or an SQL query
- Excluding duplicate rows in a database table or an SQL query
- Joining two database tables
- Combining joins with filtering or column selection
- User mentions keywords: import, sqlread, fetch, filter, rowfilter, join, sqlinnerjoin, sqlouterjoin, duplicates, databaseImportOptions, pushdown

## When NOT to Use

- Writing/inserting data into a database — use `sqlwrite`/`sqlupdate`/`execute` patterns instead
- Data too large to fit in memory — use `DatabaseDatastore` + tall arrays or `splitsqlquery`
- Object-oriented reads with class mapping — use ORM (`ormread` with `Mappable` classes)
- MongoDB, Cassandra, or Neo4j queries — pushdown functions only work with relational databases

## Critical Rules

### Pushdown Strategy
- **ALWAYS** use `sqlread` for tables, `fetch` for SQL queries.
- **ALWAYS** use `RowFilter` to push row filtering to the database. **NEVER** import all rows and filter in MATLAB.
- **ALWAYS** use `databaseImportOptions` with `SelectedVariableNames` when only a subset of columns is needed.
- **ALWAYS** verify the connection with `isopen(conn)` before operations and call `close(conn)` when done.

### Joins
- **ALWAYS** use `sqlinnerjoin`/`sqlouterjoin` for joining exactly 2 database tables.
- **NEVER** pass `databaseImportOptions` (`opts`) to `sqlinnerjoin` or `sqlouterjoin` — they do not accept it. Select columns in MATLAB after the join, or use `fetch` with explicit SQL.
- **NEVER** use pushdown joins for 3+ tables or joins with aggregation — use `fetch` with explicit SQL instead.


## Function Reference

### What Each Function Accepts

| Function | Accepts `opts`? | Accepts `RowFilter`? | Accepts `MaxRows`? | Column Selection |
|----------|:-:|:-:|:-:|---|
| `sqlread` | Yes | Yes | Yes | Via `opts.SelectedVariableNames` |
| `fetch` | Yes | Yes | Yes | Via `opts.SelectedVariableNames` |
| `sqlinnerjoin` | **No** | Yes | Yes | **Not supported** — select columns after join |
| `sqlouterjoin` | **No** | Yes | Yes | **Not supported** — select columns after join |

For full parameter details, see `reference/cards/pushdown-joins.md` and `reference/cards/import-options.md`.

## Decision Framework

> Which function should I use?

| Situation | Use | Why |
|-----------|-----|-----|
| Import from a single table | `sqlread` | Pushes filters/column selection to DB |
| Import from a SQL query | `fetch` | Executes arbitrary SQL on DB |
| Join exactly 2 tables (no column selection needed) | `sqlinnerjoin` / `sqlouterjoin` | Join executes on DB |
| Join 2 tables + select specific columns | `sqlinnerjoin` + MATLAB column selection | Join functions don't accept `opts` |
| Join 2 tables + column selection + deduplication | `fetch` with explicit SQL | Pushdown joins can't handle `opts` or `DISTINCT` |
| Join 3+ tables or use aggregation | `fetch` with explicit SQL | Pushdown joins limited to 2 tables |
| Need `ExcludeDuplicates` | `sqlread`/`fetch` with `opts` | Only these accept `databaseImportOptions` |

## Complete Examples

See knowledge cards for detailed examples:
- **Single table import with filtering**: `reference/cards/sqlread-fetch.md`
- **Column selection and deduplication**: `reference/cards/import-options.md`
- **Two-table joins with filtering**: `reference/cards/pushdown-joins.md`
- **Complex multi-table joins**: `reference/cards/pushdown-joins.md` (Fall Back to SQL section)

### Common Mistakes

```matlab
% INCORRECT — passing import options to join functions (error!)
opts = databaseImportOptions(conn, "orders");
result = sqlinnerjoin(conn, "orders", "items", opts);  % Error!

% CORRECT — join first, then select columns from the result
result = sqlinnerjoin(conn, "orders", "items", Keys="order_id");
result = result(:, ["order_id", "product", "quantity", "total"]);

% INCORRECT — using fetch without pushdown (pulls all data, filters in MATLAB)
data = fetch(conn, "SELECT * FROM orders");
filtered = data(data.total > 100, :);

% CORRECT — push the filter to the database
opts = databaseImportOptions(conn, "orders");
opts.RowFilter = opts.RowFilter.total > 100;
data = sqlread(conn, "orders", opts);
```

## Best Practices

- Use `RowFilter` as a name-value argument directly on `sqlread`/`fetch`/`sqlinnerjoin`/`sqlouterjoin` for simple filtering. Use `opts.RowFilter` when you also need column selection or deduplication.
- When layering `RowFilter` on a SQL query in `fetch`, the `RowFilter` adds conditions **on top of** the SQL `WHERE` clause. Avoid duplicating the same condition in both.
- Prefer `sqlinnerjoin`/`sqlouterjoin` over writing JOIN SQL manually when working with exactly 2 tables and no column selection or aggregation is needed.
- For join + column selection, choose based on data volume: if the extra columns are small, join then select in MATLAB. If the table is wide and data is large, use `fetch` with explicit SQL to select columns on the database.
- For tables with >50 columns, always use `SelectedVariableNames` to limit columns. For result sets >100K rows that don't fit in memory, use `DatabaseDatastore` with tall arrays or `splitsqlquery` for out-of-memory processing.

## Common Patterns

### Pattern 1: Single Table — Filter + Select Columns

```matlab
opts = databaseImportOptions(conn, "orders");
opts.SelectedVariableNames = ["OrderKey", "OrderStatus"];
opts.RowFilter = opts.RowFilter.OrderPriority == "URGENT";
T = sqlread(conn, "orders", opts);
```

### Pattern 2: Two Table Join — Filter Only

```matlab
rf = rowfilter("ShipMode");
T = sqlinnerjoin(conn, "orders", "lineitem",