sqlinnerjoin
Syntax
Description
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 data
= sqlinnerjoin(conn
,lefttable
,righttable
)SELECT * FROM lefttable,righttable
INNER JOIN lefttable.key = righttable.key
.
uses additional options specified by one or more name-value arguments. For example, specify
data
= sqlinnerjoin(conn
,lefttable
,righttable
,Name,Value
)Keys = "productNumber"
to use the productNumber
column as a key for joining the two database tables.
Examples
Join Two Database Tables Using PostgreSQL Native Interface
Use a PostgreSQL native interface database connection to import product data from an inner join between two PostgreSQL database tables into MATLAB®.
Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable
and suppliers
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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_1 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)
Specify Key for Joining Two Database Tables
Use a PostgreSQL native interface database connection to import joined product data from two PostgreSQL database tables into MATLAB®. Specify the key to use for joining the tables.
Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable
and suppliers
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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_1 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)
Filter Rows in Joined Data
Use a PostgreSQL native interface database connection to import joined product data from two PostgreSQL database tables into MATLAB®. Specify the row filter condition to use for joining the tables.
Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, username, and password. The database contains the tables productTable
and suppliers
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(datasource,username,password);
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 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_1 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_1 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"
Close the database connection.
close(conn)
Input Arguments
conn
— PostgreSQL native interface database connection
connection
object
PostgreSQL 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 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,"productTable","suppliers",'LeftCatalog',"toystore_doc",'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.
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
LeftSchema
— Left schema
character vector | string scalar
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
RightSchema
— Right schema
character vector | string scalar
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
— 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 thesqlinnerjoin
function imports data. For details, see the Limitations section."modify"
— Remove non-ASCII characters from variable names when thesqlinnerjoin
function imports data.
Example: 'VariableNamingRule',"modify"
Data Types: string
RowFilter
— Row filter condition
<unconstrained>
(default) | matlab.io.RowFilter
object
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
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.
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
.
When you import data, the sqlinnerjoin
function converts the
data type of each column from the PostgreSQL database to the MATLAB data type. This table maps the data type of a database column to the
converted MATLAB data type. The first and second columns contain the scalar data type
mappings, whereas the third and fourth columns contain the array data type
mappings.
Details for how MATLAB reads PostgreSQL array types consist of the following:
MATLAB reads columns of PostgreSQL array types into cell arrays. The dimensions of the array in each cell match the dimension of the array stored in the PostgreSQL database table row.
The data of the arrays in the MATLAB cell arrays depends on the underlying data in the PostgreSQL arrays.
The default data type of each PostgreSQL array type matches its scalar equivalent.
Scalar Data Type Mappings | Array Data Type Mappings (since R2024b) | ||
---|---|---|---|
PostgreSQL | MATLAB | PostgreSQL | MATLAB |
|
|
| Cell array of logical values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
| N/A | N/A |
|
| N/A | N/A |
|
| N/A | N/A |
|
|
| Cell array of double values |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of datetimes |
|
|
| Cell array of datetimes |
|
|
| Cell array of datetimes |
|
|
| Cell array of datetimes |
|
|
| Cell array of durations |
|
|
| Cell array of durations |
|
|
| Cell array of calendarDurations |
|
|
| Cell array of calendarDurations |
|
|
| Cell array of categoricals |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
Limitations
The name-value argument VariableNamingRule
has these limitations if it
is set to the value "modify"
:
The variable names
Properties
,RowNames
, andVariableNames
are reserved identifiers for thetable
data type.The length of each variable name must be less than the number returned by
namelengthmax
.
Version History
Introduced in R2020bR2024b: Support for reading array types
You can now read array types without any extra data conversions.
R2023a: Selectively join data based on filter condition
You can use the RowFilter
when joining data from database
tables.
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.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- 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)