sqlouterjoin

Perform outer join on two database tables

Syntax

data = sqlouterjoin(conn,lefttable,righttable)

data = sqlouterjoin(conn,lefttable,righttable,Name=Value)

Description

data = sqlouterjoin(conn,lefttable,righttable) returns a table created by performing an outer join on the specified left and right database tables. This function matches rows using all shared columns, or keys, in both tables and retains only rows that exist in both. This operation is equivalent to executing the SQL statement SELECT * FROM lefttable,righttable OUTER JOIN lefttable.key = righttable.key.

conn can be any of the following connection objects:

• MySQL^®

• PostgreSQL^®

• DuckDB™

• ODBC

• JDBC

Use this function to join two tables and include rows that do not have matching keys in one or both tables.

data = sqlouterjoin(conn,lefttable,righttable,Name=Value) uses additional options specified by one or more name-value arguments. For example, set Keys="productNumber" to join the tables using productNumber as the join key.

Examples

Join Two Database Tables Using MySQL Native Interface

Use a MySQL® native interface database connection to import product data from an outer join between two MySQL database tables into MATLAB®.

Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

Join two database tables, productTable and suppliers. The productTable table is the left table of the join, and the suppliers table is the right table of the join. The sqlouterjoin function automatically detects the shared column between the tables. Use the 'Type' name-value pair argument to retrieve records that have matching values in the selected column of both tables, and unmatched records from the left table only. data is a table that contains the matched and unmatched rows from the two tables.

lefttable = "productTable";
righttable = "suppliers";
data = sqlouterjoin(conn,lefttable,righttable,'Type',"left");

Display the first three rows of joined data. The columns from the right table (suppliers) appear to the right of the columns from the left table (productTable).

head(data,3)

ans=3×10 table
    8    212569    1001     5          "Train Set"    1001    "Wonder Products"    "New York"     "United States"    "212 435 1617"
    1    400345    1001    14    "Building Blocks"    1001    "Wonder Products"    "New York"     "United States"    "212 435 1617"
    2    400314    1002     9       "Painting Set"    1002      "Terrific Toys"      "London"    "United Kingdom"     "44 456 9345"

Close the database connection.

close(conn)

Join Two Database Tables Using PostgreSQL Native Interface

Use a PostgreSQL native interface database connection to import product data from an outer join between two PostgreSQL database tables into MATLAB®.

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Join two database tables, productTable and suppliers. The productTable table is the left table of the join, and the suppliers table is the right table of the join. The sqlouterjoin function automatically detects the shared column between the tables. The sqlouterjoin function automatically detects the shared column between the tables. data is a table that contains the matched and unmatched rows from the two tables.

lefttable = "productTable";
righttable = "suppliers";
data = sqlouterjoin(conn,lefttable,righttable);

Display the first three rows of joined data. The columns from the right table (suppliers) appear to the right of the columns from the left table (productTable).

head(data,3)

ans=3×10 table
    1    400345    1001    14    "Building Blocks"    1001    "Wonder Products"    "New York"     "United States"    "212 435 1617"
    2    400314    1002     9       "Painting Set"    1002      "Terrific Toys"      "London"    "United Kingdom"     "44 456 9345"
    3    400999    1009    17             "Slinky"    1009      "Doll's Galore"      "London"    "United Kingdom"     "44 222 2397"

Close the database connection.

close(conn)

Perform Outer Join of Two DuckDB Database Tables

Use the sqlouterjoin function to perform an outer join of two DuckDB database tables.

Create a DuckDB database connection by using the duckdb function.

conn = duckdb;

Create a table to store product data by using the table function.

ProductName = ["ProductA"; "ProductB"; "ProductC"; "ProductD"];
ProductID = [101; 102; 103; 104];
ProductTable = table(ProductName, ProductID)

ProductTable=4×2 table
    ProductName    ProductID
    ___________    _________

    "ProductA"        101   
    "ProductB"        102   
    "ProductC"        103   
    "ProductD"        104   

Next, create a table to store supplier data.

