?
Solved

Nested Inner Join Question ??

Posted on 2005-03-04
8
Medium Priority
?
1,371 Views
Last Modified: 2008-02-01
This is basically a question about how an inner joins works .

I have this query :

Select a1.Name, b2.Name, c3.Name, d4.Name
from a1
inner join b2
on a1.NameID = b2.NameID
inner join c3
on a1.NameID = c3.NameID
inner join d4
on a1.NameID = d4.NameID

and

Select a1.Name, b2.Name, c3.Name, d4.Name
from a1
inner join b2
on a1.NameID = b2.NameID
inner join c3
on b2.NameID = c3.NameID
inner join d4
on c3.NameID = d4.NameID


The differences are on the "ON Clause" . Would there be difference on results between these two queris ?

How does a inner nested query work ?

I always thought, the query will first resolve the first inner query and use that result to do the second inner query and so on.

Thanks,
Hang

0
Comment
Question by:hanglam1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 13460670
What is the database?
0
 

Author Comment

by:hanglam1
ID: 13460989
ptjcb ,

The database is Microsoft SQL Server 2000
0
 
LVL 6

Assisted Solution

by:izblank
izblank earned 400 total points
ID: 13461212
For these two particular queries, no difference - they're algebraiclly equivalent.  For inner joins, order of execution does not matter - but it maters for outer joins, so be careful.  I always explicitly specify join order whenever an outer join is present.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 15

Assisted Solution

by:Ralf Klatt
Ralf Klatt earned 400 total points
ID: 13461877
Hi,

You can address a specific question or create a filter to be used within a "parent" (main) query by including it as a subquery (nested in the parent query!).

I.e. ... a correlated subquery is processed by joining a column in the subquery to a column in the parent query. A "normal" subquery executes the inner and outer query once, returning the value of the inner query to the outer query ... instead, a "correlated" subquery executes the outer query multiple times, once for each row returned by the inner query.

So, as you've asked for the "inner nested query" ... you'll always get a filtered result, as long as a filter is involved -> outer means you'll get all related results [including "NULL" sets!] ... you may imagine which way to filter could be the more efficient way ...


Best regards,
Raisor
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 400 total points
ID: 13461892
Because of your table order, I believe the execution path would be the same. I don't have access to a SQL Server environment at the moment, but if you do, you can use Query Analyzer to show the execution path of each statement.

I found this documented: "the order in which you specify the tables in the join becomes the order in which the tables are processed by SQL".
0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 800 total points
ID: 13463440
Raisor: Some dbms can transform correleted subquery to an equivalent join and group by sql...but I don't know about MsSql
jrb1: In statistics dbms, the order of execution is not to be expected from the syntax of the query except with an optimizer hint (which can force the execution order) which you probably quoted the documentation from.
izblank: In proper designed db, the order of execution for outer joins does not matter because they are hierarchically dependent from each other but you are right that this may be the case if all joined fields comes from outer joined tables

Now as Izblank stated, obviously your 2 queries returns the exact same rows but they can be very different on the performance side...

Whatever the table order in your query, a statistic optimizer engine (which MsSql claims to be) should always decide on the lowest cost path (the more efficient one) if the necessary index on NameID are on your tables. This query path can not be assumed to be ordered by your query syntax in any way, it is only based on statistics on your tables (How large, how many rows, NameID distribution...).

Now for the difference, I am not very fluent in SqlServer but not every dbms optimizer tries to exchange joined fields (making them "transitive") and I doubt it can be achieved without some specific tuning because this feature has a high cost at parsing time (Because there are more query path combination to estimate for the optimizer)

Personally, I write query so that they are ordered in the same order that I expect the optimizer to compute as its query path...but that requires to know some volume statistics which is not always possible.

Finally, in your example, I will write syntax 1 if I expect my query plan to always start from table a1.

hth
0
 
LVL 15

Expert Comment

by:Ralf Klatt
ID: 13463537
Hi,

@BigSchmuh ... thanks for the comment in my direction ... not to excuse myself for my statement ... and surely not for seeming offending! ... but, I keep with my opinion that an Inner Join used in a query is more efficient than an outer join ... -> depending on the target of the query ... of course!


Best regards,
Raisor
0
 

Author Comment

by:hanglam1
ID: 13464746
I just want it to confirm with the experts here  that both inner joins return the same values. I tested the queries in MSSQL Query Analyze before and it did return the same values.

Thanks all,
Hang
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question