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 2Union 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 2Union_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 2Intersect 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 1Setdiff 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