Old style Join conversion

Question

Old style Join conversion

Viewing 9 posts - 16 through 23 (of 23 total)

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

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 1

The reason it returned the results it did was because (again using your first example) the optimizer applied the "WHERE t2.data=2" and "WHERE t1.data=5" before doing the join. As in my previous message the syntax below should give the same results without an ISNULL in the WHERE clause.

SELECT t2.data,t2.id, t1.data

from (SELECT id, data FROM table2 WHERE data = 2) t2 left outer join

(SELECT id, data FROM table1 WHERE data = 5) t1

on t1.id=t2.id

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

I do not think that returns the requested results...

DECLARE @Table1 TABLE( id1 integer,

data1 integer)

DECLARE @Table2 TABLE( id2 integer,

data2 integer)

INSERT INTO @Table1

SELECT 1,1 UNION ALL

SELECT 2,2 UNION ALL

SELECT 3,1 UNION ALL

SELECT 4,3 UNION ALL

SELECT 5,2 UNION ALL

SELECT 6,2 UNION ALL

SELECT 7,3 UNION ALL

SELECT 8,2 UNION ALL

SELECT 9,3

INSERT INTO @Table2

SELECT 2,10 UNION ALL

SELECT 2,20 UNION ALL

SELECT 2,30 UNION ALL

SELECT 3,20 UNION ALL

SELECT 4,30 UNION ALL

SELECT 4,20 UNION ALL

SELECT 8,30 UNION ALL

SELECT 8,20

SELECT t1.id1, t1.data1, t2.data2

FROM @Table1 t1, @Table2 t2

WHERE t1.id1 *= t2.id2

AND t1.data1 = 2

AND t2.data2 = 20

SELECT t1.id1, t1.data1, t2.data2

FROM @Table1 t1

LEFT JOIN @Table2 t2 ON( t1.id1 = t2.id2)

WHERE t1.data1 = 2

AND ISNULL( t2.data2, 20) = 20

SELECT t1.id1, t1.data1, t1.data1

FROM( SELECT id1, data1 FROM @Table1 WHERE data1 = 2) t1

LEFT OUTER JOIN( SELECT id2, data2 FROM @Table2 WHERE data2 = 20) t2

ON( t1.id1 = t2.id2)

I wasn't born stupid - I had to study.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

Doesn't t1.id1 *= t2.id2 translate to a RIGHT OUTER JOIN ?

t2.id2 =* t1.id1 (reversing the condition columns and outer join), should translate to a LEFT OUTER JOIN.

For clarity, I have renamed table2 to Headers and table1 to Details.

Your requirement is then to return all rows from HEADERS and matching rows from DETAILS but when there are no matching rows in DETAILS, return nulls of all columns of DETAILS.

Select Details.id1

, Details.data1

, Headers.data2

From Headers -- was Table2

LEFT OUTER JOIN

Details -- was Table1

On Headers.id2 = Details.id1

and Details.data1 = 2

WHERE Headers.data2 = 20

SQL = Scarcely Qualifies as a Language

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Just to clarify things a bit (or at least attempting to do so )

'*' denotes 'all' or the 'outer table'. Thus:

'*=' is a LEFT outer join (star is on the left side)

'=* is a RIGHT outer join (star is on the right side)

Apart from the syntax being deprecated, there is every reason in the world (as seen here as well) to convert away from this legacy syntax into ANSI syntax. The most compelling reason is that '*=' and '=*' simply does not work as expected.

Apart from this, there are also several things that the old syntax just won't let you do, but is very easy with ANSI (eg stacking several outer joins on eachother etc)

/Kenneth

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Author just did not follow his own logic.

On the first page he wrote:

I expect to see the following when I filter the data such that T2.Data=2 and T1.Data=5

T2.Data,T2.ID,T1.Data

2,1,5

2,2,5

2,3,NULL

And on this page we can read:

The Final Result works like a charm.

Select t1.id1,t1.data1, t2.data2

From Table1 t1 Left Outer Join Table2 t2 on t1.id1 = t2.id2

Where t1.data1=2 and isnull(t2.data2,20)=20

Last query defnetely cannot return expected result.

And bad thing about this query is using of ISNULL(t2.data,20) = 20 will cause table t2 scan - function will not allow to use index or statistics.

Right version must be like this:

Select t1.id1,t1.data1, t2.data2

From Table1 t1

Left Outer Join Table2 t2 on t1.id1=t2.id2 and t2.data2=20

Where t1.data1=2

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Last query defnetely cannot return expected result.

It does return the expected results...

Agreed about the table scan and use of index or statistics - yours is a better query.

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Originally t1.Data was expected NULL in some returned rows.

But in the last query t1.Data cannot be NULL, t2.Data takes its place.

This changes direction of the join.

_____________
Code for TallyGenerator

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 8

Farrel,

You have a typo in your example of my query. Try the query below. It will give the same results as the *= and your query.

SELECT t1.id1, t1.data1, t2.data2

FROM( SELECT id1, data1 FROM @Table1 WHERE data1 = 2) t1

LEFT OUTER JOIN( SELECT id2, data2 FROM @Table2 WHERE data2 = 20) t2 ON( t1.id1 = t2.id2)

If you look at the execution plan for my query and the original you will see they are equivalent.

Mark

Farrell Keough SSCoach Points: 17414 More actions · Answer 9

Yes I did! Sorry. It works...

I wasn't born stupid - I had to study.