Nested Join Alternative

Question

Nested Join Alternative

Viewing 4 posts - 16 through 18 (of 18 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

It’s funny nobody suggested the most obvious choice in such situation - RIGHT JOIN:

Select s.Shipment_No
     , p.pkg_no
     , p.ord_no
From wsPKG   p
Inner Join wsPKGLin l On p.PKG_ID = l.PKG_ID 
RIGHT OUTER JOIN wsPKGShipment s ON S.Shipment_No = p.Shipment_No
 Where s.Shipment_No = 169200;

Or, maybe, it’s just sad…

This reply was modified 3 years, 3 months ago by Sergiy.

_____________
Code for TallyGenerator

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Sergiy wrote:

It’s funny nobody suggested the most obvious choice in such situation - RIGHT JOIN:
Select s.Shipment_No
     , p.pkg_no
     , p.ord_no
From wsPKG   p
Inner Join wsPKGLin l On p.PKG_ID = l.PKG_ID 
RIGHT OUTER JOIN wsPKGShipment s ON S.Shipment_No = p.Shipment_No
 Where s.Shipment_No = 169200;
Or, maybe, it’s just sad…

I wouldn't say sad - for most of us RIGHT OUTER JOIN just hurts so we tend to avoid it 🙂

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 3

Sergiy wrote:

It’s funny nobody suggested the most obvious choice in such situation - RIGHT JOIN:
Select s.Shipment_No
     , p.pkg_no
     , p.ord_no
From wsPKG   p
Inner Join wsPKGLin l On p.PKG_ID = l.PKG_ID 
RIGHT OUTER JOIN wsPKGShipment s ON S.Shipment_No = p.Shipment_No
 Where s.Shipment_No = 169200;
Or, maybe, it’s just sad…

Why does Inner Join -> Right Join work , but not Left Join -> Inner Join? Because of the order of the joins?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Jackie Lowery wrote:

Sergiy wrote:
It’s funny nobody suggested the most obvious choice in such situation - RIGHT JOIN:
Select s.Shipment_No
     , p.pkg_no
     , p.ord_no
From wsPKG   p
Inner Join wsPKGLin l On p.PKG_ID = l.PKG_ID 
RIGHT OUTER JOIN wsPKGShipment s ON S.Shipment_No = p.Shipment_No
 Where s.Shipment_No = 169200;
Or, maybe, it’s just sad…
Why does Inner Join -> Right Join work , but not Left Join -> Inner Join? Because of the order of the joins?

The left join -> inner join works...it just has to be constructed differently. The left join starts with the preserved table and then the unpreserved {table|expression}, where the right join starts with the unpreserved {table|expression} and then the preserved table.

So - the construct for left join would be:

From preservedTable
Left Join {unpreservedTable|Expression} On {relationship}

For this case:

From preservedTable pt
Left Join (table1 t1 Inner Join Table2 t2 On t2.key = t1.key) On t1.key = pt.key

For the right join:

From {unpreservedTable|Expression}
Right Join preservedTable On {relationship}

So we can then get to this:

From (table1 t1 Inner Join table2 t2 On t2.key = t1.key)
Right Join preservedTable pt On pt.key = t1.key

In either form - we can then remove the parentheses as they are not really needed. I would probably keep them for the left outer join - just to make it clear.

With that said - I tend to avoid this type of complication by moving the unpreserved portion to a CTE, derived table or outer apply. In most cases, SQL Server will generate the same execution plan for the CTE or derived table vs the join - but may not depending on the complexity of the query.

Also note - anywhere you have a 'table' it can be an 'expression' which is either another select statement or joined tables.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs