January 18, 2012 at 1:56 am
is there a way for conditional joins.
like,
when (1=1) inner join table1 on column1 = column2.
when (1=2) inner join table1 on column1 = column2.
etc..
Appreciating your help.
January 18, 2012 at 2:05 am
THe only thing you can do is LEFT JOIN to all tables and then include case logic in the SELECT clause.
Or, you can use dynaminc SQL!
Having the ability to have conditional joins would make it impossible for the optimiser to come up with a reuseable plan.
January 18, 2012 at 2:15 am
DBA Rafi (1/18/2012)
is there a way for conditional joins.like,
when (1=1) inner join table1 on column1 = column2.
when (1=2) inner join table1 on column1 = column2.
etc..
Appreciating your help.
You mean...
INNER JOIN table1 t1 ON x.column1 = CASE WHEN x=y THEN t1.column2 ELSE t1.column3 END
You can but the performance is likely to be poor if x and y are columns. If x and y are variables, then a different plan would be required for each variation of the statement. Most folks prefer to use two left joins (aggregating in a derived table if necessary), resolving the output using CASE:
SELECT
WhichColumnToUse = CASE
WHEN x=y THEN 1a.column2
WHEN x=z THEN 1b.column3
ELSE NULL END
FROM...
LEFT join table1 1a on column1 = 1a.column2
LEFT join table1 1b on column1 = 1b.column3
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 2:42 am
There are lots of ways to achieve that, but I suspect none of them is efficient enough.
Here are a couple of ways:
-- Using CASE
SELECT SomeColumns
FROM table1
INNER JOIN table2
ON 1 =
CASE
WHEN (@variable=1) THEN
CASE WHEN table1.column1 = table2.column2 THEN 1 END
WHEN (@variable=2) THEN
CASE WHEN table1.column3 = table2.column4 THEN 1 END
END
-- Using inline subqueries
SELECT SomeColumns
FROM table1
INNER JOIN table2
ON EXISTS (
SELECT 1
FROM (
SELECT 1 AS columnId, column1 AS columnValue
UNION ALL
SELECT 2 AS columnId, column3 AS columnValue
) AS src1
INNER JOIN (
SELECT 1 AS columnId, column2 AS columnValue
UNION ALL
SELECT 2 AS columnId, column4 AS columnValue
) AS src2
ON src1.columnId = src2.columnId
AND src1.columnValue = src2.columnValue
WHERE src1.columnId = @variable
)
I think dynamic SQL is the way to go here.
-- Gianluca Sartori
January 18, 2012 at 5:53 am
It is possible to do this efficiently with CASE and subqueries, or with start-up filters:
Sample data:
CREATE TABLE #T (col1 integer PRIMARY KEY)
CREATE TABLE #1 (col1 integer PRIMARY KEY, col2 char(1) NOT NULL)
CREATE TABLE #2 (col1 integer PRIMARY KEY, col2 char(1) NOT NULL)
INSERT #T (col1) VALUES (1), (2), (3), (4), (5)
INSERT #1 (col1, col2) VALUES (1, 'a'), (5, 'e')
INSERT #2 (col1, col2) VALUES (2, 'b'), (4, 'd')
Using CASE with subqueries:
SELECT
t.col1,
CASE
WHEN (t.col1 % 2) = 1 THEN
(SELECT a.col2 FROM #1 AS a WHERE a.col1 = t.col1)
WHEN (t.col1 % 2) = 0 THEN
(SELECT a.col2 FROM #2 AS a WHERE a.col1 = t.col1)
ELSE NULL
END
FROM #T AS t
The joins in this plan contain pass-through predicates. The join is only executed per row if the pass-through predicate returns true.
With start-up filters:
SELECT
t.col1,
COALESCE(oa1.col2, oa2.col2)
FROM #T AS t
OUTER APPLY
(
SELECT
a.col2
FROM #1 AS a WITH (FORCESEEK)
WHERE
t.col1 % 2 = 1
AND a.col1 = t.col1
) AS oa1
OUTER APPLY
(
SELECT
a.col2
FROM #2 AS a WITH (FORCESEEK)
WHERE
t.col1 % 2 = 0
AND a.col1 = t.col1
) AS oa2
The joins in this plan are preceded by start-up filters. The table access only occurs if the start-up filter predicate returns true.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply