all groups > sql server programming > november 2006 >
You're in the

sql server programming

group:

Meaningful outer join with predicate


Meaningful outer join with predicate Steve Dassin
11/29/2006 10:43:22 PM
sql server programming:
Given a query of the form:

SELECT <columns>
FROM T1 LEFT JOIN T2 ON T1.<column>=T2.<column>

does anyone have an example/link where adding a predicate
to ON concerning a column(s) from T1:

SELECT <columns>
FROM T1 LEFT JOIN T2 ON T1.<column>=T2.<column>
and T1.<column> (predicate) [value]
(ie. T1.<column>=4)

is 'meaningful'. In other words, an example where adding a predicate(s)
concerning 'T1' solved a real problem.
I'm not interested in the mechanics, ansi, optimizer or indexes:)
Just how it was used to answer a 'real' logical question.

Thanks,
steve

Re: Meaningful outer join with predicate Uri Dimant
11/30/2006 12:00:00 AM
Steve
In your example it shows that you have already got all data from LEFT (T1)
table , so adding a predicate to one of the columns from T1 won't show you
an answer a 'real' logical question.

Consider this example

create table customers (customerid int primary key,custname varchar(50) not
null)
go
insert into customers values (1,'John')
insert into customers values(2,'Steve')
insert into customers values(3,'Uri')
go
create table orders (orderrid int primary key,customerid int references
customers(customerid))
go
insert into orders values(1000,1)
insert into orders values (2000,2)

--usage
select o.customerid,c.customerid from customers c left join orders o on
c.customerid=o.customerid
where o.customerid<>2 or o.customerid is null --filter out customerid=2

select o.customerid,c.customerid from customers c left join orders o on
c.customerid=o.customerid
and o.customerid<>2 --it was filterd out before join ,however , because it
is NULL ,OUTER JOIN returns the value
go
drop table orders,customers



[quoted text, click to view]

Re: Meaningful outer join with predicate Tony Rogerson
11/30/2006 12:00:00 AM
I've only needed this functionality a small number of times so far, but here
is an example based from memory; essentially i wanted a row to come out and
was joining for stuff from the other table based on status

declare @individual table (
id int not null identity,
fullname varchar(100)
)
declare @tasks table (
id int not null identity,
individual_id int not null,
is_complete char(1) not null
)

set nocount on

insert @individual ( fullname ) values ( 'tony rogerson' )
insert @individual ( fullname ) values ( 'trevor dwyer' )
insert @individual ( fullname ) values ( 'simon sabin' )

insert @tasks ( individual_id, is_complete ) values( 1, 'N' )
insert @tasks ( individual_id, is_complete ) values( 1, 'N' )
insert @tasks ( individual_id, is_complete ) values( 1, 'Y' )
insert @tasks ( individual_id, is_complete ) values( 1, 'Y' )
insert @tasks ( individual_id, is_complete ) values( 1, 'N' )

insert @tasks ( individual_id, is_complete ) values( 2, 'N' )
insert @tasks ( individual_id, is_complete ) values( 2, 'N' )

-- gives a row for each individual correctly.
select i.id,
i.fullname,
task = coalesce( 'task id ' + cast( t.id as varchar(10) ) + ' needs
doing', 'nothing todo' )

from @individual i
left outer join @tasks t on t.individual_id = i.id
and t.is_complete = 'N'

-- this loses the row...
select i.id,
i.fullname,
task = coalesce( 'task id ' + cast( t.id as varchar(10) ) + ' needs
doing', 'nothing todo' )

from @individual i
left outer join @tasks t on t.individual_id = i.id
where t.is_complete = 'N'



--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


[quoted text, click to view]

Re: Meaningful outer join with predicate Steve Dassin
11/30/2006 12:30:31 AM
Hi Uri,

My example wasn't really an example with data. It was just the
general form of a query with outer join to which a predicate is added.
Perhaps you can come up with something using any form of outer
join where the predicate concerns the inner (1st table). Use your
data any way you wish. Again I'm really interested in what question
the query answers.

thanks,
steve

[quoted text, click to view]

Re: Meaningful outer join with predicate Alex Kuznetsov
11/30/2006 6:26:38 AM

[quoted text, click to view]

Steve,

"I have a high priority task and I need to assign it ASAP.
Show me all our developers. If the developer is already working on a
hign priority ticket, show it as well."

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
Re: Meaningful outer join with predicate Anith Sen
11/30/2006 7:54:15 AM
The answer is no/none. A predicate specific to the preserved table ( or the
"left" table ) in the ON clause is the same as using it in the WHERE clause.

One could however generate a "pseudo-problem" by using a correlated
sub-query where the correlated column is compared to the non-preserved table
in the outer query. For example:

SELECT *
FROM t1 LEFT OUTER JOIN t2
ON t1.x = t2.x
AND t1.y = ( SELECT t3.y FROM t1 t3
WHERE t3.z = t2.z ) ;

Vs.

SELECT *
FROM t1 LEFT OUTER JOIN t2
ON t1.x = t2.x
WHERE t1.y = ( SELECT t3.y FROM t1 t3
WHERE t3.z = t2.z ) ;

--
Anith

Re: Meaningful outer join with predicate Tom Cooper
11/30/2006 12:05:15 PM
Hi Steve,

Another example would be if you had a customer table and a table of orders.
If some of the customers had never ordered any products from you and you
wanted a report of all customers (including those who had never ordered) and
the products they had ordered, that query would look something like:

Select c.CustName, o.ProductID
From Customers c
Left Outer Join Orders o On c.CustID = o.CustID
Order By c.CustName

But if the products ordered by a customer was considered sensitive
information and you wanted to suppress the products ordered if the customer
had "opted out" and you also wanted to suppress the products ordered for
anyone who is under 18, then you could use something like:

Select c.CustName, o.ProductID
From Customers c
Left Outer Join Orders o On c.CustID = o.CustID
And c.OptOut <> 1
And c.DateOfBirth <= DateAdd(yy, -18, GetDate())
Order By c.CustName

which would show only customer information, but no order information for
clients who opted out or were too young.

Tom

[quoted text, click to view]

Re: Meaningful outer join with predicate Steve Dassin
11/30/2006 3:05:13 PM
[quoted text, click to view]

Certainly the 'placement' of the predicate affects the querys
evaluation.

Modifying Tonys data some:

create table #individual (
[id] int ,
fullname varchar(15),
likeCelko char(1)
)

create table #tasks (
[id] int ,
individual_id int not null,
is_complete char(1) not null
)

