473,854 Members | 1,837 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help on inner joins.

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.First Name, Employees.LastN ame, TerritoryID,
Employees.Emplo yeeID,
RegionID, ProductID
from Employees
inner join EmployeeTerrito ries on
EmployeeTerrito ries.TerritoryI D > 98005
left outer join Region on Region.RegionID = 4
inner join Products on Products.Produc tID > 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
3 6423

"Prem" <pr*******@hotm ail.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com...
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.First Name, Employees.LastN ame, TerritoryID,
Employees.Emplo yeeID,
RegionID, ProductID
from Employees
inner join EmployeeTerrito ries on
EmployeeTerrito ries.TerritoryI D > 98005
left outer join Region on Region.RegionID = 4
inner join Products on Products.Produc tID > 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 EmployeeTerrito ries 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
>> 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
EmployeeTerrito ries 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,
EmployeeTerrito ries 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 "unpreserve d
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

hi,

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3079
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me solve my problem. I'll turn to MySQL doc after getting through this pressing project. Thanks a lot Roger! Babale -----Urspr=FCngliche Nachricht-----
3
2466
by: Troy | last post by:
I would really appreciate it if someone could give me the answers or direct me to a place that would have the answers to the following 5 questions. 1. I need an example of SQL select statement. (table tblSource, fields A,B,C where C<100).. "is this an example that I have in ( )? 2. I need to list 3 table joins or is "inner, outer, left" etc actual table joins? what are the others for my own knowledge. 3. Can you join a table to...
4
2050
by: michaelnewport | last post by:
Greetings, I like to write my inner joins as below, but someone at work tells me its not as 'performant' as using the 'inner join' statement. Is this true ? Is there a better way to write it ? thanks
2
1632
by: gkellymail | last post by:
the following query works fine: select link.idx, link.x_table, link.x_id_a, link.x_id_z, a.strandid, b.strandid from link_detail, link, strand A, strand B where link_detail.x_table = 'enclosure' and link_detail.x_id = 3 and link.idx = link_detail.linkidx and A.strandid = link.x_id_a and B.strandid = link.x_id_z would someone please convert this to a more efficient query using inner
6
9326
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: tblEmployees empId -- EmpName -- EmpRole -- EmpManager -------....------------.... ---------....--------------- 1........ dan yella..........1..........2
3
16516
by: Zeff | last post by:
Hi all, I have a relational database, where all info is kept in separate tables and just the id's from those tables are stored in one central table (tblMaster)... I want to perform a query, so all data from the separate tables is shown in a view (instead of the reference id's pointing to the separate tables...) I have some troubles formulating the SQL statement: I tried:
12
13193
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
0
1288
by: stanlew | last post by:
Happy New Year everyone! I'm new to both T-SQL and this forum. I'm currently doing an internship and my first task was to create a small program which will send an email detailing the sales of the previous day versus monthly targets and sales. Most of the parts were figured out and eveything was done in Visual Studio. The gist of the code was written in one large chunk of SQL code, as below: SELECT derivedtbl_1.family AS 'Family',...
9
11933
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example: http://www.codinghorror.com/blog/archives/000976.html
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10362
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9510
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7909
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7076
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5937
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4149
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.