1 2 Previous Next 20 Replies Latest reply: Sep 9, 2012 1:39 PM by 960941 RSS

    what is left join /right join / out join/ inner join/please give example!

    607895
      what is left join /right join / out join/ inner join/please give example!
      thanks
        • 1. Re: what is left join /right join / out join/ inner join/please give example!
          BluShadow
          Maybe these examples will give you an idea...
          SQL> select * from t1;
          
                  ID
          ----------
                   1
                   2
                   3
                   4
          
          SQL> select * from t2;
          
                  ID
          ----------
                   3
                   4
                   5
                   6
          
          -- LEFT OUTER JOIN
          SQL> select t1.id, t2.id
            2  from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);
          
                  ID         ID
          ---------- ----------
                   3          3
                   4          4
                   1
                   2
          
          -- RIGHT OUTER JOIN
          SQL> select t1.id, t2.id
            2  from t1 RIGHT OUTER JOIN t2 ON (t1.id = t2.id);
          
                  ID         ID
          ---------- ----------
                   3          3
                   4          4
                              6
                              5
          
          -- LEFT JOIN (SAME AS LEFT OUTER JOIN)
          SQL> ed
          Wrote file afiedt.buf
          
            1  select t1.id, t2.id
            2* from t1 LEFT JOIN t2 ON (t1.id = t2.id)
          SQL> /
          
                  ID         ID
          ---------- ----------
                   3          3
                   4          4
                   1
                   2
          
          -- RIGHT JOIN (SAME AS RIGHT OUTER JOIN)
          SQL> ed
          Wrote file afiedt.buf
          
            1  select t1.id, t2.id
            2* from t1 RIGHT JOIN t2 ON (t1.id = t2.id)
          SQL> /
          
                  ID         ID
          ---------- ----------
                   3          3
                   4          4
                              6
                              5
          
          -- INNER JOIN (REGULAR JOIN)
          SQL> ed
          Wrote file afiedt.buf
          
            1  select t1.id, t2.id
            2* from t1 INNER JOIN t2 ON (t1.id = t2.id)
          SQL> /
          
                  ID         ID
          ---------- ----------
                   3          3
                   4          4
          
          -- FULL OUTER JOIN
          SQL> ed
          Wrote file afiedt.buf
          
            1  select t1.id, t2.id
            2* from t1 FULL OUTER JOIN t2 ON (t1.id = t2.id)
          SQL> /
          
                  ID         ID
          ---------- ----------
                   3          3
                   4          4
                   1
                   2
                              6
                              5
          
          6 rows selected.
          
          SQL>
          • 2. Re: what is left join /right join / out join/ inner join/please give example!
            Laurent Schneider
            cross join
            SQL> with 
              t1 as (select column_value id from table(sys.odcinumberlist(1,2,3,4))),  
              t2 as (select column_value id from table(sys.odcinumberlist(3,4,5,6)))  
            select * from t1 cross join t2
            
                    ID         ID
            ---------- ----------
                     1          3
                     1          4
                     1          5
                     1          6
                     2          3
                     2          4
                     2          5
                     2          6
                     3          3
                     3          4
                     3          5
                     3          6
                     4          3
                     4          4
                     4          5
                     4          6
            and FULL NATURAL JOIN
            select * from t1 full natural join t2
            
                    ID
            ----------
                     3
                     4
            LOL

            well, a natural full outer join would be less meaningless, but I do not recommend using natural join as specifying the keys is a better practice
            • 3. Re: what is left join /right join / out join/ inner join/please give example!
              BluShadow
              Very funny Laurent. ;)

              Seriously though, does anybody actually use the CROSS JOIN syntax? It's just a bloomin' cartesian product at the end of the day. I suppose it could be useful from a documentation point of view as it would be a clear indication that the cartesian product was deliberate (assuming the developer knows what they're doing).

              Agree about the natural joins, definitely bad practice, even on the best designed databases.
              • 4. Re: what is left join /right join / out join/ inner join/please give example!
                Laurent Schneider
                cross join is useful :
                with 
                  t1 as (select rownum id, column_value x from table(sys.odcinumberlist(100,200))),  
                  t2 as (select column_value y from table(sys.odcinumberlist(300,400))),
                  t3 as (select column_value id from table(sys.odcinumberlist(2,3)))
                select * from t1 cross join t2 full join t3 on (t1.id=t3.id)
                
                        ID          X          Y         ID
                ---------- ---------- ---------- ----------
                         1        100        300           
                         1        100        400           
                         2        200        300          2
                         2        200        400          2
                                                          3
                but I do not use id every day 8-)
                • 5. Re: what is left join /right join / out join/ inner join/please give exampl
                  611118
                  SQL statements revolve around TABLEs. That is, for each record in a TABLE that the query refers to, the engine evaluates there WHERE clause, and if it returns TRUE, the SELECT list is returned.

                  The FROM list is processed geometrically. That is, if there is one TABLE, it does one evaluation for each record, if there are two TABLEs, it evaluates the first record of the first TABLE for each record in the second TABLE, then it evaluates the second record in the first TABLE for each record in the second TABLE. Wash, rinse, repeat. Same for three TABLEs and so on. This operation is known as a JOIN. Specifically, this is called a Cartesian JOIN.

                  Cartesian joins are usually useless. They return the same records many times, and this is rarely what the user wants. The user usually wants there to be some association between the the TABLEs in the FROM clause. And this is accomplished by using a WHERE clause (or the ANSI standard's JOIN clause where supported) that ties the two TABLEs together. If such a WHERE clause is used, only the relevant records in both TABLEs are returned. This is called an INNER JOIN.

                  INNER JOINs restrict both TABLEs to records that satisfy the WHERE clause. Sometimes, the user wants all the data from one TABLE, but only matching records in the second TABLE. This is called an OUTER JOIN. There are always two (sets of) TABLEs in an OUTER JOIN, one is too return all records, the other, just the matching records. To identify which TABLE is which, they are called RIGHT OUTER JOINs or LEFT OUTER JOINs. The RIGHT or LEFT refers to which side of the join operator is the TABLE that returns every record. The default is LEFT.

                  A FULL OUTER JOIN returns all records from both TABLEs, matching up the ones which match. (This is not possible in the WHERE clause.)
                  • 6. Re: what is left join /right join / out join/ inner join/please give exampl
                    BluShadow
                    Just a small correction to the wording if I may...
                    Cartesian joins are usually useless. They return the
                    same records many times, and this is rarely what the
                    user wants.
                    They don't return the same records many times, they actually return all possible combinations of records from the cartesianally joined tables. The actual returned records are all different. It is rarely what the user wants because data is often stored in a database in a relational fashion and the cartesian product returns results that imply there is no specific relationship between two sets of data hence it looks as if everything is related to everything.
                    The RIGHT or LEFT refers to which side
                    of the join operator is the TABLE that returns every
                    record. The default is LEFT.
                    There is no default. LEFT or RIGHT must be specified. It is only the "OUTER" keyword that is optional. e.g.
                    SQL> select t1.id, t2.id
                      2  from t1 OUTER JOIN t2 ON (t1.id = t2.id);
                    from t1 OUTER JOIN t2 ON (t1.id = t2.id)
                                              *
                    ERROR at line 2:
                    ORA-00904: "T1"."ID": invalid identifier
                    
                    
                    SQL> select t1.id, t2.id
                      2  from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);
                    
                            ID         ID
                    ---------- ----------
                             3          3
                             4          4
                             1
                             2
                    
                    SQL> select t1.id, t2.id
                      2  from t1 LEFT JOIN t2 ON (t1.id = t2.id);
                    
                            ID         ID
                    ---------- ----------
                             3          3
                             4          4
                             1
                             2
                    
                    SQL>
                    ;)
                    • 7. Re: what is left join /right join / out join/ inner join/please give exampl
                      611118
                      First of all, thank you for the correction! :)

                      On the Cartesian join, i meant the same record of each TABLE would be repeated. Silly me leaving out the most important point. Heh.

                      As for the OUTER JOINs, i was very wrong, that's what happens when i don't check the documentation before posting!
                      • 8. Re: what is left join /right join / out join/ inner join/please give example!
                        460178
                        Hi,

                        Ofcourse we use CROSS JOINS. Its being used when we need populate table data for a period which u may not have data for it. A Simple example.

                        Lets say we get sales data for various region for various months and the report needs for next 12 month data for every region from now. its easy to go ahead with a cross join with Date and Sales Data tables. Oracle Never gives out an option without reason

                        This may be a late reply to you
                        • 9. Re: what is left join /right join / out join/ inner join/please give example!
                          3520
                          As someone resurrected this old thread I'd like to point out to [url http://www.gplivna.eu/papers/sql_join_types.htm]my article SQL join types, which contains classified join types along with quite many examples, attempts to visualise them ;) as well as some additional info.
                          I doubt it will help OP, but probably someone will find new info there.

                          Gints Plivna
                          http://www.gplivna.eu
                          • 10. Re: what is left join /right join / out join/ inner join/please give example!
                            BluShadow
                            As someone resurrected this old thread I'd like to
                            point out to [url
                            http://www.gplivna.eu/papers/sql_join_types.htm]my
                            article SQL join types, which contains
                            classified join types along with quite many examples,
                            attempts to visualise them ;) as well as some
                            additional info.
                            Jeez man, you must have been bored to write all of that, or were you being paid to rewrite the oracle manuals?

                            ;)
                            • 11. Re: what is left join /right join / out join/ inner join/please give exampl
                              Alessandro Rossi
                              Agree about the natural joins, definitely bad
                              practice, even on the best designed databases.
                              Probably you're ignoring cases like this where natural join is very helpful.
                              select owner,table_name,column_name,r_owner,r_table_name,r_column_name
                              from (
                                        select owner,constraint_name,r_owner,r_constraint_name
                                        from dba_constraints
                                        where constraint_type = 'R'
                                   ) natural join (
                                        select owner,constraint_name,table_name,column_name, position
                                        from dba_cons_columns
                                   ) natural join (
                                        select owner as r_owner,constraint_name as r_constraint_name,
                                             table_name as r_table_name,column_name as r_column_name ,position
                                        from dba_cons_columns
                                   )
                              Bye Alessandro
                              • 12. Re: what is left join /right join / out join/ inner join/please give example!
                                3520
                                Jeez man, you must have been bored to write all of
                                that, or were you being paid to rewrite the oracle
                                manuals?

                                ;)
                                Not a single cent :P

                                At first I wanted to draw a meta model of joins. Because usually people speak about these all natural, inner, outer, whatever else joins without understanding how they relate to each other. I.e. in many articles you can find that natural joins are only inner joins, or self joins are only inner joins or whatever else wrong assumptions. So my idea was to clear up all this mess and at first understand all this stuff for myself. Then came the idea to put it somewhere on web. For me it was OK but for other people who probably didn't know all the details, examples would be helpful. Also I tried all of them on MySQL and SQL Server to understand what the difference is. So it emerged as it is now :)
                                And BTW show me where in Oracle manuals there are joins classified and such a nice picture? ;) Where are exactly explained the difference between join conditions and where conditions for outer joins? Where are explained what work and what not on SQL Server and MySQL? :)

                                Gints Plivna
                                http://www.gplivna.eu
                                • 13. Re: what is left join /right join / out join/ inner join/please give example!
                                  3520
                                  Oh yea and speaking about the join visualisation - I was a bit shocked how joins where visually "explained" [url http://www.codinghorror.com/blog/archives/000976.html]using venn diagramms here. I was thought that Venn diagramms could symbolize sets and set operations. And not in any kind joins. So I absolutely don't like this explanation. On the other hand I thought how I can offer something I like more :) And so the result is in my article...

                                  Gints Plivna
                                  http://www.gplivna.eu
                                  • 14. Re: what is left join /right join / out join/ inner join/please give example!
                                    BluShadow
                                    Oh yea and speaking about the join visualisation - I
                                    was a bit shocked how joins where visually
                                    "explained" using venn diagramms here. I was thought that
                                    Venn diagramms could symbolize sets and set
                                    operations. And not in any kind joins. So I
                                    absolutely don't like this explanation.
                                    Well data on a database is sets and joins are the joining of sets of data, so joins on that data can be represented with venn diagrams. Looked ok to me.

                                    I thought Oracle's documentation on joins was ok myself although it doesn't have a diagram to show a conceptual model of them. It does give examples and explanations which are sufficient for anybody who at least understands the basics of database concepts.

                                    Each to their own I guess.
                                    1 2 Previous Next