By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,445 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,445 IT Pros & Developers. It's quick & easy.

Need help on inner joins.

P: n/a
Hi,
I am having many problems with inner join. my first problem is :

1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName, Employees.LastName, TerritoryID,
Employees.EmployeeID,
RegionID, ProductID
from Employees
inner join EmployeeTerritories on
EmployeeTerritories.TerritoryID > 98005
left outer join Region on Region.RegionID = 4
inner join Products on Products.ProductID > 76
inner join Employees e on e.EmployeeID > 5
left outer join City on stateID > 5
inner join State on CountryId = 2

In this query form where should i start evaluating. Which join should
i evaluate first?

my second problem is i want to know the differance between following
two queries.

1)
Select table1.a, table2.b
from table1 Inner Join table2 On table1.c > table2.d or table2.d < 20

2)
Select table1.a, table2.b
from table1 Inner Join table2
Where table1.c > table2.d or table2.d < 20

Are these queries produce same result? If yes, then which should be
used and which should not and why?
please help me...
Thanks and best regards.

Prem.
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Prem" <pr*******@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Hi,
I am having many problems with inner join. my first problem is :

1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName, Employees.LastName, TerritoryID,
Employees.EmployeeID,
RegionID, ProductID
from Employees
inner join EmployeeTerritories on
EmployeeTerritories.TerritoryID > 98005
left outer join Region on Region.RegionID = 4
inner join Products on Products.ProductID > 76
inner join Employees e on e.EmployeeID > 5
left outer join City on stateID > 5
inner join State on CountryId = 2

In this query form where should i start evaluating. Which join should
i evaluate first?

my second problem is i want to know the differance between following
two queries.

1)
Select table1.a, table2.b
from table1 Inner Join table2 On table1.c > table2.d or table2.d < 20

2)
Select table1.a, table2.b
from table1 Inner Join table2
Where table1.c > table2.d or table2.d < 20

Are these queries produce same result? If yes, then which should be
used and which should not and why?
please help me...
Thanks and best regards.

Prem.


Without seeing your table structures and knowing how they're related, it's
not clear what you're trying to achieve. However, your first query looks
very unusual, because you never specify which columns to join the tables on.
That will give you combinations of possible values (like a CROSS JOIN),
rather than the actual values in the tables. Maybe that's what you want, of
course, but if not then perhaps you want something more like this (ie.
including the columns to join on):

select
...
from
Employees e
inner join EmployeeTerritories et
on e.EmployeeID = et.EmployeeID
...
where
e.EmployeeID > 5 and
et.TerritoryID > 98005 and
...

If your query only has inner joins, then the order doesn't matter. But in
your query you have a combination of inner and outer joins, so the joins
will happen in the order you specify. It might be easier to visualize it as
a series of working tables - the result of the first inner join is put in a
working table, then the working table is left joined to the Region table,
the new result is inner joined to the Products table etc.

Of your second two queries, the second one is not valid MSSQL syntax - you
haven't specified an ON clause. The first query will work, but again, it
will give you multiple possible combinations rather than limiting the result
set (which is usually the point of an inner join).

You might want to check out the Books Online information on joins - there
are lots of example using the pubs database which show how each type of join
works.

Simon
Jul 20 '05 #2

P: n/a
>> 1) I want to know the precedance while evaluating query with
multiple joins. <<

The Standard says that joins are evaluated from left to right,
following the usual rules for parentheses, and that the ON cluase is
associated with the nearest JOIN. The optimizer is allows to
re-arrange the order of evaluation if the results are not changed.
INNER JOINs are associative and commute, OUTER JOINs do not.

let's add the parens so we can see that NONE of your ON clauses have a
JOIN condition!!!

SELECT
FROM
(((((Employees AS E
INNER JOIN
EmployeeTerritories AS T
ON T.territoryid > 98005)
LEFT OUTER JOIN
Regions AS R
ON R.regionid = 4)
INNER JOIN
Products AS p
ON P.productid > 76)
INNER JOIN
Employees AS E2
ON E2.employee_id > 5)
LEFT OUTER JOIN
Cities AS C
ON C.state_id > 5)
INNER JOIN States
ON S.country_id = 2;

This is garbage. Try going back to the old syntax, so can see the
error!!

SELECT ...
FROM Employees AS E,
EmployeeTerritories AS T,
Regions AS R,
Products AS P,
States AS S,
WHERE T.territory_id > 98005
AND R.region_id = 4
AND P.product_id > 76
AND E2.employee_id > 5
AND C.state_id > 5
AND S.country_id = 2
AND <<join conditions>>;
Are these queries produce same result? If yes, then which should be

used and which should not and why? <<

Yes. Which way do you like to write your code? Remeber this does not
apply to outer joins.

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;
Jul 20 '05 #3

P: n/a

hi,

i would like to know if two outer joins are associative
--
Posted via http://dbforums.com
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.