Force Partial Join Order In Sql Server

Edit: People are having a hard time understanding what i want. So here's pretty pictures that explains it in excruciating detail. First join Transactions to Strange: The results s

Solution 1:

As @AaronBertrand mentioned, I'm a little unclear on what you are trying to do, but if you are talking about changing your output, could you just use COALESCE? Example:

SELECT COALESCE(s.Value, d.Value, c.Value), t.*
FROM Transactions as t
LEFT JOIN Strange as s
ON t.id = s.tid
LEFT JOIN Down as d
ON t.id = d.tid
LEFT JOIN Charmed as c
ON t.id = c.tid

Solution 2:

Maybe this solution will help you:

SET ANSI_WARNINGS ON;
GO
BEGIN TRAN;

CREATETABLE dbo.TableA (
    TableAID INTPRIMARY KEY,
    DescriptionA VARCHAR(50) NOTNULL
);
INSERT dbo.TableA 
VALUES (1,'A-1'), (2,'A-2');

CREATETABLE dbo.TableB (
    TableBID INTPRIMARY KEY,
    DescriptionB VARCHAR(50) NOTNULL
);
INSERT dbo.TableB
VALUES (1,'B-1'), (2,'B-2'), (4,'B-4');

CREATETABLE dbo.TableC (
    TableCID INTPRIMARY KEY,
    DescriptionC VARCHAR(50) NOTNULL
);
INSERT dbo.TableC
VALUES (1,'C-1'),(3,'C-3'), (4,'C-4');
GO

CREATETABLE dbo.[Transaction] (
    TransactionID INTIDENTITYPRIMARY KEY,
    TranDate DATENOTNULL,
    Col1 INTNULL
);
INSERT dbo.[Transaction]
VALUES ('20120101', 1), ('20120202',2), ('20120303',3), ('20120404',4), ('20120505',5);
GO

SELECT*FROM    dbo.[Transaction] t
OUTER APPLY (
    SELECT*FROM TableA a WHERE t.Col1=a.TableAID
) j1 --first joinOUTER APPLY (
    SELECT*FROM TableB b WHERE j1.TableAID ISNULLAND t.Col1=b.TableBID --First condition will force the join order (dbo.TableA.TableAID should be NOT NULL)
) j2 --second joinOUTER APPLY (
    SELECT*FROM TableC c WHERE j1.TableAID ISNULLAND j2.TableBID ISNULLAND t.Col1=c.TableCID ---First two conditions will force the join order (dbo.TableA.TableAID & dbo.TableB.TableBID should be NOT NULL)
) j3 --third joinWHERE   j1.TableAID ISNOTNULLOR      j2.TableBID ISNOTNULLOR      j3.TableCID ISNOTNULLROLLBACK;

In this case, the join order is:

1) t.Col1=a.TableAID

2) if not 1) then t.Col1=b.TableBID

3) if not 1) and 2) then t.Col1=c.TableCID

Results:

TransactionIDTranDateCol1TableAIDDescriptionATableBIDDescriptionBTableCIDDescriptionC---------------------------------------------------------------------------------------12012-01-01 11A-1NULLNULLNULLNULL22012-02-02 22A-2NULLNULLNULLNULL32012-03-03 3NULLNULLNULLNULL3C-342012-04-04 4NULLNULL4B-4NULLNULL