Is this query a join?

• shyam.oec

  Old Hand

  Points: 380

  More actions

  March 31, 2010 at 6:48 pm

  #225722

  Hi ,

  i have two tables emp1,emp2 with id field in both tables.

  when i wright this query " select emp1.id from emp1,emp2" a long list is displayed with values from emp.id repeating for the number of times as there are number of rows in emp2 table. So is this a type of join.

  What basically happens when i give more than one table name in a query?

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  March 31, 2010 at 8:14 pm

  #1143600

  select emp1.id

  from emp1,emp2

  Yes, this format produces an implicit CROSS JOIN of the two tables, resulting in a cartesian product. Even though you are only requesting that one column be returned, you are getting back a number of rows equal to the number of rows in emp1 multiplied by the number of rows in emp2.

  You can limit the number of rows returned by adding a WHERE clause, or by changing over to an express JOIN:

  SELECT emp1.id

  FROM emp1

  JOIN emp2 ON emp2.id = emp1.id -- or some other column that defines the relationship

  Even in the case of the express JOIN, you may get multiple rows with a given emp1.id, if that id matches more than one row in the emp2 table.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  March 31, 2010 at 9:50 pm

  #1143637

  Heh... I love cross joins... they make good Tally tables and interesting questions. 😀

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  March 31, 2010 at 10:05 pm

  #1143647

  It is good practice to explicitly define your joins. If for nothing else than to have better readability of the code and to better control the result set.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events