8 Replies Latest reply: May 2, 2013 12:31 PM by Frank Kulash RSS

    Join Confusions

    sufiyan
      Hi all,

      I am kinda new to oracle and sql (as some of you can tell). However i do know how to join tables but i always get confused and think to myself "did i do it right?". i guess thats because i am not confident enough and i guess thats because my concept is not clear. I just want to know the Dos and Donts when joining tables. I just have one scenario, suppose i have these below tables.


      Table1
      Table2
      Table3
      Table4
      Table5
      Table6

      Does this look about right?

      Table1=Table2
      Table1=Table3
      Table1=Table4
      Table1=Table5
      Table6=Table2


      Does this joining of tables look right to you guys?

      Ofcourse when i am joining i am joining on matching keys.
      Thanks
        • 1. Re: Join Confusions
          jeneesh
          There is nothing technicay wrong in your join..

          For n tables there should be atleast n-1 join conditions. And each table should be part in atleast one join condition.And you have that..

          Logically, is it correct? To tell that we need to see the data and need to understand the relationships..
          • 2. Re: Join Confusions
            AlbertoFaenza
            Hi,

            the correct answer is: maybe!

            As we don't know the data model we cannot say that your join conditions are correct.
            We can say however that all table have a join, at least.
            You can also use ANSI syntax when joining table.

            i.e.:
            SELECT  {* or list_of_columns_here}
              FROM Table1 t1
                   JOIN Table2 t2 ON (t1.colx = t2.colx)
                   JOIN Table3 t3 ON (t1.colx = t3.colx)
                   JOIN Table4 t4 ON (t1.colx = t4.colx)
                   JOIN Table4 t5 ON (t1.colx = t5.colx)
                   JOIN Table6 t6 ON (t2.coly = t6.coly);
            Maybe this can help you to understand the joining conditions better.

            Regards.
            Al

            Edited by: Alberto Faenza on May 2, 2013 5:00 PM
            • 3. Re: Join Confusions
              Frank Kulash
              Hi,
              Z KHAN wrote:
              Hi all,

              I am kinda new to oracle and sql (as some of you can tell). However i do know how to join tables but i always get confused and think to myself "did i do it right?". i guess thats because i am not confident enough and i guess thats because my concept is not clear. I just want to know the Dos and Donts when joining tables. I just have one scenario, suppose i have these below tables.


              Table1
              Table2
              Table3
              Table4
              Table5
              Table6

              Does this look about right?
              That looks like 5 different fragments of code, all out of context. It would be a lot more helpful if you posted a complete query.
              You could probably get your point across using only 3 or 4 tables.
              Table1=Table2
              Table1=Table3
              Table1=Table4
              Table1=Table5
              Table6=Table2
              You can't compare one table to another, you can only compare individual columns in those tables.
              The join conditions above might be correct if (for example) there is a column in table1 that is also called table1, and no other table in the query also has a column called table1.
              Does this joining of tables look right to you guys?
              Basically, it looks okay.

              There is not necessarily one right way to join any given tables. There can be many different ways, each of which is right for getting different results.
              In the vast majority of cases, each table will be joined to at least one other table, and there is a continous path from every table in the qauery to every other table. For example, there will be some connection, direct or indirefct, between Table1 and Table6 above. Table1 does not seem to be joined directly to Table6, but Table1 does appear to be joined to Table2, and Table2 seems to be joined to Table6, so that looks normal.
              Ofcourse when i am joining i am joining on matching keys.
              Of course. Matching doesn't always mean equal, as in this example:
              SELECT     e.ename
              ,     e.sal
              ,     s.*
              FROM     scott.emp       e
              JOIN     scott.salgrade     s  ON  e.sal  BETWEEN  s.losal
                                               AND      s.hisal
              ;
              • 4. Re: Join Confusions
                sufiyan
                Thanks folks, when you guys say "we need to see you data model" or "We dont know your daata". What would you see in the data model if provided? For example, when i am joining what practices should be kept in mind or a trend to follow?
                • 5. Re: Join Confusions
                  AlbertoFaenza
                  Z KHAN wrote:
                  Thanks folks, when you guys say "we need to see you data model" or "We dont know your daata". What would you see in the data model if provided? For example, when i am joining what practices should be kept in mind or a trend to follow?
                  Hi,

                  My definition my not be completely accurate and I'm sure someone else will have something to add but basically with data model we mean the entities (i.e. tables) and their relationship (i.e. foreign keys).

                  Regards.
                  Al
                  • 6. Re: Join Confusions
                    Frank Kulash
                    Hi,
                    Z KHAN wrote:
                    Thanks folks, when you guys say "we need to see you data model" or "We dont know your daata". What would you see in the data model if provided? For example, when i am joining what practices should be kept in mind or a trend to follow?
                    Here's one practice I recommend: Only use ANSI join syntax for all joins, using the keywords JOIN and ON, like this:
                    ...
                    FROM    scott.emp   e
                    JOIN    scott.dept  d  ON  d.deptno  = e.deptno
                    This will cause many common mistakes to be syntax errors, where you get a (potentially) helpful error message, rather than merely getting wrong results, where not only do you not have any clue where the mistake is, you don't even know that there is a mistake.
                    • 7. Re: Join Confusions
                      sufiyan
                      Thanks Frank. So when i use ANSI join systax like join table on column etc. This method does not give incorrect results? Rather, it provides a helpful error message? Is that correct?
                      • 8. Re: Join Confusions
                        Frank Kulash
                        Hi,
                        Z KHAN wrote:
                        Thanks Frank. So when i use ANSI join systax like join table on column etc. This method does not give incorrect results? Rather, it provides a helpful error message? Is that correct?
                        You can always get incorrect results. Nobody will ever invent a notation that guarantees you won't make some kind of mistake.

                        There are some very common mistakes that cause error messages when using ANSI syntax, but don't raise any error using the old notation, and therefore may go unnoticed.

                        For example, you can forget to specify a join condition. You can make this mistake using any notation.
                        In ANSI notation, if you forget to say how tables are joined:
                        SELECT     COUNT (*)     AS cnt
                        FROM     scott.emp   e
                        JOIN     scott.dept  d     -- *** MISTAKE *** should say:  ON d.deptno = e.deptno
                        ;
                        then you get an error message, that tells (fairly accurately) what the mistake was, and where it occurred:
                        *
                        ERROR at line 4:
                        ORA-00905: missing keyword
                        If you make a similar mistake with the old notation:
                        SELECT  COUNT (*)     AS cnt
                        FROM     scott.emp
                        ,     scott.dept
                        -- *** MISTAKE *** should say:  WHERE d.deptno = e.deptno
                        ;
                        then you get results:
                        `      CNT
                        ----------
                                56
                        These are actually the right results for some question, but probably not the question you meant to ask. You have to be paying close attention, and you have to know something about the tables involved, to even realize that there is a mistake. If you do realize there is some mistake, you still have no hint what it is, or where it occurred.