Create SQL string for joining on matching natural keys

Share:

Description

Create string with SQL expressions for WHERE clause to join two tables on the given columns.

Usage

1
natural_key(table_names, key_columns)

Arguments

table_names

[character(2)]
Name of data base tables to be joined.

key_columns

[character(1:Inf)]
Names of key columns in both tables.

Details

The names of tables and key columns must be valid SQL identifiers. They are validated to conform to the regular expression returned by valid_identifier_regex.

The SQL string is created in 3 steps:

  1. Combine table names with key names, eg, "PRL.FLIGHT_NR".

  2. Create logical expressions, eg, "PRL.FLIGHT_NR = PRL_SSR.FLIGHT_NR"

  3. Concatenate logical expressions by "and" to form final SQL esxpression.

Value

Character string to be used in SQL statement.

Note

The current implementation assumes that key columns have the same names in both tables.

Author(s)

Uwe Block

See Also

valid_identifier_regex.

Examples

1
2
3
4
5
# SQL expression
(sql_expr <- lazysql::natural_key(c("TAB1", "tab_2"),c("COL1", "col_2")))

# sample SQL JOIN statement
paste("select * from TAB1, TAB2 where", sql_expr)