SupplierID = [2001; 2003];
ProductID = [101; 103];
SupplierTable = table(SupplierID, ProductID)

SupplierTable=2×2 table
    SupplierID    ProductID
    __________    _________

       2001          101   
       2003          103   

Export the tables to the DuckDB database by using the sqlwrite function.

productTableName = "productTable";
supplierTableName = "supplierTable";
sqlwrite(conn,productTableName,ProductTable);
sqlwrite(conn,supplierTableName,SupplierTable);

Join the tables by using the sqlouterjoin function.

sqlouterjoin(conn,productTableName,supplierTableName)

ans=4×4 table
    ProductName    ProductID    SupplierID    ProductID_1
    ___________    _________    __________    ___________

    "ProductA"        101          2001           101    
    "ProductB"        102           NaN           NaN    
    "ProductC"        103          2003           103    
    "ProductD"        104           NaN           NaN    

Join Two Database Tables in Catalog and Schema

Use an ODBC connection to import product data from an outer join between two Microsoft® SQL Server® database tables into MATLAB®. Specify the database catalog and schema where the tables are stored.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the tables productTable and suppliers.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message

ans =

     []

Join two database tables, productTable and suppliers. The productTable table is the left table of the join, and the suppliers table is the right table of the join. The sqlouterjoin function automatically detects the shared column between the tables. Specify the toy_store catalog and the dbo schema for both the left and right tables. Use the 'LeftCatalog' and 'LeftSchema' name-value pair arguments for the left table, and the 'RightCatalog' and 'RightSchema' name-value pair arguments for the right table.

data is a table that contains the matched and unmatched rows from the two tables.

lefttable = 'productTable';
righttable = 'suppliers';
data = sqlouterjoin(conn,lefttable,righttable,'LeftCatalog','toy_store', ...
    'LeftSchema','dbo','RightCatalog','toy_store','RightSchema','dbo');

Display the first three rows of joined data. The columns from the right table appear to the right of the columns from the left table.

head(data,3)

ans =

  3×10 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber      SupplierName          City           Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    _________________    __________    ________________    ______________

          1          4.0035e+05          1001            14       'Building Blocks'          1001         'Wonder Products'    'New York'    'United States'     '212 435 1617'
          2          4.0031e+05          1002             9       'Painting Set'             1002         'Terrific Toys'      'London'      'United Kingdom'    '44 456 9345' 
          3            4.01e+05          1009            17       'Slinky'                   1009         'Doll's Galore'      'London'      'United Kingdom'    '44 222 2397' 

Close the database connection.

close(conn)

Specify Key for Joining Two Database Tables

Use an ODBC connection to import joined product data from two Microsoft® SQL Server® database tables into MATLAB®. Specify the key to use for joining the tables.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the tables productTable and suppliers.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message

ans =

     []

Join two database tables, productTable and suppliers. The productTable table is the left table of the join, and the suppliers table is the right table of the join. Specify the key, or shared column, between the tables using the 'Keys' name-value pair argument.

data is a table that contains the matched and unmatched rows from the two tables.

lefttable = 'productTable';
righttable = 'suppliers';
data = sqlouterjoin(conn,lefttable,righttable,'Keys','supplierNumber');

Display the first three rows of joined data. The columns from the right table appear to the right of the columns from the left table.

head(data,3)

ans =

  3×10 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber      SupplierName          City           Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    _________________    __________    ________________    ______________

          1          4.0035e+05          1001            14       'Building Blocks'          1001         'Wonder Products'    'New York'    'United States'     '212 435 1617'
          2          4.0031e+05          1002             9       'Painting Set'             1002         'Terrific Toys'      'London'      'United Kingdom'    '44 456 9345' 
          3            4.01e+05          1009            17       'Slinky'                   1009         'Doll's Galore'      'London'      'United Kingdom'    '44 222 2397' 

Close the database connection.

close(conn)

Filter Rows in Joined Data

Use a MySQL® native interface database connection to import product data from an outer join between two MySQL database tables into MATLAB®. Specify the row filter condition to use for joining the tables.

