0

Assume the following relations schemas:

  • S(A,B)
  • T(B,C)

Rewrite the following expressions without using join or outer join operators explicitly:

  1. S LEFT OUTER JOIN T
  2. S FULL OUTER JOIN T

How do I do these two questions? I've tried using a where clause but I don't think it is correct. I know the first one has to use a union but how do I go about writing the queries?

1 Answer 1

-1

Left outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s

Full outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s
union all
select null, b, c from t
where b not in (select b from s)
6
  • Unless b is a unique key in T, these will not work. Apr 7, 2014 at 8:28
  • Null value in b is problematic, but why should b be be unique. Maybe I am missing something. Please see my test sqlfiddle.com/#!6/1f186/1 Apr 7, 2014 at 13:21
  • 5
    What does null have to do with uniqueness? See the problem with your queries: SQL-fiddle-2 Apr 7, 2014 at 13:30
  • What I meant is my union query does not include rows like T(null, 4). But it does work with rows T(5, 4), T(5, 8), which are non-unique b values in T. Apr 7, 2014 at 13:35
  • 4
    Your query does not work. Check the fiddle in the above comment. Apr 7, 2014 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.