--- In firebird-support@yahoogroups.com, "Paul R. Gardner"
<gardnerp@y...> wrote:

>
> Let's say I have the following select statement:
>
> Select a.Field1, b.Field2
> From A
> Join B on A.KeyField1 = B.KeyField2
>
> Instead of JOIN, what is the difference between the following:
>

Join (aka inner join) only returns the record if there is a match
between A.KeyField1 = B.KeyField2.

> 1. Left Join

This will return all records matching your where criteria from 'A',
even if there is no match to 'B'. b.Field2 will be NULL in these
cases.

> 2. Right Join

Opposite of left join. This will return all records matching your
where criteria from 'B', even if there is no match to 'A'. A.Field1
will be NULL in these cases.

> 3. Left Outer Join

Same as left join (I believe, correct me if I am wrong)

> 4. Right Outer Join

Same as right join (I believe, correct me if I am wrong)

> 5. Left Inner Join

No such thing

> 6. Right Inner Join

No such thing

> 7. Join <-- Just plain old JOIN is valid as well, right?

Yes, 'inner join' is exactly the same thing. It requires that there
exists a record where A.KeyField1 = B.KeyField2.

>
> OR
>
> 8. A different approach:
>
> Select a.Field1, b.Field2
> From A, B
> Where A.KeyField1 = B.KeyField2

This is the SQL-89 version of a join, and is equivalent to 7.

7 is better though. It is far more readable and maintainable, because
you don't get your joining conditions mixed in with your where logic.

There is also another you have not mentioned, full outer join. This
returns the same as a join except it also includes records from both
tables where no match can be found.

>
>
> Which is better 1 - 8? Why?
> I've always used #1 only, and I'm wondering if this is wrong and if

so,

> for what scenarios.

It is wrong where you don't want to see the record if there is no
matching record in 'b'

For example, if your code looks like this:

Select a.Field1, b.Field2
From A
left Join B on A.KeyField1 = B.KeyField2
where b.KeyField2 is not null

Then you will get more readable code (and normally far better
performance to boot) if you replace the left join with:

Select a.Field1, b.Field2
From A
Join B on A.KeyField1 = B.KeyField2

(you no longer need the where clause because this is implied by the
inner join).

You also force the optimiser to run through table A first.

But basically, you decide what to use based on what records you want
to return. The "wrong" join is the join that doesn't join the table
the way you need it to. 7 gives the optimiser the most flexibility.

Example

Customer (ID, Name)
1 Fred
2 Barney
3 Wilma

Orders (ID, CustomerID, Product)
1 1 Product1
2 1 Product2
3 3 Product1

----

select c.name, o.product
from Customer c
join Orders o on (c.id = o.customerid)

Fred Product1
Fred Product2
Wilma Product1

select c.name, o.product
from Customer c
left join Orders o on (c.id = o.customerid)

Fred Product1
Fred Product2
Barney <null>
Wilma Product1

etc.

Hope that helps

Adam