Outer join question

I have three tables:
1 - people
2 - peopleAddressLinkingTable
3 - Addresses
I want to get all people and their street name.
I thought that this would be simple and thought that the following query
should work:
select * from people, addresses.street
from people
left outer join peopleAddressLinkingTable on people.fieldA =
peopleAddressLinkingTable.fieldB
inner join addresses on peopleAddressLinkingTable.fieldC = addresses.fieldC
Using Enterprise Manager, I built the query using the visual interface, but
this created a slightly different syntax:
select * from people, addresses.street
from users
inner join peopleAddressLinkingTable on users.fieldC =
peopleAddressLinkingTable.fieldC
right outer join people on peopleAddressLinkingTable.fieldB = people.fieldA
Fair enough, these two must be equal, but when I ran both queries, they
returned different information.
So, unless I've messed something up here, these things aren't as equivalent
as I thought.
Why?
Thanks in advance
GriffHello, Griff
The queries are not equivalent. Let me respond by quoting a message
that I've previously wrote in another forum. If it's not clear, I will
get back with details on your particular query.
-- Start quote
To explain better what I understand of it, let me translate the queries
in "Northwind terms" (so that everyone could understand, even if they
don't have your data). This would be the first query (that returns the
unexpected results):
SELECT COUNT(*) FROM Customers C
LEFT JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN [Order Details] D ON O.OrderID=D.OrderID
In Northwind, it should return 2155 (exactly the number of rows from
Order Details).
The second query would be:
SELECT COUNT(*) FROM [Order Details] D
INNER JOIN Orders O ON O.OrderID=D.OrderID
RIGHT JOIN Customers C ON C.CustomerID=O.CustomerID
This will return 2157, because there are 2 customers that have no
orders.
The subquery variant would be something like:
SELECT COUNT(*) FROM Customers C
LEFT JOIN (
SELECT O.CustomerID FROM Orders O
INNER JOIN [Order Details] D ON O.OrderID=D.OrderID
) X ON C.CustomerID=X.CustomerID
This will also return 2157.
The query that alzdba suggested would be:
SELECT COUNT(*) FROM Customers C
LEFT JOIN Orders O
INNER JOIN [Order Details] D ON O.OrderID=D.OrderID
ON C.CustomerID=O.CustomerID
It seems a little odd, but I think that this query really helps us to
understand what's going on. It gives exactly the same result as the
second query (the one with the RIGHT JOIN).
And the query that Vladan suggested would be:
SELECT COUNT(*) FROM Customers C
LEFT JOIN Orders O ON C.CustomerID=O.CustomerID
LEFT JOIN [Order Details] D ON O.OrderID=D.OrderID
This will also give 2157, but it may give different results if there
are Orders that have no Order Details.
Now let me say why I consider this to be normal:
SQL Server evaluates the joins in the order that they are written (the
fact that it may choose a different query plan it's another thing, but
all those query plans would give the same result).
In the first query it joins the Customers with the Orders (doing a left
join) and then joins the result (doing an inner join) with the Order
Details. Because the last inner join involves the OrderID column from
the Orders table, SQL Server realizes that any row that would have null
in this column (such as the rows corresponding to the customers with no
orders) will not be included in the final resultset (because when you
compare null with anything, you will get false). Because of this SQL
Server optimizes the operations by doing an inner join in the first
place, and that's why there are no left joins in the query plan.
The second query will perform the inner join between the Orders and
Order Details first, and then join the result with the Customers,
therefore giving different results. I'll skip the third query (the one
with the subquery), because it's obvious.
The fourth query can be written in plain english like this: left join
the Customers with "something" on the CustomerID column; "something" is
computed by joining the Orders with the Order Details on the OrderID
column. This way we can see that SQL will have to execute the inner
join first and then the left join, therefore executing exactly the same
as the second query. We can verify this by looking at the query plans.
We will also notice that the fifth query will have a different query
plan than the others (which could be an indication that it may, indeed,
give different results).
-- End quote
Razvan|||Hi Griff
The queries the tools create is an INNER JOIN, the query that u are
using is an OUTER JOIN.
This is the main difference to see different data in the result
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||I think I understand...
The outer join has to be performed last and the joining starts on the first
table mentioned.
So, do all the inner joins first and then right outer join.
Thanks
Griff|||Hi Griff,
hope this query will give u the solution
select a.people_name,b.street_name from people a inner join
peopleAddressLinkingTable c on
a.people_id = c.people_id inner join address b on b.address_id = c.address_i
d
table structure
create table people(people_id int primary key,people_name varchar(50))
create table Address(address_id int primary key,street_name varchar(100))
create table peopleAddressLinkingTable(people_id int foreign key references
people(people_id), address_id int foreign key references address(address_id)
)
please let me know whether it has given u the answer.
regds
vanitha
"Griff" wrote:

> I have three tables:
> 1 - people
> 2 - peopleAddressLinkingTable
> 3 - Addresses
> I want to get all people and their street name.
> I thought that this would be simple and thought that the following query
> should work:
> select * from people, addresses.street
> from people
> left outer join peopleAddressLinkingTable on people.fieldA =
> peopleAddressLinkingTable.fieldB
> inner join addresses on peopleAddressLinkingTable.fieldC = addresses.field
C
> Using Enterprise Manager, I built the query using the visual interface, bu
t
> this created a slightly different syntax:
> select * from people, addresses.street
> from users
> inner join peopleAddressLinkingTable on users.fieldC =
> peopleAddressLinkingTable.fieldC
> right outer join people on peopleAddressLinkingTable.fieldB = people.field
A
> Fair enough, these two must be equal, but when I ran both queries, they
> returned different information.
> So, unless I've messed something up here, these things aren't as equivalen
t
> as I thought.
> Why?
> Thanks in advance
> Griff
>
>sql