Teradata R Package Function Reference | 17.00 - 17.00 - Set Operations - Teradata R Package

Teradata® R Package Function Reference

prodname
Teradata R Package
vrm_release
17.00
created_date
September 2020
category
Programming Reference
featnum
B700-4007-090K

Introduction to Set Operations

The goal of this vignette is to get the user familiar with the usage of objects of class “tbl_teradata” in the following set operations provided by the dbplyr package:

  • union (equivalent to Teradata’s UNION)
  • union-all (equivalent to Teradata’s UNION-ALL)
  • intersect (equivalent to Teradata’s INTERSECT)
  • setdiff (equivalent to Teradata’s MINUS and EXCEPT)

Each set operation has a section in this vignette. Each section can be read in isolation, except where references are made to other sections or other vignettes.

Notes

  • dbplyr does not provide set operations equivalent to INTERSECT ALL and MINUS/EXCEPT ALL.

Once the connection is established using td_create_context, you can load the data mtcars and then copy into the tables using copy_to and create respective objects(s) of class “tbl_teradata” using the following statements:

first <- mtcars[1:20, ]
second <- mtcars[10:32, ]

# Copy into a remote data source
df_first <- copy_to(con, first, name = "mtcars_first", row.names = TRUE)
df_second <- copy_to(con, second, name = "mtcars_second", row.names = TRUE)
# Print data.frame instead of tbl_teradata to display all rows.
print(as.data.frame(df_first))
#>              row_names  mpg cyl  disp  hp drat   wt qsec vs am gear carb
#> 1              Valiant 18.1   6 225.0 105 2.76 3.46 20.2  1  0    3    1
#> 2    Chrysler Imperial 14.7   8 440.0 230 3.23 5.34 17.4  0  0    3    4
#> 3          Honda Civic 30.4   4  75.7  52 4.93 1.61 18.5  1  1    4    2
#> 4           Datsun 710 22.8   4 108.0  93 3.85 2.32 18.6  1  1    4    1
#> 5  Lincoln Continental 10.4   8 460.0 215 3.00 5.42 17.8  0  0    3    4
#> 6        Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.88 17.0  0  1    4    4
#> 7            Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
#> 8            Mazda RX4 21.0   6 160.0 110 3.90 2.62 16.5  0  1    4    4
#> 9       Toyota Corolla 33.9   4  71.1  65 4.22 1.83 19.9  1  1    4    1
#> 10   Hornet Sportabout 18.7   8 360.0 175 3.15 3.44 17.0  0  0    3    2
#> 11      Hornet 4 Drive 21.4   6 258.0 110 3.08 3.21 19.4  1  0    3    1
#> 12            Merc 230 22.8   4 140.8  95 3.92 3.15 22.9  1  0    4    2
#> 13            Fiat 128 32.4   4  78.7  66 4.08 2.20 19.5  1  1    4    1
#> 14          Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
#> 15            Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
#> 16          Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
#> 17          Duster 360 14.3   8 360.0 245 3.21 3.57 15.8  0  0    3    4
#> 18           Merc 240D 24.4   4 146.7  62 3.69 3.19 20.0  1  0    4    2
#> 19  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.25 18.0  0  0    3    4
#> 20         Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3

# Print data.frame instead of tbl_teradata to display all rows.
print(as.data.frame(df_second))
#>              row_names  mpg cyl  disp  hp drat   wt qsec vs am gear carb
#> 1        Maserati Bora 15.0   8 301.0 335 3.54 3.57 14.6  0  1    5    8
#> 2    Chrysler Imperial 14.7   8 440.0 230 3.23 5.34 17.4  0  0    3    4
#> 3          Honda Civic 30.4   4  75.7  52 4.93 1.61 18.5  1  1    4    2
#> 4             Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
#> 5        Porsche 914-2 26.0   4 120.3  91 4.43 2.14 16.7  0  1    5    2
#> 6           Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
#> 7       Ford Pantera L 15.8   8 351.0 264 4.22 3.17 14.5  0  1    5    4
#> 8            Fiat X1-9 27.3   4  79.0  66 4.08 1.94 18.9  1  1    4    1
#> 9  Lincoln Continental 10.4   8 460.0 215 3.00 5.42 17.8  0  0    3    4
#> 10       Toyota Corona 21.5   4 120.1  97 3.70 2.46 20.0  1  0    3    1
#> 11           Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
#> 12         AMC Javelin 15.2   8 304.0 150 3.15 3.44 17.3  0  0    3    2
#> 13      Toyota Corolla 33.9   4  71.1  65 4.22 1.83 19.9  1  1    4    1
#> 14    Pontiac Firebird 19.2   8 400.0 175 3.08 3.85 17.1  0  0    3    2
#> 15    Dodge Challenger 15.5   8 318.0 150 2.76 3.52 16.9  0  0    3    2
#> 16         Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3
#> 17            Fiat 128 32.4   4  78.7  66 4.08 2.20 19.5  1  1    4    1
#> 18  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.25 18.0  0  0    3    4
#> 19          Camaro Z28 13.3   8 350.0 245 3.73 3.84 15.4  0  0    3    4
#> 20          Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
#> 21        Ferrari Dino 19.7   6 145.0 175 3.62 2.77 15.5  0  1    5    6
#> 22          Volvo 142E 21.4   4 121.0 109 4.11 2.78 18.6  1  1    4    2
#> 23        Lotus Europa 30.4   4  95.1 113 3.77 1.51 16.9  1  1    5    2