Create a MySQL native interface database connection to a MySQL database using the data source name, username, and password. The database contains the tables productTable and suppliers.

datasource = "MySQLDataSource";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

Join the two database tables, productTable and suppliers. The productTable table is the left table of the join, and the suppliers table is the right table of the join. The sqlouterjoin function automatically detects the shared column between the tables. Use Type to retrieve records that have matching values in the selected column of both tables, and unmatched records from the left table only. The table data contains the matched and unmatched rows from the two tables.

lefttable = "productTable";
righttable = "suppliers";
data = sqlouterjoin(conn,lefttable,righttable,"Type","left");

Display the first five rows of matched data. The columns from the right table appear to the right of the columns from the left table.

head(data,5)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber      SupplierName          City           Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    _________________    __________    ________________    ______________

          1          4.0034e+05          1001            14       "Building Blocks"          1001         "Wonder Products"    "New York"    "United States"     "212 435 1617"
          1          4.0034e+05          1001            14       "Building Blocks"          1001         "Wonder Products"    "New York"    "United States"     "212 435 1617"
          2          4.0031e+05          1002             9       "Painting Set"             1002         "Terrific Toys"      "London"      "United Kingdom"    "44 456 9345" 
          2          4.0031e+05          1002             9       "Painting Set"             1002         "Terrific Toys"      "London"      "United Kingdom"    "44 456 9345" 
          3            4.01e+05          1009            17       "Slinky"                   1009         "Doll's Galore"      "London"      "United Kingdom"    "44 222 2397" 

Join the same tables, but this time use a row filter. The filter condition is that unitCost must be less than 10. Again, display the first five rows of matched data.

rf = rowfilter("unitCost");
rf = rf.unitCost <= 10;
data = sqlouterjoin(conn,lefttable,righttable, ...
                    "Type","left", ...
                    "RowFilter",rf);
head(data,5)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber         SupplierName           City          Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    ______________________    ________    ________________    ______________

          2          4.0031e+05          1002            9          "Painting Set"           1002         "Terrific Toys"           "London"    "United Kingdom"    "44 456 9345" 
          2          4.0031e+05          1002            9          "Painting Set"           1002         "Terrific Toys"           "London"    "United Kingdom"    "44 456 9345" 
          5          4.0046e+05          1005            3          "Tin Soldier"            1005         "Custers Tin Soldiers"    "Boston"    "United States"     "617 939 1234"
          5          4.0046e+05          1005            3          "Tin Soldier"            1005         "Custers Tin Soldiers"    "Boston"    "United States"     "617 939 1234"
          6          4.0088e+05          1004            8          "Sail Boat"              1004         "Incredible Machines"     "Dublin"    "Ireland"           "01 222 3456" 

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

MySQLconnection object | PostgreSQL connection object | DuckDB connection object | ODBC connection object | JDBC connection object

Database connection, specified as any of the following:

• MySQL connection object created by using the mysql function.

• PostgreSQL connection object created by using the postgresql function.

• DuckDB connection object created by using the duckdb function.

• ODBC connection object created by using the database function.

• JDBC connection object created by using the database function.

lefttable — Left table

string scalar | character vector

Left table, specified as a string scalar or character vector. Specify the name of the database table on the left side of the join.

Example: "inventoryTable"

Data Types: string | char

righttable — Right table

string scalar | character vector

Right table, specified as a string scalar or character vector. Specify the name of the database table on the right side of the join.

Example: "productTable"

Data Types: string | char

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: data = sqlouterjoin(conn,lefttable,righttable,Type="left",MaxRows=5) performs a left outer join between the two tables and returns up to five rows of the joined result.

LeftCatalog — Left catalog

string scalar | character vector

Left catalog, specified as a string scalar or character vector. LeftCatalog represents the name of the database catalog that contains lefttable.

Example: LeftCatalog="toy_store"

Data Types: string | char

RightCatalog — Right catalog

string scalar | character vector

Right catalog, specified as a string scalar or character vector. RightCatalog represents the name of the database catalog that contains righttable.

Example: RightCatalog="toy_store"

