Natural Joins....



  • Need I say more?

     

    After writing a dozen or so views using 'natural joins' in MSSQL, we had a support tech (converted from tech writer) ask us why his views were returning millions of rows instead of the 20 he expected.  His last employer used Oracle and he learned natural joins here.  Now this guy isn't a techie at all so I understand that he would not know this.  The WTF is that there is such a thing to begin with. 



  • @NCBloodhound said:

    Need I say more?
     

    Well, yeah I think you do. I read this twice and I still don't understand what's noteworthy about this. So, somebody wrote a natural join and it wasn't the join he wanted? Or he was unfamiliar with a where predicate? I don't get it.



  • @Nether said:

    I don't get it.
     

    Seconded.



  •  The guy that wrote the views didn't do anything wrong.  He didn't know any better.  The WTF is that natural joins event exists.  If you understand natural joins then you understand the WTF. 



  • I understand them. I've used them. They are a shorthand form of a query with an implicit predicate that depends on the names of the fields available in the tables it operates on.

    Why do you think they are a WTF? Because one inexperienced guy used them when really he meant to use another kind of join? I still don't get it.



  • @Nether said:

    I understand them. I've used them. They are a shorthand form of a query with an implicit predicate that depends on the names of the fields available in the tables it operates on.

    Why do you think they are a WTF? Because one inexperienced guy used them when really he meant to use another kind of join? I still don't get it.

     

    IMO natural joins are evil. Just like insert statements without a column list ("insert into sometable values (1,2,3)") or select * statements with a list of host variables ("select * into foo, bar from foobar"). All those constructs make it difficult to add columns to tables, something that is bound to happen sooner or later, maybe even several times, in the lifespan of an application.



  • @ammoQ said:

    IMO natural joins are evil. Just like insert statements without a column list ("insert into sometable values (1,2,3)") or select * statements with a list of host variables ("select * into foo, bar from foobar"). All those constructs make it difficult to add columns to tables, something that is bound to happen sooner or later, maybe even several times, in the lifespan of an application.

    Inflexible, certainly. There are plenty of ways to write inflexible queries and code of any sort that would make data binding a headache when your schema has to change. I don't think this makes such constructs inherently "evil". A natural join is a shorthand for a very common, simple type of query, which is just fine if your application is simple.



  • @Nether said:

    which is just fine if your application is simple.
     

    Or if you are just throwing together a quick statement.... Certainly this kind of stuff should be avoided when writing sprocs for huge enterprise systems.



  • @MasterPlanSoftware said:

    Or if you are just throwing together a quick statement.... Certainly this kind of stuff should be avoided when writing sprocs for huge enterprise systems.

     

    Natural joins are obviously ok for ad-hoc queries. 



  • @ammoQ said:

    @MasterPlanSoftware said:

    Or if you are just throwing together a quick statement.... Certainly this kind of stuff should be avoided when writing sprocs for huge enterprise systems.

     

    Natural joins are obviously ok for ad-hoc queries. 

     

    Can you argue with me anyway? I am not used to agreeable people around here...



  • @NCBloodhound said:

     The guy that wrote the views didn't do anything wrong.  He didn't know any better.  The WTF is that natural joins event exists.  If you understand natural joins then you understand the WTF. 

     

    I don't understand why their existance is a WTF in a rDBMS.  They are a fairly significant part of relational algebra (which is the underlying principle in relational databases).

     Have I ever actually used one in a real query?  No, but I certainly understand why they would be implemented to begin with.



  • Um, I don't think they are fundamental to relational algebra at all, any more than the specific operation of adding 5 and 7 is fundamental to mathematics. A cartesian join is fundamental. A natural join is just a shorthand for a simple, common, useful predicate.



    Oh and MasterPlanSoftware, you are

    WRONG WRONG WRONG

    That should do it.



  • @Nether said:

    Oh and MasterPlanSoftware, you are

    WRONG WRONG WRONG

     

    Yes you are right and the rest of the world is wrong.



  • @Nether said:

    Um, I don't think they are fundamental to relational algebra at all, any more than the specific operation of adding 5 and 7 is fundamental to mathematics. A cartesian join is fundamental. A natural join is just a shorthand for a simple, common, useful predicate.

    WTF, are you joking?  Natural joins are as fundamental to relational algebra as addition is to arithmetic.  However, modern RDBMS give more powerful facilities like the ability to enumerate fields and not rely on implied relations which makes for better software engineering.


  • ♿ (Parody)

    @morbiuswilters said:

    Natural joins are as fundamental to relational algebra as addition is to arithmetic.
    A better analogy might be division.  If you're not careful, it can blow up in your face.


Log in to reply