Conditional JOIN in a view possible?

  • I have a Table1 which I need to JOIN with either Table2 or Table3, depending if a related record exists in T3 or T2. A related record cannot exists in both tables: if there is a related record in T2 then there is no in T3, and vice verse. Also, there is only 1 related record, if any.

    I can do this in a SP with the use of a virtual table, or even some other programming. However, for reasons I won't go into a view is almost demanded.

    The datamodel, which splits similar data over 2 tables is not my design, this is the situation I am confronted with, reperations of the model is highly unlikely.

    TIA,

    Greetz,
    Hans Brouwer

  • Left Joins with an COALESCE?

    CREATE VIEW SSCTempView

    AS

    SELECT A.T1IDent, COALESCE(B.Value, C.Value) MyValue

    FROM Table1 A

    LEFT JOIN Table2 B ON A.T1Ident = B.T2Ident

    LEFT JOIN Table3 C ON A.T1Ident = C.T3Ident

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Is the view going to return more than records from 1 table only or is it going to return values from table2 and table3?

    As an example:

    table1 (col1 int, col2 name)

    table2 (col3 int, col4 datetime)

    table3 (col5 int, col6 datetime)

    are you returning records from a single table showing

    col1, col2, and either col4 or col6

    or are you returning mutliple records from each of the tables to get something like this

    col1, col2, col4

    col1, col2, col6

    col1, col2, col6

    col1, col2, col4

    col1, col2, col4

    col1, col2, col6

    Fraggle

  • Why not just have 2 statements and a UNION ALL ? The INNER JOIN ensures the sets are mutually exclusive (assuming your rule that a record can't be in both T2 and T3 holds true).

    Create View YourView

    As

    Select T1.{columns}, T2.{Columns}

    From Table1 As T1

    Inner Join Table2 As T2

    On (T1.key = T2.Key)

    UNION ALL

    Select T1.{columns}, T3.{Columns}

    From Table1 As T1

    Inner Join Table3 As T3

    On (T1.key = T3.Key)

  • A UNION statement would work. I just hate using them. Always try to find a different way of doing something as I don't think they are very efficent.

    Fraggle

  • It's not a UNION. It's a UNION ALL.

    Different query plan, no need for an expensive sort to eliminate dupes.

  • Tnx for answering all.

    Garadin, your COALESCE suggestion is interesting, never thought of using that this way, I think I never used COALESCE in my life!

    On the use of UNION(ALL): I don't think only the use of UNION will work. But maybe, maybe...

    I'm using values of 2 tables, namely from TABLE1 and EITHER values from T2 OR T3, depending on an existing relation between T1 with T2 OR T3; as I said earlier: there can be only 1 relation: if there is 1 between T1 and T2 there is none with T3, and vice verse.

    Tnx again all.

    Greetz,
    Hans Brouwer

  • It's easy enough to test with a couple of temp tables. Results are equivalent between the Coalesce & Union. So check the query plan on each and implement whichever costs less in your environment and data set.

    Create Table #Table1 (RowID int)

    Create Table #Table2 (RowID int, SomeData varchar(50))

    Create Table #Table3 (RowID int, SomeData varchar(50))

    Insert Into #Table1 Values (1)

    Insert Into #Table1 Values (2)

    Insert Into #Table1 Values (3)

    Insert Into #Table3 Values (1, 'Record 1')

    Insert Into #Table2 Values (2, 'Record 2')

    Insert Into #Table3 Values (3, 'Record 3')

    Select T1.RowID, T2.SomeData

    From #Table1 As T1

    Inner Join #Table2 As T2

    On (T1.RowID = T2.RowID)

    UNION ALL

    Select T1.RowID, T3.SomeData

    From #Table1 As T1

    Inner Join #Table3 As T3

    On (T1.RowID = T3.RowID)

    Order By T1.RowID

    Select T1.RowID, Coalesce(T2.SomeData, T3.SomeData)

    From #Table1 As T1

    Left Join #Table2 As T2

    On (T1.RowID = T2.RowID)

    Left Join #Table3 As T3

    On (T1.RowID = T3.RowID)

    Order By T1.RowID

  • I believed both my coalesce View and the UNION ALL to be viable solutions to your issue, but I was curious as to which was more efficient. I'll post what I did to test them below, but the bottom line is, they're virtually identical. The coalesce seems to be ever so slightly faster, but it's too small a difference to be sure. Note, that I did testing on a 2K5 server, using 2K8 tools, but I don't believe that affected anything.

    Test Setup:

    USE SSC

    ----------- SETUP ---------------------

    SELECT TOP 200000 IDENTITY(INT,1,1) Ident

    INTO Tally

    FROM syscolumns cross join syscolumns c -- Thanks Jeff

    SELECT Ident, 'AAAAA' + CAST(Ident AS varchar(10)) Value

    INTO A

    FROM Tally

    WHERE Ident <=100000

    SELECT Ident, 'BBBBB' + CAST(Ident AS varchar(10)) Value

    INTO B

    FROM Tally

    WHERE Ident >100000

    CREATE TABLE Data(

    DataIdentint)

    INSERT INTO Data(DataIdent) VALUES(10)

    INSERT INTO Data(DataIdent) VALUES(100)

    INSERT INTO Data(DataIdent) VALUES(1000)

    INSERT INTO Data(DataIdent) VALUES(10000)

    INSERT INTO Data(DataIdent) VALUES(100000)

    INSERT INTO Data(DataIdent) VALUES(111111)

    INSERT INTO Data(DataIdent) VALUES(112222)

    INSERT INTO Data(DataIdent) VALUES(133333)

    ---------------------------------------

    ------- CREATE VIEWS ------------------

    CREATE VIEW COA

    AS

    SELECT D.DataIdent, COALESCE(A.Value, B.Value) MyValue

    FROM Data D

    LEFT JOIN A ON D.DataIdent = A.Ident

    LEFT JOIN B ON D.DataIdent = B.Ident

    CREATE VIEW UNI

    AS

    SELECT D.DataIdent, A.Value

    FROM Data D INNER JOIN A ON D.DataIdent = A.Ident

    UNION ALL

    SELECT D.DataIdent, B.Value

    FROM Data D INNER JOIN B ON D.DataIdent = B.Ident

    ---------------------------------------

    ------ SELECT DATA --------------------

    SELECT * FROM COA

    ORDER BY DataIdent

    SELECT * FROM UNI

    ORDER BY DataIdent

    RESULTS:

    Thinking that I needed a test with more data, I used excel to generate 250 additional random numbers between 1 and 200000 and inserted those into data, and re-ran the two views.

    RESULTS:

    [Edit] Hah, PW beat me to the punch on that one, oh well.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin,

    you forgot to do FREEPROCCACHE.

    Without it you just read the same recordsets cached in memory again and again.

    No actual data accessing happens in your test.

    From logical point of view COALESCE option scans Table1 once and UNION ALL option does it twice.

    Unless Table1 is perfectly partitioned by the groups of rows related to Table2 and Table3 UNION ALL must be always slower.

    _____________
    Code for TallyGenerator

  • Sergiy:

    Thanks for the tip. I added in a DBCC FREEPROCCACHE before the views and reran the results, but still no differences worth mentioning. I'm guessing that's because my data is too "perfect" across the two tables.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • And too small.

    SQL Server reads data by pages.

    If your whole data set fits one page it will be 1 read, no matter is it 1k or 7k.

    And this page will be cached, so second read will use just that cached page.

    _____________
    Code for TallyGenerator

  • Hi The way only i can think of using UNION or UNION ALL.

    You can't have one query to place IF..ELSE conditions in Joining conditions.

    Thanks -- Vj

    -- For Microsoft related queries

    http://dotnetvj.blogspot.com

    -- For Oracle related queries

    http://oravj.blogspot.com

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply