sqlouterjoin

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.

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

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)

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)

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 and unmatched rows from the two tables.

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

Display the last three unmatched rows of joined data. Display the last five variables of the joined data.

tail(data(:,end-4:end),3)

ans =

  3×5 table

    DEPARTMENT_ID    DEPARTMENT_ID_1     DEPARTMENT_NAME     DEPT_MANAGER_ID    LOCATION_ID
    _____________    _______________    _________________    _______________    ___________

         NaN               230          'IT Helpdesk'              NaN             1700    
         NaN                40          'Human Resources'          203             2400    
         NaN                10          'Administration'           200             1700    

Close the database connection.

close(conn)

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. Create a right join using the 'Type' name-value pair argument.

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

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

Display the last three unmatched rows of joined data. Display the last five variables of the joined data.

tail(data(:,end-4:end),3)

ans =

  3×5 table

    DEPARTMENT_ID    DEPARTMENT_ID_1    DEPARTMENT_NAME    DEPT_MANAGER_ID    LOCATION_ID
    _____________    _______________    _______________    _______________    ___________

         NaN               250          'Retail Sales'           NaN             1700    
         NaN               260          'Recruiting'             NaN             1700    
         NaN               270          'Payroll'                NaN             1700    

Close the database connection.

close(conn)

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

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

data =

  3×10 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription    SupplierNumber            SupplierName                City            Country           FaxNumber   
    _____________    ___________    ______________    ________    __________________    ______________    _____________________________    ___________    _______________    _______________

          7          3.8912e+05          1007            16        'Engine Kit'              1007         'Garvin's Electrical  Gizmos'    'Wellesley'    'United States'    '617 919 3456' 
          8          2.1257e+05          1001             5        'Train Set'               1001         'Wonder Products'                'New York'     'United States'    '212 435 1617' 
          9          1.2597e+05          1003            13        'Victorian Doll'          1003         'Wacky Widgets'                  'Adelaide'     'Australia'        '618 8490 2211'

data is a table that contains three of the matched and unmatched 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)