Reproduce conditional LEFT JOIN behavior in WHERE?

Question

I have a problem and right now I am out of ideas.

I am doing some optimisation for a database application. There is a method (Method_A) called a couple hundred times that does this kind of query:

SELECT 
A.a,
ISNULL(A.b, 'Nothing') As alias_b
ISNULL(B.a, 'N/A') as alias_c
FROM A
LEFT JOIN B on A.fk=B.fk AND B.a = 'SOME_KEY'
WHERE A.c = 'SOME_OTHER_KEY'

Resulting in one row looking like this:

[a      ][alias_b][  alias_c  ]
[ val_a ][ val_b ][val_c|'N/A']

What I want to do in order to optimize my code is to make a single request before Method_A, retreiving all the data and make Method_A filter client-side. (It is coded in .NET, I am replacing an OleDBConnection with a DataSet)

How could I acheive this kind of behavior? Because if I filter with a client-side condition, instead of getting 'N/A' in the case where the join returns nothing, I just get no row, and this is a problem since I still need val_a and val_b

Thank you for your help!

You want to have all of both `A` and `B` sent to the client individually, and then have the client do the join? — Matthew, Dec 13 '12 at 18:13
How are you doing the join/filter client side, this is how to do a left join in LINQ http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join — Ben Robinson, Dec 13 '12 at 18:15
I was looking for a server-side solution (in SELECT or WHERE) but yeah I think I am not going to have the choice to retreive A and B separately and do the join client-side, am I right? — m6a-uds, Dec 13 '12 at 18:18
@m6a-uds I'm having trouble understanding what you want to accomplish. — Matthew, Dec 13 '12 at 18:21
How about joining in an additional join with a derived list of all known 'SOME_OTHER_KEY' type values? Then you can get back all the possibilities before your call to Method_A. `INNER JOIN (SELECT distinct some_key from some_key_table) keys LEFT JOIN B on A.fk=B.fk AND B.a = keys.some_key` — DWright, Dec 13 '12 at 18:22

score 0 · Answer 1 · answered Dec 13 '12 at 23:44

You can try this, basically split the two cases where there are non matching B elements and when there are matching (and do an inner join), maybe it will have a cleaner execution plan:

(SELECT A.a,
        ISNULL(A.b, 'Nothing') As alias_b
        'N/A' as alias_c
 FROM A 
 WHERE A.c = 'SOME_OTHER_KEY' and
  not exists (select B.fk FROM B WHERE A.fk=B.fk AND B.a = 'SOME_KEY')
)
UNION ALL
(SELECT A.a
        ISNULL(A.b, 'Nothing') As alias_b
        B.a as alias_c
 FROM A, B
 WHERE A.c = 'SOME_OTHER_KEY' and 
       A.fk= B.fk and 
       B.a = 'SOME_KEY'
)

Note you need the following indexes:

A(c)
A(fk)
B(fk)
B(fk,a)
A(fk,c)

Sorry for the delay, I was on my week-end. This solution works if 'SOME_KEY' is known, but I would like to load every possible value of SOME_KEY... Sorry, now that I thought about it for a while it might just not be technically possble in my case. :-/ Well, I suppose this was kind of a bad question after all... — m6a-uds, Dec 18 '12 at 20:37

Reproduce conditional LEFT JOIN behavior in WHERE?

1 Answers1