1 2 Previous Next 16 Replies Latest reply: Jun 24, 2011 11:59 AM by jgarry RSS

    what is LEFT JOIN ?

    user575089
      I have gone through LEFT JOIN . Here is LEFT JOIN . Check the link

      http://i218.photobucket.com/albums/cc298/curseofgoldendragon/test-3.png

      I have a doubt here. I see we are picking all the records from Table A ...so, I dont think we need to join here.

      We could just use a "select" query to fetch all the records from Table A...is not it ?

      Hence, I dont find usefulness of "LEFT JOIN".

      Can anybody explain if I'm missing something ?
        • 1. Re: what is LEFT JOIN ?
          Fran
          with "LEFT JOIN" you select all the fields of "A" (matched or not matched) and only fileds matched in the clause where of "B"
          • 2. Re: what is LEFT JOIN ?
            Helios-GunesEROL
            Hi;

            The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

            Referance and more details:
            http://www.w3schools.com/sql/sql_join_left.asp

            Regard
            Helios
            • 3. Re: what is LEFT JOIN ?
              AlexAnd
              i like OUTER JOIN when Multi-Column Join and Join to a Constant (see table in http://www.orafaq.com/node/855)

              example
              select * from T1, T2 where T1.C1 = T2.C1(+) and T1.C2(+) = ‘Y’;
              but below code is more beautiful :)
              select *
              from T1 left outer join T2 on (T1.C1 = T2.C1 and T1.C2 = ‘Y’)
              also U may use something
              select *
              from T1 left outer join T2 on (T1.C1 = T2.C1 and substr(T1.C2,10,20) = ‘Y’)
              but in plus notation it's more complex

              Edited by: AlexAnd on Jun 24, 2011 1:08 AM
              • 4. Re: what is LEFT JOIN ?
                Fran
                Alex:

                select * from T1, T2 where T1.C1 = T2.C1(+) and T1.C2(+) = ‘Y’; <-- Obsolete

                select * from T1 left outer join T2 on (T1.C1 = T2.C1 and T1.C2 = ‘Y’) <-- Best choice.
                • 5. Re: what is LEFT JOIN ?
                  user575089
                  You are not getting my question.

                  I am asking we can use a select query to pick all records from Table A. No need to use a "LEFT JOIN"

                  In other words, "LEFT JOIN" can be replaced by a "SELECT" query.
                  • 6. Re: what is LEFT JOIN ?
                    Fran
                    no, it can't.

                    if you try a "SELECT" you only can join two tables with fiels matched.
                    If you use "LEFT JOIN" you can join two tables, full left table and fiels matched on right table.

                    why don't you try to do that and see the diference?
                    • 7. Re: what is LEFT JOIN ?
                      789929
                      You would be using only SELECT instead of LEFT JOIN if you need only columns from table A.

                      If you need columns from table A and B joined together you would used SELECT with join clause and only intersection of A and B would be returned to you

                      If you need columns from table A and B joined together + all other records from table A you must use left join...
                      • 8. Re: what is LEFT JOIN ?
                        user575089
                        No. you did not get my point yet. see below..
                        Fran wrote:
                        no, it can't.

                        if you try a "SELECT" you only can join two tables with fiels matched.
                        I dont want to join using a select ! . I'll just pick all records from Table A . That's the point I'm making

                        "select * from Table A where blah blah" ------>This will give FULL Table A records. I dont need a JOIN for this.

                        If you use "LEFT JOIN" you can join two tables, full left table and fiels matched on right table.
                        I agree. But are not they giving the same result ? both are giving FULL Table A records.
                        • 9. Re: what is LEFT JOIN ?
                          Fran
                          yeah, If you want only results only from table A you dont need any JOIN. JOIN is successfull if you need results from two or more tables

                          But your Post Message is: "what is LEFT JOIN"', why are you using JOIN if you only work with 1 table?
                          • 10. Re: what is LEFT JOIN ?
                            user575089
                            Fran wrote:
                            yeah, If you want only results only from table A you dont need any JOIN.
                            No.

                            If I want only results only from table A then I could use LEFT JOIN also. because LEFT JOIN will return all records from table A...so no issue. ( matching records in Table B will also be in Table A) So, LEFT JOIN will always return records from Table A.


                            The main point what I'm trying to make is , a *"Select" and "LEFT JOIN" returns same.*
                            • 11. Re: what is LEFT JOIN ?
                              Fran
                              Let's try again....

                              If you use JOIN --> full Table A (matched and not matched), and only fields matched table B

                              If you use SELECT --> You can use col1.tableA=col2.tableB --> shows only fields matched in both tables, if you want a full table A you cant use any clause "where".
                              But how you do a full table A and fields matched in table B with a select? Please try to do the same with join and with select. You will understand it by yourself.
                              • 12. Re: what is LEFT JOIN ?
                                sybrand_b
                                No, incorrect. They don't necessarily. If the second table has matching records, they will be shown.

                                ------------
                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: what is LEFT JOIN ?
                                  sybrand_b
                                  Databases don't have fields. Punched cards have.

                                  -----------
                                  Sybrand Bakker
                                  Senior Oracle DBA
                                  • 14. Re: what is LEFT JOIN ?
                                    Fran
                                    sorry for write "fields", its the translate from spanish. records better?
                                    1 2 Previous Next