matlab-read-database

name: matlab-read-database description: "Reads data from relational databases using MATLAB Database Toolbox pushdown capabilities. Use when importing data from JDBC/ODBC databases, filtering rows, selecting columns, excluding duplicates, joining database tables, using sqlread, fetch, sqlinnerjoin, sqlouterjoin, databaseImportOptions, or rowfilter." license: MathWorks BSD-3-Clause metadata: author: MathWorks version: "1.0"

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

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

Decision Framework

Which function should I use?

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.