0

I'm trying to understand how the natural full outer join works, so lets consider the tables R and S.

Table R:

  a  |   b  |   c  |  e
  1  | null |   1  |  2
  1  |   3  | null |  2
  2  |   4  |   2  |  2
null |   5  |   2  |  2
null |   4  |   1  |  2
null |   2  | null |  2
  0  |   2  |   4  | null

And a table S:

  c  |  d
  1  |  2
  2  |  2 
  3  |  2 
null |  2 
  4  | null

The result that I got from doing

select *
from R natural full outer join S

Was:

  a  |   b  |   c  |   d  |   e
  1  | null |   1  |   2  |   2
  1  |   3  | null |   2  |   2
  2  |   4  |   2  |   2  |   2
null |   5  |   2  |   2  |   2
null |   4  |   1  |   2  |   2
null |   2  | null |   2  |   2
  0  |   2  |   4  | null | null
null | null |   3  |   2  | null

However this is not correct because the sum of all the values from the following query has to be 20:

SELECT b, COUNT(*), AVG(a+d) 
FROM R NATURAL FULL OUTER JOIN S 
WHERE NOT d<>e 
GROUP BY b

Please note that this was all done by hand.

Aterin
  • 557
  • 1
  • 6
  • 13
  • No reason to do this by hand. Head over to http://sqlfiddle.com/ – Eric Hauenstein Apr 10 '14 at 17:58
  • Thank you for your help, that website will indeed be quite useful to me in the future! However, in this case it isn't much help because the NATURAL FULL OUTER JOIN isn't supported by MySQL, hence my need to do it by hand. – Aterin Apr 10 '14 at 18:08
  • You are trying to understand how a join works using a language that doesn't support that join? I feel like I'm being pranked. – Eric Hauenstein Apr 10 '14 at 18:38
  • It is supported by SQL, just not MySQL software. – Aterin Apr 10 '14 at 18:46
  • 1
    According to the docs, it is supported: http://dev.mysql.com/doc/refman/5.0/en/join.html It is also supported in Oracle and PostgreSQL, so you might try changing the sqlfiddle target language to one of those. – Eric Hauenstein Apr 10 '14 at 18:55
  • Eric: Thanks, I didn't know MySQL supports "Natural". @Aterin:"NATURAL" is a join where the DBMS looks at the field names on both sides and matches up the columns without being told (based on names). So, you can simulate it in any DBMS by specifying the ON clause yourself, even though it is not in the original question. – Darius X. Apr 10 '14 at 19:12
  • Eric: I see, seems I was mistaken about it not being supported, my apologies. Still I can't get it to work on sqlfiddle, here's the link with the schema already build: http://sqlfiddle.com/#!2/f6fec/3 @DariusX. That's what I did on the query I placed on the original question (I think I did it right). I matched the c from R with the c from S, however the resulting table is incorrect (hence my need for help). – Aterin Apr 10 '14 at 20:50
  • This fiddle has the query without the group by. http://sqlfiddle.com/#!2/f6fec/6 When you group, where are you expecting 20 ? – Darius X. Apr 10 '14 at 21:35
  • @EricHauenstein 20 is the expected result of adding all the values in: 'SELECT b, COUNT(*), AVG(a+d) FROM R NATURAL FULL OUTER JOIN S WHERE NOT d<>e GROUP BY b' – Aterin Apr 11 '14 at 10:44
  • Why do you have WHERE NOT d<>e instead of WHERE d=e? – Eric Hauenstein Apr 11 '14 at 12:36
  • @EricHauenstein Yes, that query was in a midterm, the teacher most likely put it there to confuse the students. – Aterin Apr 11 '14 at 13:42

0 Answers0