10 Replies Latest reply: Apr 2, 2013 8:56 AM by BluShadow RSS

    clauses

    948062
      Hi All,



      Is it Possible to Join two tables without where condition, can it possible to join two tables by using group by and having clause.
      for ex: i want to join emp and Dept tables




      Thanks
        • 1. Re: clauses
          Ashu_Neo
          Yes, it is possible to join 2 tables without where conditions. And it will be a cross-join.
          • 2. Re: clauses
            948062
            i want to use by using having and group by clause to join emp and dept tables can you please send me the query.

            Thanks
            • 3. Re: clauses
              Ramin Hashimzadeh
              please first search in google keyword "SQL - JOIN" then if not find any answer then explain your problem what you want to do.
              • 4. Re: clauses
                Paul  Horth
                945059 wrote:
                Hi All,



                Is it Possible to Join two tables without where condition,
                Yes,
                select *
                from dept,emp;
                will give what's called a Cartesian join: every row in one table is matched with every row in the other.

                Also
                select *
                from dept d
                inner join emp e
                on e.dept_id = d.dept_id
                doesn't have a where clause but does a join on the dept_id using the ANSI join syntax.
                can it possible to join two tables by using group by and having clause.
                No, that's not what group by and having clauses are for.
                for ex: i want to join emp and Dept tables
                Not much of an example: why not just join them using a where
                or ANSI join?
                • 5. Re: clauses
                  BEDE
                  Classical Oracle syntax is to join in the where clause, or outer join using (+).
                  select d.dept_name, e.emp_name
                  from dept d, emp e
                  where d.dept_id=e.dept_id (+)
                  Otherwise, you may use "join on" or "left join on", in which case it is possible not to have any whare clause.

                  In order to get better directions you should explain exactly what you want to do.
                  select d.dept_name, e.emp_name
                  from dept d 
                  left join emp e on d.dept_id=e.emp_id 
                  • 6. Re: clauses
                    sybrand_b
                    Not possible. GROUP BY and HAVING are not there to join tables.

                    -------------
                    Sybrand Bakker
                    Senior Oracle DBA
                    • 7. Re: clauses
                      SomeoneElse
                      i want to use by using having and group by clause to join emp and dept tables
                      Why do you want to do it this way instead of a proper join?
                      • 8. Re: clauses
                        Martin Preiss
                        in Oracle (and some other databases) you can use a NATURAL JOIN to join by columns with the same name in both tables: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55325

                        - but I don't think that's a good idea, because a change of the table structure will result in strange problems.
                        • 9. Re: clauses
                          Frank Kulash
                          Hi,
                          945059 wrote:
                          Hi All,



                          Is it Possible to Join two tables without where condition,
                          ANSI join syntax doesn't need a WHERE clause. Join conditions are included in the FROM clause.
                          can it possible to join two tables by using group by and having clause.
                          Joins are done in the FROM clause or the WHERE clause, not in any other clauses. However, you can sometimes get the effect of joining on something from another clause if (for example) your actual join conditions produce more rows than you need, but the HAVING clause eliminates the unwanted rows.
                          It would really help if you posted an example of what you were trying to do using tables in the scott schema.

                          Joins are done before aggregate functions are computed, so if the join condition really depends on something that would be appropriate to use in a HAVING clause, then you have to do the GROUP BY in a sub-query, then you can join that result set to another table using the
                          for ex: i want to join emp and Dept tables
                          Show the results you want, and explain how you get those results.
                          See the forum FAQ {message:id=9360002}
                          • 10. Re: clauses
                            BluShadow
                            945059 wrote:
                            i want to use by using having and group by clause to join emp and dept tables can you please send me the query.
                            Having and Group are related to data aggregation, not join conditions.

                            Why do you want to use the wrong tool for the job? You wouldn't use a hammer to try and drill a hole, so why use Having/Group to try and join tables?