Expert: Donald Campbell - 7/22/2005

Question
Sir,
Can u pls tell me where we have to use inner join.The result after using cartesian product and inner join gives one of the same result.So i m unable to understand vch one is better in terms of performance and optimisation.
my query was:

INNER JOIN:
select * from aw_latest_quotes as alq
inner join aw_exchanges as ae on alq.exchange_code = ae.exchange_code

CARTESIAN PRODUCT:

select * from aw_latest_quotes as alq
, aw_exchanges as ae where alq.exchange_code = ae.exchange_code

Also tell me when to use outer join and how its differnt from inner join.

Thanks and Regards
Ankit jain


Answer
Hello!

Inner join allows you specifically state a join clause, like alq.exchange_code = ae.exchange_code. The cartesian product is less efficient, because it first joins every possible combination of rows, and then filters the results to match your join clause.

An outer join, when compared to an inner join, displays all information, even if the information does not have an equivalent in the joined field. A great example and demonstration can be found on this page:

http://www.1keydata.com/sql/sqlouterjoin.html

I hope this has helped you understand Cartesian Products and joins!

-Donald Campbell
dcampbell@campbellproductions.net