joins

Question

Post reply

joins

Crazy Nash

SSC Journeyman

Points: 79
More actions
March 22, 2006 at 8:13 am

#112295

can anybody help me know which is faster outer join OR Inner join

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erwin Dockx SSC Eights! Points: 842 More actions · Answer 1

hi Nash,

One of the best ways to boost join performance is to limit how many rows need to be joined. This is especially beneficial for the outer table in a join. An INNER JOIN displays only the rows that have a match in both joined tables (only those rows need to be joined).

If you have a query that use a LEFT OUTER JOIN, check it carefully to be sure that is the type of join you really want to use. As you may know, a LEFT OUTER JOIN is used to create a result set that includes all of the rows from the left table specified in the clause, not just the ones in which the joined columns match. In addition, when a row in the left table has no matching rows in the right table, the result set row contains NULL values for all the selected columns coming from the right table. If this is what you want, then use this type of join.

_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.

You can find me on LinkedIn.
I support The Programmer's Bill of Rights.

MCITP, MCDBA, MCSD

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

Nash,

I don't think that performance is really the maint point of ahaving outer and inner joins.

they both return different sets of data. An outer join will almost always run more slowly than an inner due to the fact it will return more rows (usually) - but you should be asking the question

"to return the correct data do i need an innner or an outer join"

MVDBA

Crazy Nash SSC Journeyman Points: 79 More actions · Answer 3

Hi,

Thanx for the response. What I was confused was the way the data is retrieved, like these queries return the same record

select x.* from tbl1 X

left outer join tbl2 Y On X.ID = Y.ID

where Y.ID IS NOT NULL

select x.* from tbl1 X, tbl2 Y

where X.ID = Y.ID

in case of first query will both tables get scanned ,and the filter applied on the resultset???

Thanks

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

using the outer join with a y.id is not null will do more work than the 2nd query as you will have to do an index seek or table scan 9efefctively you're using criteria to turn an outer into an inner)

if in doubt - load them both into query analyser and generate an execution plan - compare the cost of the 2 queries and see what that gives you

MVDBA