Article:
  More on JOINS
Subject:   Right outer joins in MS SQL SERVER
Date:   2002-05-01 02:13:54
From:   sanjayk
I am interested in knowing how the right outer joins work in sql server. I noticed the difference in behavior between the =* and the phrase "right outer join". The result set is different. Please go through the T-sql statements shown below.


select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)



select a.au_id, b.title, c.qty
from titleauthor a
right outer join titles b
on (a.title_id = b.title_id )
right outer join sales c
on (a.title_id = c.title_id )


The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. Can you through some light on this?


Full Threads Newest First

Showing messages 1 through 5 of 5.

  • Right outer joins in MS SQL SERVER
    2003-07-10 11:59:34  anonymous2 [View]

    The first query works as a cross join because you are not telling the system what type of join you want to use. The second one is written properly.
    • Right outer joins in MS SQL SERVER
      2005-07-17 15:20:59  MJC [View]

      NO MESSAGE
      • Right outer joins in MS SQL SERVER
        2006-09-05 03:09:23  ChandanaRavi [View]

        • Right outer joins in MS SQL SERVER
          2006-09-12 06:13:16  husain_shabbir [View]

          see the data
    • Right outer joins in MS SQL SERVER
      2006-01-28 23:31:14  JCrespin [View]

      The answer given is correct. There is a cross join between table titles and table sales as you have not defined the join criteria.

      If you want to receive the same result that you receive with the right outer join query you have to define the join between titles and sales (see below)...

      select a.au_id, b.title,
      c.qty
      from titleauthor a,
      titles b,
      sales c
      where
      (a.title_id =* b.title_id)
      and (a.title_id =* c.title_id)
      and (b.title_id = c.title_id)
      order by a.title_id

      That said, definitely use the second query.