-2

Please see the SQL DDL below:

create table tablea (id int identity, name varchar(30))
create table tableb (id int identity, wid int, rid int)
create table tablec (id int)

insert into tablea(name) values ('ian')
insert into tableb(wid,rid)values (1,1)
insert into tablec values(1)
insert into tablec values(2)
insert into tablec values(3)

and the SQL statement below:

select tablea.* from tablea
left join tableb on tablea.id=tableb.wid
right join tablec on tablea.id=tablec.id

I wasn't sure what to expect with this. The results are:

2
3

I was expecting three results initially. Why are there two results? I realise this is a basic question, but I have not used SQL statements like this before.

The reason I expected three rows is because the following query returns one row:

select tablea.* from tablea
left join tableb on tablea.id=tableb.wid

and the following query returns two rows:

select tablea.* from tablea
right join tablec on tablea.id=tablec.id

and 2+1=3.

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • 2
    How can the results be "2 3" if you are selecting tableb.*, which only has (1,1) in it? – Blorgbeard Jun 11 '13 at 22:19
  • 3
    http://sqlfiddle.com/#!3/bc68c/1 <-- my results are different. – Blorgbeard Jun 11 '13 at 22:22
  • 1
    aviod right joins, They can be rewritten as left joins and it makes it easier to read and understand – exussum Jun 11 '13 at 22:24
  • 1
    @user1281385 - For multi table joins sometimes the right join version can be simpler. [Example](http://stackoverflow.com/a/7313507/73226). To use a `left join` there you need two `ON` clauses in succession or to introduce a derived table or CTE. – Martin Smith Jun 11 '13 at 22:29
  • Check your results again, they still don't match. [See here](http://sqlfiddle.com/#!3/bc68c/5) – Chad Jun 11 '13 at 22:41

1 Answers1

2

I'm not totally sure what you're trying to do, but perhaps you have a typo in selecting from tableb? If you change it to select from tablec:

select tablec.* from tablea
left join tableb on tablea.id=tableb.wid
right join tablec on tablea.id=tablec.id

That returns each row from tablec as you said you initially expected.

Chad
  • 7,279
  • 2
  • 24
  • 34