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
-
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.
-
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.
-
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
-- For Oracle related queries
Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply