sqlinnerjoin

Inner join between two MySQL database tables

Syntax

data = sqlinnerjoin(conn,lefttable,righttable)

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

Description

data = sqlinnerjoin(conn,lefttable,righttable) returns a table resulting from an inner join between the left and right database tables. This function matches rows using all shared columns, or keys, in both database tables. The inner join retains only the rows that match between the two tables. Executing this function is the equivalent of writing the SQL statement SELECT * FROM lefttable,righttable INNER JOIN lefttable.key = righttable.key.

data = sqlinnerjoin(conn,lefttable,righttable,Name,Value) uses additional options specified by one or more name-value pair arguments. For example, 'Keys','productNumber' specifies using the productNumber column as a key for joining the two database tables.

Examples

Join Two Database Tables Using MySQL Native Interface

Use a MySQL® native interface database connection to import product data from an inner 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 sqlinnerjoin function automatically detects the shared column between the tables. data is a table that contains the matched rows from the two tables.

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

Display the first three rows of matched 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
    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber      SupplierName          City           Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    _________________    __________    ________________    ______________

          8          2.1257e+05          1001             5       "Train Set"                1001         "Wonder Products"    "New York"    "United States"     "212 435 1617"
          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" 

Close the database connection.

close(conn)

Specify Key for Joining Two Database Tables

Use a MySQL® native interface database connection to import joined product data from two MySQL database tables into MATLAB®. Specify the key to use for joining the tables.

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. Specify the key, or shared column, between the tables using the 'Keys' name-value pair argument. data is a table that contains the matched rows from the two tables.

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

Display the first three rows of matched 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
    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber      SupplierName          City           Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    _________________    __________    ________________    ______________

          8          2.1257e+05          1001             5       "Train Set"                1001         "Wonder Products"    "New York"    "United States"     "212 435 1617"
          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" 

Close the database connection.

close(conn)

Input Arguments

conn — MySQL^® native interface database connection

connection object

MySQL native interface database connection, specified as a connection object.

lefttable — Left table

character vector | string scalar

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

Example: 'inventoryTable'

Data Types: char | string

righttable — Right table

character vector | string scalar

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

Example: 'productTable'

Data Types: char | string

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: data = sqlinnerjoin(conn,"productTable","suppliers",'LeftCatalog','toy_store','RightCatalog','toy_shop','MaxRows',5) performs an inner join between left and right tables by specifying the catalog for both tables and returns five matched rows.

'LeftCatalog' — Left catalog

character vector | string scalar

Left catalog, specified as the comma-separated pair consisting of 'LeftCatalog' and a character vector or string scalar. Specify the database catalog name where the left table of the join is stored.

Example: 'LeftCatalog','toy_store'

Data Types: char | string

'RightCatalog' — Right catalog

character vector | string scalar

Right catalog, specified as the comma-separated pair consisting of 'RightCatalog' and a character vector or string scalar. Specify the database catalog name where the right table of the join is stored.

Example: 'RightCatalog','toy_store'

Data Types: char | string

'Keys' — Keys

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

Keys, specified as the comma-separated pair consisting of 'Keys' and a character vector, string scalar, cell array of character vectors, or string array. Specify a character vector or string scalar to indicate one key. For multiple keys, specify a cell array of character vectors or a string array. Use this name-value pair argument to identify the shared keys (columns) between the two tables to join.

You cannot use this name-value pair argument with the 'LeftKeys' and 'RightKeys' name-value pair arguments.

Example: 'Keys','MANAGER_ID'

Data Types: char | string | cell

'LeftKeys' — Left keys

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

Left keys, specified as the comma-separated pair consisting of 'LeftKeys' and a character vector, string scalar, cell array of character vectors, or string array. Specify a character vector or string scalar to indicate one key. For multiple keys, specify a cell array of character vectors or a string array. This name-value pair argument identifies the keys in the left table for the join to the right table.

Use this name-value pair argument with the 'RightKeys' name-value pair argument. Both arguments must specify the same number of keys. The sqlinnerjoin function pairs the values of the keys based on their order.

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

Data Types: char | string | cell

'RightKeys' — Right keys

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

Right keys, specified as the comma-separated pair consisting of 'RightKeys' and a character vector, string scalar, cell array of character vectors, or string array. Specify a character vector or string scalar to indicate one key. For multiple keys, specify a cell array of character vectors or a string array. This name-value pair argument identifies the keys in the right table for the join to the left table.

Use this name-value pair argument with the 'LeftKeys' name-value pair argument. Both arguments must specify the same number of keys. The sqlinnerjoin function pairs the values of the keys based on their order.

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

Data Types: char | string | cell

'MaxRows' — Maximum number of rows to return

positive numeric scalar

Maximum number of rows to return, specified as the comma-separated pair consisting of 'MaxRows' and a positive numeric scalar. By default, the sqlinnerjoin function returns all rows from the executed SQL query. Use this name-value pair argument to limit the number of rows imported into MATLAB^®.

Example: 'MaxRows',10

Data Types: double

'VariableNamingRule' — Variable naming rule

"preserve" (default) | "modify"

Variable naming rule, specified as the comma-separated pair consisting of 'VariableNamingRule' and one of these values:

• "preserve" — Preserve most variable names when the sqlinnerjoin function imports data. For details, see the Limitations section.

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

Example: 'VariableNamingRule',"modify"

Data Types: string

Output Arguments

data — Joined data

table

Joined data, returned as a table that contains the matched rows from the join of the left and right tables. data also contains a variable for each column in the left and right tables.

When you import data, the sqlinnerjoin function converts the data type of each column from the MySQL database to the MATLAB data type. This table maps the data type of a database column to the converted MATLAB data type.

MySQL Data Type	MATLAB Data Type
BIT	logical
TINYINT	double
SMALLINT	double
BIGINT	double
REAL	double
DOUBLE	double
DECIMAL	double
NUMERIC	double
CHAR	string
VARCHAR	string
LONGVARCHAR	string
TIMESTAMP	datetime
DATE	datetime
TIME	duration
YEAR	double
ENUM	categorical
JSON	char

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

Limitations

The name-value pair argument 'VariableNamingRule' has these limitations when it 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.