Nested Join Alternative

Question

Post reply

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: 110192 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, 1 month ago by Sergiy.

_____________
Code for TallyGenerator

Jeffrey Williams SSC Guru Points: 89773 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Jackie Lowery SSCommitted Points: 1877 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: 89773 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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