Data Types: string | char

LeftSchema — Left schema

string scalar | character vector

Left schema, specified as a string scalar or character vector. LeftSchema represents the name of the database schema that contains lefttable.

Example: LeftSchema="dbo"

Note

This argument is not valid when conn is an MySQL connection object.

Data Types: string | char

RightSchema — Right schema

string scalar | character vector

Right schema, specified as a string scalar or character vector. RightSchema represents the name of the database catalog that contains righttable.

Example: RightSchema="dbo"

Note

This argument is not valid when conn is an MySQL connection object.

Data Types: string | char

Keys — Keys

string scalar | string array | character vector | cell array of character vectors

Keys, specified as a string scalar, string array, character vector, or cell array of character vectors. Use Keys to specify common columns used for matching rows.

• For one key, provide a string scalar or character vector.

• For multiple keys, provide a string array or cell array of character vectors.

You cannot combine this argument with LeftKeys and RightKeys.

Example: Keys="MANAGER_ID"

Data Types: string | char | cell

LeftKeys — Left table keys

string scalar | character vector | string array | cell array of character vectors

Left table keys, specified as a string scalar, string array, character vector, or cell array of character vectors.

• Use LeftKeys to define the columns in lefttable that match with columns in the righttable.

• For one key, specify a string scalar or character vector.

• For multiple keys, specify a string array or a cell array of character vectors.

Use this argument together with RightKeys. Both arguments must specify the same number of keys, and the sqlouterjoin pairs keys based on their order.

Example: LeftKeys=["productNumber" "Price"],RightKeys=["productNumber" "Price"]

Data Types: string | char | cell

RightKeys — Right table keys

string scalar | string array | character vector | cell array of character vectors

Right table keys, specified as a string scalar, string array, character vector, or cell array of character vectors.

• Use RightKeys to define the columns in righttable that match with columns in lefttable.

• For one key, specify a string scalar or character vector.

• For multiple keys, specify a string array or a cell array of character vectors.

Use this argument together with LeftKeys. Both arguments must specify the same number of keys, and the sqlouterjoin pairs keys based on their order.

Example: LeftKeys=["productIdentifier" "Cost"],RightKeys=["productNumber" "Price"]

Data Types: string | char | cell

MaxRows — Maximum number of rows to return

positive numeric scalar

Maximum number of rows to return, specified as a positive numeric scalar. By default, the sqlouterjoin function returns all rows from the executed SQL query. Use this argument to limit the number of rows imported.

Example: MaxRows=10

Data Types: double

Type — Outer join type

"full" (default) | "left" | "right"

Outer join type, specified as one of the following:

• "full" — Returns all records with matching values in the selected columns of both tables, plus unmatched records from both the left and right tables.

• "left" — Returns all records with matching values in both tables, plus unmatched records from the left table only.

• "right" — Returns all records with matching values in both tables, plus unmatched records from the right table only.

Not all databases support every join type. If a join type is unsupported, use the sqlread function to import data from both tables into the MATLAB^® workspace. Then, use the outerjoin function to perform the join within MATLAB.

Example: Type="left"

Note

This argument is not valid when conn is an MySQL connection object.

VariableNamingRule — Variable naming rule

"modify" (default) | "preserve"

Variable naming rule, specified as one of the following:

• "modify" — Remove non-ASCII characters from variable names when the sqlouterjoin function imports data.

• "preserve" — Preserve most variable names when the sqlouterjoin function imports data.

Example: VariableNamingRule="modify"

RowFilter — Row filter condition

<unconstrained> (default) | matlab.io.RowFilter object

Row filter condition, specified as a matlab.io.RowFilter object.

Example: rf = rowfilter("productnumber"); rf = rf.productnumber <= 5; sqlouterjoin(conn,lefttable,righttable,RowFilter=rf)

Output Arguments

data — Joined data

table

Joined data, returned as a table that contains rows matched by keys in the left and right database tables and the retained unmatched rows. data also contains a variable for each column in the left and right tables.

