sqlinnerjoin

Inner join between two database tables

collapse all in page

Syntax

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

Description

example

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.

example

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

collapse all

Open Script

Use an ODBC connection to import product data from an inner join between two Microsoft® SQL Server® database tables into MATLAB®.

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 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 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)
Open Script

Use an ODBC connection to import product data from an inner 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 sqlinnerjoin 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 rows from the two tables.

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

Display the first three rows of matched 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)
Open Script

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 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 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)
Open Script

Use an ODBC connection to import employee data from an inner join between two Microsoft® SQL Server® database tables into MATLAB®. Specify the left and right keys for the join.

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 employees and departments.

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, employees and departments, to find the managers for particular departments. The employees table is the left table of the join, and the departments table is the right table of the join. Here, the column names of the keys are different. Specify the MANAGER_ID key in the left table using the 'LeftKeys' name-value pair argument. Specify the DEPT_MANAGER_ID key in the right table using the 'RightKeys' name-value pair argument.

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

lefttable = 'employees';
righttable = 'departments';
data = sqlinnerjoin(conn,lefttable,righttable,'LeftKeys','MANAGER_ID', ...
    'RightKeys','DEPT_MANAGER_ID');

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×15 table

    EMPLOYEE_ID    FIRST_NAME    LAST_NAME      EMAIL        PHONE_NUMBER           HIRE_DATE           JOB_ID      SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID    DEPARTMENT_ID_1    DEPARTMENT_NAME    DEPT_MANAGER_ID    LOCATION_ID
    ___________    __________    _________    __________    ______________    _____________________    _________    ______    ______________    __________    _____________    _______________    _______________    _______________    ___________

        101         'Neena'      'Kochhar'    'NKOCHHAR'    '515.123.4568'    '2005-09-21 00:00:00'    'AD_VP'      17000          NaN             100             90                90             'Executive'            100             1700    
        102         'Lex'        'De Haan'    'LDEHAAN'     '515.123.4569'    '2001-01-13 00:00:00'    'AD_VP'      17000          NaN             100             90                90             'Executive'            100             1700    
        104         'Bruce'      'Ernst'      'BERNST'      '590.423.4568'    '2007-05-21 00:00:00'    'IT_PROG'     6000          NaN             103             60                60             'IT'                   103             1400

Close the database connection.

close(conn)
Open Script

Use an ODBC connection to import joined product data from two Microsoft® SQL Server® database tables into MATLAB®. Specify the number of rows to return.

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 sqlinnerjoin function automatically detects the shared column between the tables. Specify the number of rows to return using the 'MaxRows' name-value pair argument.

lefttable = 'productTable';
righttable = 'suppliers';
data = sqlinnerjoin(conn,lefttable,righttable,'MaxRows',3)

data =

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

data is a table that contains three of the matched rows from the two tables. The columns from the right table appear to the right of the columns from the left table.

Close the database connection.

close(conn)
Open Live Script

Import joined product data from two Microsoft® SQL Server® database tables into MATLAB® by using an ODBC connection. One of the tables contains a variable name with a non-ASCII character. When importing data, preserve the names of all the variables.

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

datasource = "MSSQLServerAuth";
conn = database(datasource,"","");

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

conn.Message
ans =

     []

Add a column to the database table productTable. The column name contains a non-ASCII character.

sqlquery = "ALTER TABLE productTable ADD tamaño varchar(30)"; 
execute(conn,sqlquery)

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. Specify the number of rows to return using the 'MaxRows' name-value pair argument. 

lefttable = 'productTable'; 
righttable = 'suppliers'; 
data = sqlinnerjoin(conn,lefttable,righttable,'MaxRows',3) 
data=3×11 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription       tama_o      SupplierNumber       SupplierName            City             Country             FaxNumber    
    _____________    ___________    ______________    ________    ___________________    __________    ______________    ___________________    ____________    __________________    ________________

          1          4.0035e+05          1001            14       {'Building Blocks'}    {0×0 char}         1001         {'Wonder Products'}    {'New York'}    {'United States' }    {'212 435 1617'}
          2          4.0031e+05          1002             9       {'Painting Set'   }    {0×0 char}         1002         {'Terrific Toys'  }    {'London'  }    {'United Kingdom'}    {'44 456 9345' }
          8          2.1257e+05          1001             5       {'Train Set'      }    {0×0 char}         1001         {'Wonder Products'}    {'New York'}    {'United States' }    {'212 435 1617'}

data is a table that contains three of the matched rows from the two tables. The sqlinnerjoin function converts the name of the new variable into ASCII characters.

Preserve the name of the variable that contains the non-ASCII character by specifying the VariableNamingRule name-value pair argument. Import the data again.

data = sqlinnerjoin(conn,lefttable,righttable,'MaxRows',3, ...
    'VariableNamingRule',"preserve") 
data=3×11 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription       tamaño      SupplierNumber       SupplierName            City             Country             FaxNumber    
    _____________    ___________    ______________    ________    ___________________    __________    ______________    ___________________    ____________    __________________    ________________

          1          4.0035e+05          1001            14       {'Building Blocks'}    {0×0 char}         1001         {'Wonder Products'}    {'New York'}    {'United States' }    {'212 435 1617'}
          2          4.0031e+05          1002             9       {'Painting Set'   }    {0×0 char}         1002         {'Terrific Toys'  }    {'London'  }    {'United Kingdom'}    {'44 456 9345' }
          8          2.1257e+05          1001             5       {'Train Set'      }    {0×0 char}         1001         {'Wonder Products'}    {'New York'}    {'United States' }    {'212 435 1617'}

The sqlinnerjoin function preserves the non-ASCII character in the variable name.

Close the database connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object created with the database function.

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

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,lefttable,righttable,'LeftCatalog','toy_store','LeftSchema','dbo','RightCatalog','toy_shop','RightSchema','toys','MaxRows',5) performs an inner join between left and right tables by specifying the catalog and schema for both tables and returns five matched rows.

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

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

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

Example: 'LeftSchema','dbo'

Data Types: char | string

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

Example: 'RightSchema','dbo'

Data Types: char | string

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

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

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

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

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

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

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

Example: 'VariableNamingRule',"modify"

Data Types: string

Output Arguments

collapse all

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.

For columns that have numeric data types in the database table, the variable data types in data are double by default. For columns that have text, date, time, or timestamp data types in the database table, the variable data types are cell arrays of character vectors by default.

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:

  • The sqlinnerjoin function returns an error when you use the 'VariableNamingRule' name-value pair argument with the SQLImportOptions object opts.

  • When the 'VariableNamingRule' name-value pair 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.

See Also

| | | |

Topics

Introduced in R2018a

Database Toolbox Documentation

Support