Conditional JOIN

[chandy]

Hi,

I am trying to change an SP from dynamic SQL to proper SQL but I can't
figure a way to conditionally add extra parts to the statement. How
can I do the equivalent of the following?

DECLARE @arg NVARCHAR(10)

SELECT a.i, a.x
FROM aTable a
IF LEN(@arg)
BEGIN
INNER JOIN bTable b ON a.[id] = b.[id]
END

Conditionally adding the INNER JOIN is very easy when building up a SQL
string but I can't see how to do it in pure SQL?

Thanks.

[David Portas]

What do you intend by a conditional join? The purpose of a join is
usually to bring back some extra columns from additional tables. Static
queries also have static metadata (in other words always the same set
of columns are returned every time) so a "conditiona l" join such as you
have posted is really just a selection:

SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @arg = ''

(I'm assuming ID is unique in B otherwise your original query might
return duplicate rows).

In general you can use OR to implement optional criteria but this often
leads to sub-optimal query plans. You should consider using IF
statements to choose from a set of possible queries or just break up
the different queries into separate SPs to be called independently.
Either approach is usually preferable to dynamic SQL.

--
David Portas
SQL Server MVP
--

[chandy]

I possibly gave a less than clear example. Insert an additional join
on aTable:

SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b on a.[id] = b.[id]
IF (LEN(@arg) > 0)
BEGIN
INNER JOIN cTable c ON a.[id] = c.[id]
END

The condition is that if the parameter @arg is of length 0 then I do
not want to join to cTable but if it is greater than 0 then I do. It's
essentially a switch to impose additional restrictions upon the
recordset being returned. I am not changing the structure of what is
being selected, just _conditionally_ adding an extra filter on the
data.

Thanks.

[David Portas]

SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b
ON a.[id] = b.[id]
WHERE EXISTS
(SELECT *
FROM cTable c
WHERE c.[id] = a.[id])
OR @arg = ''

Again, assuming ID is unique in C this is equivalent to an INNER JOIN.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

(ch****@totalis e.co.uk) writes:

I possibly gave a less than clear example. Insert an additional join
on aTable:

SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b on a.[id] = b.[id]
IF (LEN(@arg) > 0)
BEGIN
INNER JOIN cTable c ON a.[id] = c.[id]
END

The condition is that if the parameter @arg is of length 0 then I do
not want to join to cTable but if it is greater than 0 then I do. It's
essentially a switch to impose additional restrictions upon the
recordset being returned. I am not changing the structure of what is
being selected, just _conditionally_ adding an extra filter on the
data.

And that is exactly what David's query achieved. He hinted that his query
might not get a good query plan, but in fact

SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @arg = ''

should perform very well when @arg is ''. SQL Server will understand
that it does have to access bTable at all. It will appear in the plan,
but a so-called startup expression prevents it from being accessed when
there is no need to. (Interested readers can find more details on
this in http://www.sommarskog.se/dyn-search....plexconditions.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[chandy]

Ahh, I get it now, hadn't fully comprehended what the WHERE EXISTS was
doing. That seems to work nicely.

Thanks!