Union operation

union function combines two or more objects of class “tbl_teradata” into a single result tbl_teradata.

df_union <- df_first %>% union(df_second)

# Print data.frame instead of tbl_teradata to display full results.
print(as.data.frame(df_union))
#>              row_names  mpg cyl  disp  hp drat   wt qsec vs am gear carb
#> 1          AMC Javelin 15.2   8 304.0 150 3.15 3.44 17.3  0  0    3    2
#> 2   Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.25 18.0  0  0    3    4
#> 3           Camaro Z28 13.3   8 350.0 245 3.73 3.84 15.4  0  0    3    4
#> 4    Chrysler Imperial 14.7   8 440.0 230 3.23 5.34 17.4  0  0    3    4
#> 5           Datsun 710 22.8   4 108.0  93 3.85 2.32 18.6  1  1    4    1
#> 6     Dodge Challenger 15.5   8 318.0 150 2.76 3.52 16.9  0  0    3    2
#> 7           Duster 360 14.3   8 360.0 245 3.21 3.57 15.8  0  0    3    4
#> 8         Ferrari Dino 19.7   6 145.0 175 3.62 2.77 15.5  0  1    5    6
#> 9             Fiat 128 32.4   4  78.7  66 4.08 2.20 19.5  1  1    4    1
#> 10           Fiat X1-9 27.3   4  79.0  66 4.08 1.94 18.9  1  1    4    1
#> 11      Ford Pantera L 15.8   8 351.0 264 4.22 3.17 14.5  0  1    5    4
#> 12         Honda Civic 30.4   4  75.7  52 4.93 1.61 18.5  1  1    4    2
#> 13      Hornet 4 Drive 21.4   6 258.0 110 3.08 3.21 19.4  1  0    3    1
#> 14   Hornet Sportabout 18.7   8 360.0 175 3.15 3.44 17.0  0  0    3    2
#> 15 Lincoln Continental 10.4   8 460.0 215 3.00 5.42 17.8  0  0    3    4
#> 16        Lotus Europa 30.4   4  95.1 113 3.77 1.51 16.9  1  1    5    2
#> 17       Maserati Bora 15.0   8 301.0 335 3.54 3.57 14.6  0  1    5    8
#> 18           Mazda RX4 21.0   6 160.0 110 3.90 2.62 16.5  0  1    4    4
#> 19       Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.88 17.0  0  1    4    4
#> 20            Merc 230 22.8   4 140.8  95 3.92 3.15 22.9  1  0    4    2
#> 21           Merc 240D 24.4   4 146.7  62 3.69 3.19 20.0  1  0    4    2
#> 22            Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
#> 23           Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
#> 24          Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
#> 25          Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
#> 26         Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3
#> 27    Pontiac Firebird 19.2   8 400.0 175 3.08 3.85 17.1  0  0    3    2
#> 28       Porsche 914-2 26.0   4 120.3  91 4.43 2.14 16.7  0  1    5    2
#> 29      Toyota Corolla 33.9   4  71.1  65 4.22 1.83 19.9  1  1    4    1
#> 30       Toyota Corona 21.5   4 120.1  97 3.70 2.46 20.0  1  0    3    1
#> 31             Valiant 18.1   6 225.0 105 2.76 3.46 20.2  1  0    3    1
#> 32          Volvo 142E 21.4   4 121.0 109 4.11 2.78 18.6  1  1    4    2

Union_all operation

union_all function combines two or more objects of class “tbl_teradata” into a single result tbl_teradata without removing duplicate rows.

df_union_all <- df_first %>% union_all(df_second)