By default, the variable data types are double for columns that have numeric data types in the database table. For any text, date, time, or timestamp data types in the database table, the variable data type is a cell array of character vectors by default.

If the column names are shared between the joined database tables and have the same case, then the outerjoin function adds a unique suffix to the corresponding variable names in data.

The variables in data that correspond to columns in the left table contain NULL values when no matched rows exist in the right database table. Similarly, the variables that correspond to columns in the right table contain NULL values when no matched rows exist in the left database table.

The following table lists how each database data type maps to a MATLAB data type.

Database Data Type	MySQL	PostgreSQL	DuckDB	SQLite	JDBC/ODBC
BOOLEAN	N/A	logical	logical	int64	logical
TINYINT	double	N/A	double	double	double
SMALLINT		double
INTEGER	N/A			N/A
INT		N/A	N/A	double
BIGINT	double	double	double
UTINYINT	N/A	N/A		N/A
USMALLINT
UINTEGER
UBIGINT
FLOAT				double
DOUBLE	double	double
HUGEINT	N/A	N/A		N/A
UHUGEINT
NUMERIC	double	double	N/A	double
SMALLSERIAL	N/A			N/A
SERIAL
BIGSERIAL
REAL	double			double
MONEY	N/A			N/A
MEDIUMINT		N/A		double
YEAR	double			N/A
TIMESTAMP	datetime	datetime	datetime		cell array or character vectors
TIMESTAMP_S	N/A	N/A
TIMESTAMP_MS
TIMESTAMP_NS
TIMESTAMPZ		datetime	N/A
ABSTIME
DATE	datetime		datetime (12AM on specified date)	string
DATETIME	N/A	N/A	N/A
DECIMAL	double	double	double	N/A	double
VARCHAR	string	string		string	cell array or character vectors
BIT	logical	N/A	N/A	N/A	logical
BYTEA	N/A	string			cell array or character vectors
UUID			string
CIDR			N/A
INET
MACADDR
XML
TIME	duration	duration	duration
TIMEZ	N/A		N/A
TIME_TZ		N/A	duration
INTERVAL		calendarDuration	calendarDuration
HUGEINT		N/A	double		double
UHUGEINT
BLOB		cell array of uint8 vectors	cell array of uint8 vectors	Nx1 uint8 vector	cell array or character vectors
ENUM		categorical (categories match values of ENUM type)	categorical (categories match values of ENUM type)	N/A
CHAR	string	string	N/A	string
TEXT	N/A			N/A
RELTIME		calendarDuration
JSON	char	N/A
LONGVARCHAR	string
LIST	N/A		Cell array where each cell contains a vector of the corresponding type. For example, DOUBLE[] maps to a cell array of double vectors.
STRUCT			Each field maps to its corresponding DuckDB type. For example, STRUCT(name VARCHAR, age INTEGER) maps to a MATLAB struct, where name fields are strings and age fields are int32.
MAP			Cell array of dictionaries. Key and value types match the DuckDB types. For example, MAP(DOUBLE, VARCHAR) maps to a cell array of dictionaries with double keys and string values.
ARRAY			Cell array with each cell containing a vector of the corresponding type. For example, DOUBLE[3] maps to a cell array of double vectors.
UNION			Cell array where each cell can contain a value matching any DuckDB type defined in the UNION. For example, UNION(t TIMESTAMP, d DOUBLE), maps to a cell array where each element is either a datetime or a double.

Limitations

The name-value argument VariableNamingRule has these limitations:

• The sqlouterjoin function returns an error if you specify the VariableNamingRule name-value argument with the SQLImportOptions object opts.

• When the VariableNamingRule name-value argument is set to the value "modify":

  • The variable names Properties, RowNames, and VariableNames are reserved identifiers for the table data type.

  • The length of each variable name must be less than the number returned by namelengthmax.

Version History

Introduced in R2018a

R2026a: sqlouterjoin supports DuckDB connection objects

Perform an outer join on two DuckDB database tables by specifying a DuckDB connection object when using the sqlouterjoin function.

R2023a: Selectively join data based on filter condition

You can use the RowFilter when joining data from database tables.