relational joins in S-Plus

relational joins in S-Plus In the following examples, click on "inner.s" and "outer.s" and "leftouter.s" to see the S-Plus code.

To run the examples:

  1. Use your Web browser to save the files "a.s", "b.s", "c.s", "inner.s", "outer.s", and "leftouter.s" on a computer where S-Plus is installed.
  2. Go "back" to the previous window with your Web browser.
  3. Open up a separate window on your computer and move to the directory where you saved the files.
  4. In that window, "mouse in" the commands "splus" through "q()" which appear in your Web browswer window. The commands will be echoed and you will see the results.
  5. Edit the files "a.s", "b.s" and "d.s" as desired, and repeat.

inner join

An inner join produces a new table (matrix or relation) with combined rows (records or tuples) from (only) those rows of tables whose keys match.

Suppose table a has columns a1,a2,a3,...; and table b has columns b1,b2,b3,...; a1 and b1 are keys which specify unique rows in a and b respectively.

An inner join with a1=b1 gives a combined table with columns (a1=b1),a2,a3,...,b2,b3,... from matching rows occurring in both a and b. Partially empty rows, from rows occurring in only one of the input tables, are excluded.

Example: 

splus
options(echo=T)
source ("a.s")
source ("b.s")
source ("inner.s")
a
b
inner.join(a,b)
q()

outer join

An outer join produces a new table (matrix or relation) with combined rows (records or tuples) from rows of tables whose keys match. In addition, for rows whose keys don't match, it produces combined rows with null values for the columns (variables or attributes) of the missing rows.

Suppose table a has columns a1,a2,a3,...; and table b has columns b1,b2,b3,...; a1 and b1 are keys which specify unique rows in a and b respectively.

An outer join with a1=b1 gives a combined table with columns (a1=b1),a2,a3,...,b2,b3,... from rows occurring in either a and b. Partially empty rows, from rows occurring in only one of the input tables, are included.

Example: 

splus
options(echo=T)
source ("a.s")
source ("b.s")
source ("outer.s")
a
b
outer.join(a,b)
q()

left outer join

A left outer join on a common variable produces a new table (matrix or relation) with combined rows (records or tuples) from the rows of two tables. Each combined row includes the data from the first table, plus data from the row in the second table whose key matches a selected column in the first table.

Suppose table a has columns a1,a2,a3,...; table b has columns b1,b2,b3,...; a1 is any column in a; b1 is a key which specifies a unique rows in a and b.

A left outer join with a1=b1 gives a combined table with columns (a1=b1),a2,a3,...,b2,b3,... The combined rows correspond to the rows of a. In each combined row, values of b2,b3,... are taken from the row in b whose key value b1 matches the value of column a1. Rows in b which do not match rows in a are ignored.

Example: 

splus
options(echo=T)
source ("d.s")
source ("b.s")
source ("leftouter.s")
d
b
leftouter(d,b,2)
q()
back to hints for using S-Plus
http://merrill.wwh.net/mdocs/splus/joins.html 1/17/96
dwmerrill@lbl.gov