# Print data.frame instead of tbl_teradata to display full results.
print(as.data.frame(df_union_all))
#>              row_names  mpg cyl  disp  hp drat   wt qsec vs am gear carb
#> 1              Valiant 18.1   6 225.0 105 2.76 3.46 20.2  1  0    3    1
#> 2    Chrysler Imperial 14.7   8 440.0 230 3.23 5.34 17.4  0  0    3    4
#> 3          Honda Civic 30.4   4  75.7  52 4.93 1.61 18.5  1  1    4    2
#> 4           Datsun 710 22.8   4 108.0  93 3.85 2.32 18.6  1  1    4    1
#> 5  Lincoln Continental 10.4   8 460.0 215 3.00 5.42 17.8  0  0    3    4
#> 6        Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.88 17.0  0  1    4    4
#> 7            Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
#> 8            Mazda RX4 21.0   6 160.0 110 3.90 2.62 16.5  0  1    4    4
#> 9       Toyota Corolla 33.9   4  71.1  65 4.22 1.83 19.9  1  1    4    1
#> 10   Hornet Sportabout 18.7   8 360.0 175 3.15 3.44 17.0  0  0    3    2
#> 11      Hornet 4 Drive 21.4   6 258.0 110 3.08 3.21 19.4  1  0    3    1
#> 12         Honda Civic 30.4   4  75.7  52 4.93 1.61 18.5  1  1    4    2
#> 13      Ford Pantera L 15.8   8 351.0 264 4.22 3.17 14.5  0  1    5    4
#> 14           Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
#> 15            Merc 230 22.8   4 140.8  95 3.92 3.15 22.9  1  0    4    2
#> 16            Fiat 128 32.4   4  78.7  66 4.08 2.20 19.5  1  1    4    1
#> 17          Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
#> 18    Dodge Challenger 15.5   8 318.0 150 2.76 3.52 16.9  0  0    3    2
#> 19            Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
#> 20          Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
#> 21       Maserati Bora 15.0   8 301.0 335 3.54 3.57 14.6  0  1    5    8
#> 22       Porsche 914-2 26.0   4 120.3  91 4.43 2.14 16.7  0  1    5    2
#> 23 Lincoln Continental 10.4   8 460.0 215 3.00 5.42 17.8  0  0    3    4
#> 24      Toyota Corolla 33.9   4  71.1  65 4.22 1.83 19.9  1  1    4    1
#> 25            Fiat 128 32.4   4  78.7  66 4.08 2.20 19.5  1  1    4    1
#> 26          Duster 360 14.3   8 360.0 245 3.21 3.57 15.8  0  0    3    4
#> 27          Camaro Z28 13.3   8 350.0 245 3.73 3.84 15.4  0  0    3    4
#> 28           Merc 240D 24.4   4 146.7  62 3.69 3.19 20.0  1  0    4    2
#> 29          Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
#> 30  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.25 18.0  0  0    3    4
#> 31   Chrysler Imperial 14.7   8 440.0 230 3.23 5.34 17.4  0  0    3    4
#> 32          Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
#> 33       Toyota Corona 21.5   4 120.1  97 3.70 2.46 20.0  1  0    3    1
#> 34    Pontiac Firebird 19.2   8 400.0 175 3.08 3.85 17.1  0  0    3    2
#> 35  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.25 18.0  0  0    3    4
#> 36        Ferrari Dino 19.7   6 145.0 175 3.62 2.77 15.5  0  1    5    6
#> 37         Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3
#> 38            Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
#> 39           Fiat X1-9 27.3   4  79.0  66 4.08 1.94 18.9  1  1    4    1
#> 40         AMC Javelin 15.2   8 304.0 150 3.15 3.44 17.3  0  0    3    2
#> 41         Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3
#> 42          Volvo 142E 21.4   4 121.0 109 4.11 2.78 18.6  1  1    4    2
#> 43        Lotus Europa 30.4   4  95.1 113 3.77 1.51 16.9  1  1    5    2

Intersect operation

intersect function returns the data that exist in both the objects of class “tbl_teradata”.

df_intersect <- df_first %>% intersect(df_second)

# Print data.frame instead of tbl_teradata to display full results.
print(as.data.frame(df_intersect))
#>              row_names  mpg cyl  disp  hp drat   wt qsec vs am gear carb
#> 1          Honda Civic 30.4   4  75.7  52 4.93 1.61 18.5  1  1    4    2
#> 2  Lincoln Continental 10.4   8 460.0 215 3.00 5.42 17.8  0  0    3    4
#> 3    Chrysler Imperial 14.7   8 440.0 230 3.23 5.34 17.4  0  0    3    4
#> 4             Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
#> 5            Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
#> 6           Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
#> 7           Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
#> 8             Fiat 128 32.4   4  78.7  66 4.08 2.20 19.5  1  1    4    1
#> 9          Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3
#> 10  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.25 18.0  0  0    3    4
#> 11      Toyota Corolla 33.9   4  71.1  65 4.22 1.83 19.9  1  1    4    1

Setdiff operation

setdiff function returns difference of two objects of class “tbl_teradata”.

df_setdiff <- df_first %>% setdiff(df_second)

# Print data.frame instead of tbl_teradata to display full results.
print(as.data.frame(df_setdiff))
#>           row_names  mpg cyl disp  hp drat   wt qsec vs am gear carb
#> 1    Hornet 4 Drive 21.4   6  258 110 3.08 3.21 19.4  1  0    3    1
#> 2 Hornet Sportabout 18.7   8  360 175 3.15 3.44 17.0  0  0    3    2
#> 3        Datsun 710 22.8   4  108  93 3.85 2.32 18.6  1  1    4    1
#> 4         Mazda RX4 21.0   6  160 110 3.90 2.62 16.5  0  1    4    4
#> 5     Mazda RX4 Wag 21.0   6  160 110 3.90 2.88 17.0  0  1    4    4
#> 6          Merc 230 22.8   4  141  95 3.92 3.15 22.9  1  0    4    2
#> 7         Merc 240D 24.4   4  147  62 3.69 3.19 20.0  1  0    4    2
#> 8           Valiant 18.1   6  225 105 2.76 3.46 20.2  1  0    3    1
#> 9        Duster 360 14.3   8  360 245 3.21 3.57 15.8  0  0    3    4