Outer join between two database tables
data = sqlouterjoin(conn,lefttable,righttable)SELECT * FROM lefttable,righttable OUTER JOIN lefttable.key =
righttable.key.
data = sqlouterjoin(conn,lefttable,righttable,Name,Value)'Keys','productNumber' specifies using the
productNumber column as a key for joining the two database
tables.
Use an ODBC connection to import product data from an outer 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 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)
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)
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)
Use an ODBC connection to import employee data from an outer 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 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)
Use an ODBC connection to import joined employee data from two Microsoft® SQL Server® database tables into MATLAB®. Create a right join and 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. 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)
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 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)
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 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×11 table
productNumber stockNumber supplierNumber unitCost productDescription tama_o SupplierNumber SupplierName City Country FaxNumber
_____________ ___________ ______________ ________ __________________ __________ ______________ _______________________________ _____________ _________________ _________________
7 3.8912e+05 1007 16 {'Engine Kit' } {0×0 char} 1007 {'Garvin's Electrical Gizmos'} {'Wellesley'} {'United States'} {'617 919 3456' }
8 2.1257e+05 1001 5 {'Train Set' } {0×0 char} 1001 {'Wonder Products' } {'New York' } {'United States'} {'212 435 1617' }
9 1.2597e+05 1003 13 {'Victorian Doll'} {0×0 char} 1003 {'Wacky Widgets' } {'Adelaide' } {'Australia' } {'618 8490 2211'}
data is a table that contains three of the matched rows from the two tables. The sqlouterjoin 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 = sqlouterjoin(conn,lefttable,righttable,'MaxRows',3, ... 'VariableNamingRule',"preserve")
data=3×11 table
productNumber stockNumber supplierNumber unitCost productDescription tamaño SupplierNumber SupplierName City Country FaxNumber
_____________ ___________ ______________ ________ __________________ __________ ______________ _______________________________ _____________ _________________ _________________
7 3.8912e+05 1007 16 {'Engine Kit' } {0×0 char} 1007 {'Garvin's Electrical Gizmos'} {'Wellesley'} {'United States'} {'617 919 3456' }
8 2.1257e+05 1001 5 {'Train Set' } {0×0 char} 1001 {'Wonder Products' } {'New York' } {'United States'} {'212 435 1617' }
9 1.2597e+05 1003 13 {'Victorian Doll'} {0×0 char} 1003 {'Wacky Widgets' } {'Adelaide' } {'Australia' } {'618 8490 2211'}
The sqlouterjoin function preserves the non-ASCII character in the variable name.
Close the database connection.
close(conn)
conn — Database connectionconnection objectDatabase connection, specified as an ODBC connection
object or JDBC connection object created using the
database function.
lefttable — Left tableLeft 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 tableRight 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
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.
data =
sqlouterjoin(conn,lefttable,righttable,'Type','left','MaxRows',5)
performs an outer left join between left and right tables and returns five rows of
the joined data.'LeftCatalog' — Left catalogLeft 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 catalogRight 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
'LeftSchema' — Left schemaLeft 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
'RightSchema' — Right schemaRight 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' — KeysKeys, 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 keysLeft 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
sqlouterjoin 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 keysRight 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 sqlouterjoin 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 returnMaximum number of rows to return, specified as the comma-separated pair consisting of
'MaxRows' and a positive numeric scalar. By default, the
sqlouterjoin 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
'Type' — Outer join type'full' (default) | 'left' | 'right'Outer join type, specified as the comma-separated pair consisting of
'Type' and one of these values:
'full' — A full join retrieves records
that have matching values in the selected column of both
tables, and unmatched records from both the left and right
tables.
'left' — A left join retrieves records
that have matching values in the selected column of both
tables, and unmatched records from the left table
only.
'right' — A right join retrieves
records that have matching values in the selected column of
both tables, and unmatched records from the right table
only.
You can specify these values as a character vector or string scalar.
Not all databases support all join types. For an unsupported database,
you must use the sqlread function to import data from both tables into
MATLAB. Then, use the outerjoin function to
join tables in the MATLAB workspace.
Example: 'Type','left'
'VariableNamingRule' — Variable naming rule"modify" (default) | "preserve"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 sqlouterjoin function imports data.
"preserve" — Preserve most variable names when the
sqlouterjoin function imports data. For details, see
the Limitations section.
Example: 'VariableNamingRule',"modify"
Data Types: string
data — Joined dataJoined 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 name-value pair argument 'VariableNamingRule' has these limitations:
The sqlouterjoin 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.
close | database | sqlfind | sqlinnerjoin | sqlread
You have a modified version of this example. Do you want to open this example with your edits?