sqlinnerjoin
Inner join between two database tables
Syntax
Description
data = sqlinnerjoin(conn,lefttable,righttable)SELECT * FROM lefttable,righttable INNER JOIN lefttable.key =
righttable.key.
data = sqlinnerjoin(conn,lefttable,righttable,Name,Value)Keys = "productNumber" to use the
productNumber column as a key for joining the two database
tables.
Examples
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)
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)
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)
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)
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)
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)
Use an ODBC connection to import product data from an inner join between two Microsoft® SQL Server® database tables into MATLAB®. Specify the row filter condition to use for joining the tables.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username 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 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 sqlinnerjoin function automatically detects the shared column between the tables. The table data contains the matched rows from the two tables.
lefttable = "productTable"; righttable = "suppliers"; data = sqlinnerjoin(conn,lefttable,righttable);
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'}
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' }
4 4.0034e+05 1008 21 {'Space Cruiser' } 1008 {'The Great Train Company'} {'Nashua' } {'United States' } {'403 121 3478'}
5 4.0046e+05 1005 3 {'Tin Soldier' } 1005 {'Custers Tin Soldiers' } {'Boston' } {'United States' } {'617 939 1234'}
Join the same tables, but this time use a row filter. The filter condition is that unitCost must be less than 15. Again, display the first five rows of matched data.
rf = rowfilter("unitCost"); rf = rf.unitCost < 15; data = sqlinnerjoin(conn,lefttable,righttable,"RowFilter",rf); 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'}
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'}
6 4.0088e+05 1004 8 {'Sail Boat' } 1004 {'Incredible Machines' } {'Dublin' } {'Ireland' } {'01 222 3456' }
8 2.1257e+05 1001 5 {'Train Set' } 1001 {'Wonder Products' } {'New York'} {'United States' } {'212 435 1617'}
Input Arguments
Database connection, specified as an ODBC connection
object or JDBC connection object created using 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 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 =
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 one of the following:
"modify"— Remove non-ASCII characters from variable names when thesqlinnerjoinfunction imports data."preserve"— Preserve most variable names when thesqlinnerjoinfunction imports data.
Example: VariableNamingRule="modify"
Row filter condition, specified as a
matlab.io.RowFilter object.
Example: rf = rowfilter("productnumber"); rf =
rf.productnumber <= 5;
sqlinnerjoin(conn,lefttable,righttable,"RowFilter",rf)
Output Arguments
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 argument VariableNamingRule has these
limitations:
The
sqlinnerjoinfunction returns an error if you specify theVariableNamingRulename-value argument with theSQLImportOptionsobjectopts.When the
VariableNamingRulename-value argument is set to the value"modify":The variable names
Properties,RowNames, andVariableNamesare reserved identifiers for thetabledata type.The length of each variable name must be less than the number returned by
namelengthmax.
Version History
Introduced in R2018aYou can use the RowFilter when joining data from database
tables.
See Also
sqlfind | sqlread | sqlouterjoin | database | close
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Sélectionner un site web
Choisissez un site web pour accéder au contenu traduit dans votre langue (lorsqu'il est disponible) et voir les événements et les offres locales. D’après votre position, nous vous recommandons de sélectionner la région suivante : .
Vous pouvez également sélectionner un site web dans la liste suivante :
Comment optimiser les performances du site
Pour optimiser les performances du site, sélectionnez la région Chine (en chinois ou en anglais). Les sites de MathWorks pour les autres pays ne sont pas optimisés pour les visites provenant de votre région.
Amériques
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)