|
|
You're in the
sql server programming
group:VERY ODD (LEFT OUTER JOIN)
sql server programming:
join (an INNER join), plus any NULL matches for the specified table(s). In this case, you specified LEFT OUTER JOIN, so you will also get null matches from the Address table on the left side. Also, I am not sure of the consequences of combining a traditional WHERE clause condition with JOIN ON syntax. I would definitely break them out.... SELECT P.Id, P.Name, A.Address FROM vPeople P LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id) WHERE (A.Deleted<>1) ....but that's just me.
A.Deleted?
Don't you mean P.Deleted?
[quoted text, click to view]
> This returns MORE than 1000 records.. how is that possible? Probably because a person can have more than 1 address? If you have these rows: People ID Name 1 foo 2 bar Addresses ParentID Address 2 1 Oak St. 2 2 Main St. 2 3 Foo St. Your result will be 4 rows: 1 foo NULL 2 bar 1 Oak St. 2 bar 2 Main St. 2 bar 3 Foo St. Also note that p.Deleted <> 1 AND A.Deleted <> 1 are not the same clause. You should probably move the filter criteria out of the join clause to make the semantics more clear, e.g. LEFT OUTER JOIN Addresses A ON A.ParentID = p.Id WHERE A.Deleted <> 1 A couple of other things that would make the design more intuitive: Use PersonID instead of ID. Then you can use the same column name in all tables, and nobody ever has to wonder what kind of ID the query is talking about. Use equality rather than inequality, and positive rather than negative. If your current Deleted column can only be 0 or 1, then I would rather see: AND A.Deleted = 0 But even better would be to have a column called Active. Then the logic behind the column is something that includes them, rather than excludes. This makes the query much easier to translate to and from English. AND A.Active = 1
This would occur when there is more than one record in the addresses
table that relates to the vPeople record. For instances if you have the following vperson records: id name ------- ----------------- 1 Test 1 2 Test 2 3 Test 3 4 Test 4 and the following Address records ParentId Address --------------- ------------------------------------------------ 1 Address 1-1 1 Address 1-2 2 Address 2 4 Address 4 The rows resulting from your query would be expected to be: Id Name Address ------- --------------- ------------------------- 1 Test 1 Address 1-1 1 Test 1 Address 1-2 2 Test 2 Address 2 3 Test 3 4 Test 4 Address 4 Note that there are two records corresponding to ID=1, one for each of the related address records. The only thing that the outer join does is includes the records from vPeople even if there is no corresponding Address record. The only difference in your result set if you used inned joins would be that you would not recieve a row from ID=3 since there is not a corresponding address in this example. Hope this helps. [quoted text, click to view] Lisa Pearlson wrote:
> Hi, > > I'm not sure I understand outer joins. > > I have a join like this: > > -- > CREATE VIEW vPeople > AS > SELECT Id,Name FROM People WHERE Deleted<>1 > -- > > SELECT * FROM vPeople > it returns like 1000 records > > then I have: > > SELECT P.Id, P.Name, A.Address FROM vPeople P > LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id AND A.Deleted<>1) > > This returns MORE than 1000 records.. how is that possible? > > > Lisa >
In the past I have found that putting the traditional WHERE clause in
the JOIN ON can provide a significant performance increase in some situations. That said I do agree that putting it in the WHERE clause is much clearer. That said the main difference between the two in this case is that if you put it in the WHERE clause in this case and a vPerson record exists that only has address records where A.Deleted=1 then no record at all will be returned for the vPerson record. On the other hand when you put it in the JOIN ON a record is returned with a NULL Address. So really the determination of where to put it depends on how you want that situation handled. [quoted text, click to view] joey.powell@topscene.com wrote:
> An outer join will return all that you would normally get on a regular > join (an INNER join), plus any NULL matches for the specified table(s). > In this case, you specified LEFT OUTER JOIN, so you will also get null > matches from the Address table on the left side. > > Also, I am not sure of the consequences of combining a traditional > WHERE clause condition with JOIN ON syntax. I would definitely break > them out.... > > SELECT P.Id, P.Name, A.Address FROM vPeople P > LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id) > WHERE (A.Deleted<>1) > > ...but that's just me.
Hi,
I'm not sure I understand outer joins. I have a join like this: -- CREATE VIEW vPeople AS SELECT Id,Name FROM People WHERE Deleted<>1 -- SELECT * FROM vPeople it returns like 1000 records then I have: SELECT P.Id, P.Name, A.Address FROM vPeople P LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id AND A.Deleted<>1) This returns MORE than 1000 records.. how is that possible? Lisa
Lisa Pearlson (no@spam.plz) writes:
[quoted text, click to view] > I'm not sure I understand outer joins. > > I have a join like this: > > -- > CREATE VIEW vPeople > AS > SELECT Id,Name FROM People WHERE Deleted<>1 > -- > > SELECT * FROM vPeople > it returns like 1000 records > > then I have: > > SELECT P.Id, P.Name, A.Address FROM vPeople P > LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id AND A.Deleted<>1) > > This returns MORE than 1000 records.. how is that possible? Some people have more than one address? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
On Tue, 8 Nov 2005 23:13:57 +0100, Lisa Pearlson wrote: >Hi, > >I'm not sure I understand outer joins. > >I have a join like this: > >-- >CREATE VIEW vPeople >AS >SELECT Id,Name FROM People WHERE Deleted<>1 >-- > >SELECT * FROM vPeople >it returns like 1000 records > >then I have: > >SELECT P.Id, P.Name, A.Address FROM vPeople P > LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id AND A.Deleted<>1) > >This returns MORE than 1000 records.. how is that possible? Hi Lisa, If a row from vPersons is not matched against any row in Addresses, it will still be in the result set (courtesy of the LEFT OUTER join-type). If a row from vPersons is matched against one row in Addresses, it will be in the result set once (same as for INNER JOIN). And if a row from vPersons is matched against several rows in Addresses, it will be in the result set several times; once for each matching row in Addresses (again, same as for INNER JOIN). The last category is the reason that your result has more than 1000 rows. Best, Hugo --
This subject in my opinion deserves a thread of it's own.. I will post new
on "Location of condition in a join" [quoted text, click to view] <joey.powell@topscene.com> wrote in message news:1131488678.240245.123370@g49g2000cwa.googlegroups.com... > An outer join will return all that you would normally get on a regular > join (an INNER join), plus any NULL matches for the specified table(s). > In this case, you specified LEFT OUTER JOIN, so you will also get null > matches from the Address table on the left side. > > Also, I am not sure of the consequences of combining a traditional > WHERE clause condition with JOIN ON syntax. I would definitely break > them out.... > > SELECT P.Id, P.Name, A.Address FROM vPeople P > LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id) > WHERE (A.Deleted<>1) > > ...but that's just me. >
I DO need all records in vPerson to be returned.. so putting the Deleted<>1
inside the ON *is* the right choice for me. Thank you for confirming that where you put that condition really does impact your result so it's a bit odd then that people suggest me breaking it out of the ON clause to make it more 'intuitive' when in fact it leads to a completely different resultset and isn't just a matter of readability only! Lisa [quoted text, click to view] "Steve Hamilton" <shamilton@community.nospam> wrote in message news:u1zmgVL5FHA.2816@tk2msftngp13.phx.gbl... > In the past I have found that putting the traditional WHERE clause in the > JOIN ON can provide a significant performance increase in some situations. > That said I do agree that putting it in the WHERE clause is much clearer. > That said the main difference between the two in this case is that if you > put it in the WHERE clause in this case and a vPerson record exists that > only has address records where A.Deleted=1 then no record at all will be > returned for the vPerson record. On the other hand when you put it in the > JOIN ON a record is returned with a NULL Address. So really the > determination of where to put it depends on how you want that situation > handled. > > > > > joey.powell@topscene.com wrote: >> An outer join will return all that you would normally get on a regular >> join (an INNER join), plus any NULL matches for the specified table(s). >> In this case, you specified LEFT OUTER JOIN, so you will also get null >> matches from the Address table on the left side. >> >> Also, I am not sure of the consequences of combining a traditional >> WHERE clause condition with JOIN ON syntax. I would definitely break >> them out.... >> >> SELECT P.Id, P.Name, A.Address FROM vPeople P >> LEFT OUTER JOIN Addresses A ON (A.ParentId=P.Id) >> WHERE (A.Deleted<>1) >> >> ...but that's just me. >>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |