sqlpostgresqljoinnatural-join

Natural join on two tables with different number of columns that working properly

I'm trying to understand why natural join is not working properly in sql on my two tables:

bookno |        title
--------+----------------------
2001 | Databases
2012 | Geometry
2010 | Philosophy
2008 | DataScience
2011 | Anthropology
2003 | Networks
2013 | RealAnalysis
2006 | SQL
2002 | OperatingSystems
2007 | ProgrammingLanguages
2009 | Calculus
2005 | DiscreteMathematics
2014 | Topology
2004 | AI
(14 rows)

and

price | bookno |  title   | b2price | sid  |   sname
-------+--------+----------+---------+------+-----------
70 |   2014 | Topology |      70 | 1022 | Joeri
80 |   2012 | Geometry |      80 | 1008 | Emma
80 |   2012 | Geometry |      80 | 1010 | Linda
80 |   2012 | Geometry |      80 | 1020 | Ahmed
80 |   2012 | Geometry |      80 | 1004 | Chin
80 |   2012 | Geometry |      80 | 1023 | Chris
80 |   2012 | Geometry |      80 | 1007 | Catherine
80 |   2012 | Geometry |      80 | 1017 | Ellen
70 |   2014 | Topology |      70 | 1023 | Chris
80 |   2012 | Geometry |      80 | 1012 | Eric
80 |   2012 | Geometry |      80 | 1006 | Ryan
80 |   2012 | Geometry |      80 | 1002 | Maria
80 |   2012 | Geometry |      80 | 1014 | Filip
80 |   2012 | Geometry |      80 | 1005 | John
80 |   2012 | Geometry |      80 | 1009 | Jan
80 |   2012 | Geometry |      80 | 1013 | Lisa
80 |   2012 | Geometry |      80 | 1011 | Nick
80 |   2012 | Geometry |      80 | 1003 | Anna
(18 rows)

The second table is table made using a couple of cross-joins and different tables. Shouldn't the result of this natural join be:

bookno |        title
--------+----------------------
2012 | Geometry
2014 | Topology

But instead, all it's outputting is the first table over again. Why is this the case? The exact code is: 

select distinct b.bookno, b.title
from book b, student s
natural join (select distinct b1.price, b2.bookno, b2.title, b2.price, s1.sid, s1.sname
from buys t cross join book b1 cross join book b2 cross join student s1
where b1.price > 50 and s1.sid = t.sid and 
t.bookno = b1.bookno and b2.price = b1.price) q0;
Solution

  • This is (horrible) old syntax produces a cross join between book and student:

    select distinct b.bookno, b.title
from book b, student s

    rewritten for explicit join syntax it is:

    select distinct b.bookno, b.title
from book b CROSS JOIN student s

    But if that is literally how the query is structured all you would get in return would be the bookno and title, which is effectively the same as:

    select b.bookno, b.title
from book b

    and the rest of you query is just noise.

    nb: If your result repeats bookno/title then you haven't copied the exact query into the questions