How left joins and right joins work in relational algebra

Question

If I have two tables

CREATE TABLE R
(
a int,
b int
);

CREATE TABLE S
(
b int,
c int,
d int
);

INSERT INTO R
VALUES (3,1),(2,3),(2,0),(3,3),(1,1);

INSERT INTO S
VALUES (2,2,3),(3,0,0),(1,3,3),(2,2,0);

Then I do R outer join S. I'm trying to figure out the result. If I try to use sql code, I would do:

  SELECT r.a,r.b,s.c,s.d
  FROM R r
  LEFT JOIN S s ON r.b=s.b

  UNION

  SELECT r.a,r.b,s.c,s.d
  FROM R r
  RIGHT JOIN S s ON r.b=s.b

The result of that query is:

A   B   C   D
3   1   3   3
2   3   0   0
2   0   -   -
3   3   0   0
1   1   3   3
-   -   2   3
-   -   2   0

If I try to solve it by hand:

The 3 1 matches with 1 3 3

The 2 3 matches with 3 0 0

The 2 0 matches with - - -

The 3 3 matches with 3 0 0

The 1 1 matches with 1 3 3

This makes the first 5 rows above in that result. The two remaining rows come from the right join. But what I don't understand is why the right join produces

-   -   2   3
-   -   2   0

Because on the left join the 2 0 didn't match with anything, yet the b column was preserved and the 0 was there.

But on the right join, the 2 2 3 and 2 2 0 didn't match with anything but the b column turned into a null. Shouldn't the b column be preserved here like in the left join?

Can anyone explain whats going on here?

Thanks.

score 1 · Answer 1 · answered Feb 17 '14 at 20:28

To answer Omega's questions about outer join:

Strictly speaking in the RA, there is no such thing as outer join. Because you would have to put nulls in the result for the 'missing' attributes.

But if you insist, go to http://en.wikipedia.org/wiki/Relational_algebra on 'Full outer join'. There's a picture showing nulls variously under all the mis-matched attributes.

Now you have to involve all the machinery of possibly-null values, and what to do with selections that mention them. You just don't need the headache.

score 0 · Answer 2 · answered Jan 27 '14 at 07:12

0

Its because your asking for r.b which doesn't exist. Use s.b instead. Example

SELECT r.a,r.b,s.c,s.d
  FROM R r
  LEFT JOIN S s ON r.b=s.b

  UNION

  SELECT r.a,s.b,s.c,s.d
  FROM R r
  RIGHT JOIN S s ON r.b=s.b

answered Jan 27 '14 at 07:12

exussum

18,275
8
32
65

But in relational algebra (not in sql), does it select `r.b` or `s.b`? – omega Jan 27 '14 at 07:14

score 0 · Answer 3 · edited May 23 '17 at 12:28

0

The reason that in your RiGHT JOIN query you you got the

-   -   2   3
-   -   2   0

records is because when you specifically asked for it.
When you perform a right join you are asking for all records from the argument table on the right (S in your case), with the matching rows from the left argument table (R in your case). If no such match exists, the left records will be replaced by nulls.

Take a look at this SO thread.

edited May 23 '17 at 12:28

Community

1
1

answered Jan 27 '14 at 07:14

Avi Turner

10,234
7
48
75

But in relational algebra, if you just did `R outer join S` how would the result look like? (how do you pick if its r.b or s.b)? – omega Jan 27 '14 at 07:18
@Omega The result would be the union of the `LEFT` and `RIGHT` `JOINS`. pretty much like your query. those rows are the `RIGHT JOIN` part within the union. – Avi Turner Jan 27 '14 at 07:26
But in a right join, I could of selected s.b or r.b, I happened to pick r.b which gave a null, if I put s.b it would not of been null. Both of these are valid to do in sql, but I don't understand which gets used in just relational algebra... – omega Jan 27 '14 at 07:43
When you use an `OUTER JOIN` it is symmetrical, both will be chosen. The `LEFT` **and** the `RIGHT` join will be performed (hence the `UNION`). – Avi Turner Jan 27 '14 at 07:49

How left joins and right joins work in relational algebra

3 Answers3