What's the difference between various joins?

[may1hem]

Would someone please enlighten me as to the difference between these types of joins:

- Inner and Equi-join
- Full outer and Non-equi-join

One book I read implies that Inner is the same type of join as Equi-join, whereas an article I read online implies that they are different. Are they exactly the same or do they produce the same results using different syntax or are they completely different.

I'm confused... Please help!

Thanks,

May

 

[r937]

i'd be interested to see the online article you mentioned that an inner join and equi-join are different

technically, i guess equi-join is a special case of inner

inner join finds a match, and equi-join uses equality for the join condition

an inner join which uses inequaltiy is called a theta join (i have no idea why, i'm not a theoretician) --

[tt]select a.one, b.two
from a inner join b
on a.id > b.id[/tt]

full outer finds unmatched in both tables, whereas left outer and right outer find unmatched in only one

rudy

 

[may1hem]

Thanks Rudy.

This doesn't really explain the differences fully for me.

Can anyone explain this further?

Thanks,

May

 

[r937]

sure, i can, unless you are specifically excluding me

what didn't you understand? got a couple of tables that you want me to use as an example of each type of join?

 

[PruSQLer]

As Rudy said, the difference between an Equi Join and an Inner Join is very slight. And there is no difference in how they are executed by the DBMS.

EQUI JOIN

Select t1.name, t2.address
From table1 t1
, table2 t2
Where t1.ID = t2.ID

INNER JOIN

Select t1.name, t2.address
From table1 t1 INNER JOIN table2 t2
On t1.ID = t2.ID

Both of the above are joining on the ID but the criteria goes in the Join clause in one while the criteria is in the Predicate in the EQUI. The result is the same.


The difference between a non-equi join and a full outer join are vast.
A NON-EQUI Join goes like this:

Select name, quota, city, sales_total_yr
From Salesreps sale, offices off
Where off.quota < sale.sales_total_yr

With this query you'll get the info in the SELECT statement whenever a row on Offices has a quota that is less than the sales total on the Salesrep table.

The FULL OUTER JOIN is coded like this:

Select name, city
from Salesreps sale Full Outer Join Offices off
On sale.sale_ID = off.sale_ID

The result of this query is in effect, an Inner Join, a Left Outer Join, and a Right Outer Join. In other words, you'll get all rows which match on Sale_ID plus those that don't match. So, if Salesrep has 2 erroneous IDs and Offices has 5 erroneous IDs, those seven rows will show up on the results despite the fact that they don't match on Sale_ID.

Hope that clears up your confusion.