insert #individual values ( 1,'tony rogerson','N' )
insert #individual values ( 2,'trevor dwyer','Y' )
insert #individual values ( 3,'simon sabin','Y' )

insert #tasks values(1, 1, 'N' )
insert #tasks values(2, 1, 'N' )
insert #tasks values(3, 2, 'Y' )
insert #tasks values(4, 2, 'Y' )

Using likeCelko='Y' in on:

select fullname,likeCelko,individual_id,is_complete
from #individual A left join #tasks B
on A.[id]=individual_id and likeCelko='Y'

fullname likeCelko individual_id is_complete
--------------- --------- ------------- -----------
tony rogerson N NULL NULL
trevor dwyer Y 2 Y
trevor dwyer Y 2 Y
simon sabin Y NULL NULL

Using likeCelko='Y' in where:

select fullname,likeCelko,individual_id,is_complete
from #individual A left join #tasks B
on A.[id]=individual_id
where likeCelko='Y'

fullname likeCelko individual_id is_complete
--------------- --------- ------------- -----------
trevor dwyer Y 2 Y
trevor dwyer Y 2 Y
simon sabin Y NULL NULL

[quoted text, click to view]

"The answer is no/none". I agree. Of what 'logical' value is there using
a predicate from the inner table in on? Surely the predicate in WHERE
conveys the real intent of the query. Why would I want to include
no information from #tasks and include a fullname I am explicitly
asking it to exlude (likeCelko='Y') using the predicate in on? Who does
such a thing?-:) If I had a million rows where only 2% liked Celko the
logic seems even more compelling. In fact where else in the language
can I include rows that I am attempting to eliminate? One wonders what
is the real intent of this manifestion of the construct.

Of course we can go even further. Using a predicate with the outer table
(#tasks) will serve to eliminate its 'rows'. Which could be easily
accomplished in a derived table with a restriction. Looking at it like
this the predicate from the outer table in on is merely a shortcut,
obviating the need to construct a preliminary derived table for the outer
join. If using a predicate in on with an inner table is 'illogical' and
the predicate is merely a shortcut when used with the outer table,
it would appear it brings nothing new to the language. What have
I missed?

best,
steve




Re: Meaningful outer join with predicate Steve Dassin
11/30/2006 3:06:54 PM
Hi,

I appreciate the response. Please see my reply to Anith.

Re: Meaningful outer join with predicate Steve Dassin
11/30/2006 3:07:33 PM
Hi,

I appreciate the reply. Please see my reply to Anith.

Re: Meaningful outer join with predicate Tom Cooper
11/30/2006 10:32:58 PM

[quoted text, click to view]

I would agree that this construct is very rarely used. But it's not
'illogical' (IMHO). Anything that follows the rules of the language and
produces the correct result is logical to me. Of course, it may not be the
best possible code and I would encourage people to avoid unusual constructs
simply because they can be more difficult for someone else to understand and
therefore more difficult to maintain. However, in some cases unusual code
is easier to understand and/or significantly more efficient. In those
cases, I'm all for using unusual constructs, with, of course, adquate
comments so the next person can figure out what is going on.

I'm sure that anything that could be done with this construct could be done
in some other manner. But that is true of almost anything you do in almost
any computer language. By the same argument, there would be no need for
INNER JOINs since anything that can be done with an INNER JOIN can be done
with a CROSS JOIN by moving the ON clause into the WHERE clause, that is:
SELECT * FROM TABLEA
INNER JOIN TABLEB ON blahblah...
is equivalent to
SELECT * FROM TABLEA
CROSS JOIN TABLEB
WHERE blahblah...

You say "the predicate from the outer table in on is merely a shortcut,
obviating the need to construct a preliminary derived table for the outer
join". Maybe so, but not necessarily relavent. By that argument, we don't
need a multiply operator (*) since we have addition (+). Would you argue
"multiply is merely a shortcut, obviating the need to construct a loop
adding the value the correct number of times"? I think not.

Tom

[quoted text, click to view]

Re: Meaningful outer join with predicate Steve Dassin
11/30/2006 11:44:28 PM
[quoted text, click to view]

No and I wouldn't argue with Update replacing deletes/inserts or Case
replacing
characteristic functions. But here the situation is not so clear:) My guess
is that the
major consideration is for performance (outer table predicate) and they got
left holding
the bag for the predicate in the inner table.
Interestingly, MS says so little on this subject I don't know how anyone
could really
know. BOL says little and what is said (see FROM) makes little or no sense.
Perhaps you will find this article interesting (courtesy of Alex Kuznetsov):
"Meet the experts: Terry Purcell on coding predicates in outer joins"
http://www-128.ibm.com/developerworks/db2/library/techarticle/purcell/0112purcell.html

but don't expect much when it comes to 'what does it really mean' -:)

best,
steve

http://racster.blogspot.com

Got something to say? Click here to post a reply to this thread.
Don't see what you're looking for? Try a search.
View other messages in the sql server programming group.
AddThis Social Bookmark Button
View Other Months
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