sqlouterjoin

Outer join between two PostgreSQL database tables

Since R2020b

Syntax

data = sqlouterjoin(conn,lefttable,righttable)

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

Description

data = sqlouterjoin(conn,lefttable,righttable) returns a table resulting from an outer join between the left and right database tables. This function matches rows using all shared columns, or keys, in both database tables. The outer join retains the matched and unmatched rows between the two tables. Executing this function is the equivalent of writing the SQL statement SELECT * FROM lefttable,righttable OUTER JOIN lefttable.key = righttable.key.

example

data = sqlouterjoin(conn,lefttable,righttable,Name,Value) uses additional options specified by one or more name-value arguments. For example, specify Keys = "productNumber" to use the productNumber column as a key for joining the two database tables.

example

Examples

collapse all

Join Two Database Tables Using PostgreSQL Native Interface

Open Live Script

Use a PostgreSQL native interface database connection to import product data from an outer 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 sqlouterjoin function automatically detects the shared column between the tables. 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 (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

Open Live Script

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

lefttable = "productTable";
righttable = "suppliers";
data = sqlouterjoin(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

Open Live Script

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

lefttable = "productTable";
righttable = "suppliers";
data = sqlouterjoin(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 = sqlouterjoin(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

collapse all

`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 = sqlouterjoin(conn,"productTable","suppliers",'Type','left','MaxRows',5) performs an outer left join between left and right tables and returns five rows of the joined data.

`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 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 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 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 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 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 this value as a character vector or string scalar.

Example: 'Type','left'

`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 the sqlouterjoin function imports data. For details, see the Limitations section.
"modify" — Remove non-ASCII characters from variable names when the sqlouterjoin 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; sqlouterjoin(conn,lefttable,righttable,"RowFilter",rf)

Output Arguments

collapse all

`data` — Joined data
table

Joined 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.

If the column names are shared between the joined database tables and have the same case, then the sqlouterjoin 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.

When you import data, the sqlouterjoin 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
`Boolean`	`logical`	`Boolean []`	Cell array of logical values
`Smallint`	`double`	`Smallint []`	Cell array of double values
`Integer`	`double`	`Integer []`	Cell array of double values
`Bigint`	`double`	`Bigint []`	Cell array of double values
`Decimal`	`double`	`Decimal []`	Cell array of double values
`Numeric`	`double`	`Numeric []`	Cell array of double values
`Real`	`double`	`Real []`	Cell array of double values
`Double precision`	`double`	`Double precision []`	Cell array of double values
`Smallserial`	`double`	N/A	N/A
`Serial`	`double`	N/A	N/A
`Bigserial`	`double`	N/A	N/A
`Money`	`double`	`Money []`	Cell array of double values
`Varchar`	`string`	`Varchar []`	Cell array of strings
`Char`	`string`	`Char []`	Cell array of strings
`Text`	`string`	`Text []`	Cell array of strings
`Bytea`	`string`	`Bytea []`	Cell array of strings
`Timestamp`	`datetime`	`Timestamp []`	Cell array of datetimes
`Timestampz`	`datetime`	`Timestampz []`	Cell array of datetimes
`Abstime`	`datetime`	`Abstime []`	Cell array of datetimes
`Date`	`datetime`	`Date []`	Cell array of datetimes
`Time`	`duration`	`Time []`	Cell array of durations
`Timez`	`duration`	`Timez []`	Cell array of durations
`Interval`	`calendarDuration`	`Interval []`	Cell array of calendarDurations
`Reltime`	`calendarDuration`	`Reltime []`	Cell array of calendarDurations
`Enum`	`categorical`	`Enum []`	Cell array of categoricals
`Cidr`	`string`	`Cidr []`	Cell array of strings
`Inet`	`string`	`Inet []`	Cell array of strings
`Macaddr`	`string`	`Macaddr []`	Cell array of strings
`Uuid`	`string`	`Uuid []`	Cell array of strings
`Xml`	`string`	`Xml []`	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, 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.

Version History

Introduced in R2020b

expand all

R2024b: 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.

sqlouterjoin

Syntax

Description

Examples

Join Two Database Tables Using PostgreSQL Native Interface

Specify Key for Joining Two Database Tables

Filter Rows in Joined Data

Input Arguments

`conn` — PostgreSQL native interface database connection
`connection` object

`lefttable` — Left table
character vector | string scalar

`righttable` — Right table
character vector | string scalar

Name-Value Arguments

`LeftCatalog` — Left catalog
character vector | string scalar

`RightCatalog` — Right catalog
character vector | string scalar

`LeftSchema` — Left schema
character vector | string scalar

`RightSchema` — Right schema
character vector | string scalar

`Keys` — Keys
character vector | string scalar | cell array of character vectors | string array

`LeftKeys` — Left keys
character vector | string scalar | cell array of character vectors | string array

`RightKeys` — Right keys
character vector | string scalar | cell array of character vectors | string array

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`Type` — Outer join type
`'full'` (default) | `'left'` | `'right'`

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

Output Arguments

`data` — Joined data
table

Limitations

Version History

R2024b: Support for reading array types

R2023a: Selectively join data based on filter condition

See Also

Topics

sqlouterjoin

Syntax

Description

Examples

Join Two Database Tables Using PostgreSQL Native Interface

Specify Key for Joining Two Database Tables

Filter Rows in Joined Data

Input Arguments

conn — PostgreSQL native interface database connection connection object

lefttable — Left table character vector | string scalar

righttable — Right table character vector | string scalar

Name-Value Arguments

LeftCatalog — Left catalog character vector | string scalar

RightCatalog — Right catalog character vector | string scalar

LeftSchema — Left schema character vector | string scalar

RightSchema — Right schema character vector | string scalar

Keys — Keys character vector | string scalar | cell array of character vectors | string array

LeftKeys — Left keys character vector | string scalar | cell array of character vectors | string array

RightKeys — Right keys character vector | string scalar | cell array of character vectors | string array

MaxRows — Maximum number of rows to return positive numeric scalar

Type — Outer join type 'full' (default) | 'left' | 'right'

VariableNamingRule — Variable naming rule "preserve" (default) | "modify"

RowFilter — Row filter condition <unconstrained> (default) | matlab.io.RowFilter object

Output Arguments

data — Joined data table

Limitations

Version History

R2024b: Support for reading array types

R2023a: Selectively join data based on filter condition

See Also

Topics

WeChat

`conn` — PostgreSQL native interface database connection
`connection` object

`lefttable` — Left table
character vector | string scalar

`righttable` — Right table
character vector | string scalar

`LeftCatalog` — Left catalog
character vector | string scalar

`RightCatalog` — Right catalog
character vector | string scalar

`LeftSchema` — Left schema
character vector | string scalar

`RightSchema` — Right schema
character vector | string scalar

`Keys` — Keys
character vector | string scalar | cell array of character vectors | string array

`LeftKeys` — Left keys
character vector | string scalar | cell array of character vectors | string array

`RightKeys` — Right keys
character vector | string scalar | cell array of character vectors | string array

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`Type` — Outer join type
`'full'` (default) | `'left'` | `'right'`

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

`data` — Joined data
table