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

% 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

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

rf = rowfilter("ShipMode");
T = sqlinnerjoin(conn, "orders", "lineitem", Keys="OrderKey", RowFilter=rf.ShipMode == "AIR");

Pattern 3: Two Table Join — Filter + Select Columns (Post-Join)

rf = rowfilter(["OrderPriority", "ShipMode"]);
T = sqlinnerjoin(conn, "orders", "lineitem", Keys="OrderKey", ...
    RowFilter=rf.OrderPriority == "URGENT" & rf.ShipMode == "AIR");
result = T(:, ["OrderKey", "OrderStatus"]);

Pattern 4: Fall Back to SQL for Complex Queries

sqlquery = "SELECT o.OrderKey, o.OrderStatus " + ...
    "FROM orders o INNER JOIN lineitem l ON o.OrderKey = l.OrderKey " + ...
    "WHERE o.OrderPriority = 'URGENT' AND l.ShipMode = 'AIR'";
T = fetch(conn, sqlquery);

Pattern 5: Safe Import with Error Handling

try
    opts = databaseImportOptions(conn, "orders");
    opts.SelectedVariableNames = ["id", "total", "status"];
    opts.RowFilter = opts.RowFilter.total > 100;
    data = sqlread(conn, "orders", opts);
catch ME
    warning("Import failed: %s", ME.message);
    data = table.empty;
end

Checklist

Before finalizing pushdown import code, verify:

• sqlread used for database tables, fetch used for SQL queries
• Row filters use RowFilter parameter (pushed to database), not client-side filtering
• Only needed columns selected via databaseImportOptions with SelectedVariableNames
• databaseImportOptions is NOT passed to sqlinnerjoin or sqlouterjoin
• ExcludeDuplicates used instead of MATLAB unique() for deduplication
• Joins use sqlinnerjoin/sqlouterjoin (limited to 2 tables, no opts)
• Complex queries (3+ tables, aggregation, join + column selection) use fetch with SQL
• isopen(conn) checked after connection attempt
• close(conn) called at the end

Troubleshooting

Issue: sqlinnerjoin errors when passing databaseImportOptions

• Solution: sqlinnerjoin and sqlouterjoin do not accept databaseImportOptions. Remove opts from the call. Select columns in MATLAB after the join, or use fetch with explicit SQL.

Issue: RowFilter has no effect — all rows are still returned

• Solution: Verify the column name in rowfilter("ColName") matches the database column exactly (case-sensitive for some databases).

Issue: sqlinnerjoin errors with "Key variable not found"

• Solution: The Keys value must match a column name that exists in both tables. Use sqlfind(conn, "tableName") to inspect column names.

Issue: databaseImportOptions errors on a SQL query

• Solution: Ensure the SQL query is valid and returns results. databaseImportOptions executes a metadata query — if the base query has syntax errors, it will fail.

Issue: ExcludeDuplicates doesn't remove duplicates as expected

• Solution: ExcludeDuplicates applies to the combination of all selected variables. Use SelectedVariableNames to narrow the columns first, then set ExcludeDuplicates = true.

Issue: sqlouterjoin returns unexpected NULLs

• Solution: Outer joins include non-matching rows filled with NULLs. This is expected SQL behavior. Use RowFilter to exclude rows post-join, or switch to sqlinnerjoin if you only want matching rows.

---

Copyright 2026 The MathWorks, Inc.

---