We help IT Professionals succeed at work.

OUTER JOIN of 2 separate INNER JOINS

Trying to produce OUTER JOIN of 2 separate INNER JOINS

SELECT DL.ID FROM [dbo].[NDL_Download] [DL] (NOLOCK)  
(
  INNER JOIN [dbo].[DMX_PS] [PS] (NOLOCK) ON DL.ID = [PS].DlID 
-- Need Outer Join of these 2 inner joins between PS and PF on DL
  INNER JOIN [dbo].[DMX_PF] [PF] (NOLOCK) ON DL.ID = [PF].DlID 
)
INNER JOIN [dbo].[DMX_OS] [OS] (NOLOCK) ON DL.ID = [OS].DlID 
INNER JOIN [dbo].[DMX_LG] [LG] (NOLOCK) ON DL.ID = [LG].DlID 
WHERE PS.MxID = 80 AND PF.MxID = 593 AND OS.MxID = 8 AND LG.MxID = 1

Open in new window


Stated logically as shown in PSUEDO example below

SELECT DL.ID FROM [dbo].[NDL_Download] [DL] (NOLOCK)  
(
  INNER JOIN [dbo].[DMX_PS] [PS] (NOLOCK) ON DL.ID = [PS].DlID 
OR
  INNER JOIN [dbo].[DMX_PF] [PF] (NOLOCK) ON DL.ID = [PF].DlID 
)
INNER JOIN [dbo].[DMX_OS] [OS] (NOLOCK) ON DL.ID = [OS].DlID 
INNER JOIN [dbo].[DMX_LG] [LG] (NOLOCK) ON DL.ID = [LG].DlID 
WHERE PS.MxID = 80 AND PF.MxID = 593 AND OS.MxID = 8 AND LG.MxID = 1

Open in new window


Any help would be much appreciated?
Comment
Watch Question

Author

Commented:
This seems to work, but concerned about throughput/performance:
(SELECT DISTINCT DL.ID FROM [dbo].[DL] [DL] (NOLOCK)  
INNER JOIN [dbo].[DMX_PS] [DMPS] (NOLOCK) ON DL.ID = [DMPS].DlID
INNER JOIN [dbo].[DMX_OS] [DMOS] (NOLOCK) ON DL.ID = [DMOS].DlID 
INNER JOIN [dbo].[DMX_LG] [DMLG] (NOLOCK) ON DL.ID = [DMLG].DlID 
WHERE DMPS.MxID = 80 AND DMOS.MxID = 8 AND DMLG.MxID = 1
UNION 
SELECT DISTINCT DL.ID FROM [dbo].[DL] [DL] (NOLOCK) 
INNER JOIN [dbo].[DMX_PF] [DMPF] (NOLOCK) ON DL.ID = [DMPF].DlID 
INNER JOIN [dbo].[DMX_OS] [DMOS] (NOLOCK) ON DL.ID = [DMOS].DlID 
INNER JOIN [dbo].[DMX_LG] [DMLG] (NOLOCK) ON DL.ID = [DMLG].DlID 
WHERE 
DMPF.MxID = 593 AND DMOS.MxID = 8 AND DMLG.MxID = 1)

Open in new window

Commented:
Hi theblanc0,

How about this:

SELECT DL.ID 
FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
	INNER JOIN [dbo].[DMX_OS] [OS] (NOLOCK) ON DL.ID = [OS].DlID 
	INNER JOIN [dbo].[DMX_LG] [LG] (NOLOCK) ON DL.ID = [LG].DlID 
	LEFT OUTER JOIN [dbo].[DMX_PS] [PS] (NOLOCK) ON DL.ID = [PS].DlID AND PS.MxID = 80
	LEFT OUTER JOIN [dbo].[DMX_PF] [PF] (NOLOCK) ON DL.ID = [PF].DlID AND PF.MxID = 593
WHERE OS.MxID = 8 AND LG.MxID = 1 AND ([PS].DlID IS NOT NULL OR [PF].DlID IS NOT NULL)

Open in new window

you can do joins on queries, giving the queries names is the key to that

e.g.

select * from (select * from master) as xx
left  join (select * from pay)  as yy on xx.id = yy.id

Author

Commented:
Thanks Rimvis,

 The suggestion returns a null set, I think because there is an implicit AND between each of the JOINS, irrelevant as to inner, outer, self.......

 So, I think your example can be PSUEDO interpreted as:
 
SELECT DL.ID 
FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
	INNER JOIN [dbo].[DMX_OS] [OS] (NOLOCK) ON DL.ID = [OS].DlID 
-- AND
	INNER JOIN [dbo].[DMX_LG] [LG] (NOLOCK) ON DL.ID = [LG].DlID 
-- AND
	LEFT OUTER JOIN [dbo].[DMX_PS] [PS] (NOLOCK) ON DL.ID = [PS].DlID AND PS.MxID = 80
-- AND
	LEFT OUTER JOIN [dbo].[DMX_PF] [PF] (NOLOCK) ON DL.ID = [PF].DlID AND PF.MxID = 593
WHERE OS.MxID = 8 AND LG.MxID = 1 AND ([PS].DlID IS NOT NULL OR [PF].DlID IS NOT NULL)

Open in new window


The objective is to impose a OR, rather than AND between the PS & PF inner joins. That’s essentially what the
UNION in the example shared does. The gotcha is this is more expensive because it is essentially executing
the AND’d inner joins twice, once for each side of the UNION.

Author

Commented:
Thanks deighton,

 Any chance you could provide a contextual example that reflects the specific query shared?

 Was hoping to avoid nested selects, but may not be avoidable, the UNION works, but again
this is essentially forcing 2 execution paths, similarly a nested select would force multiple execution
paths.

 I will compare execution plans & times for UNIONS and nested selects, but expectation is nested
selects are rather expensive.

 This is essentially both a join question as well a query optimization question.

Commented:
Hi theblanc0,
well actually OUTER JOIN is similar to "OR", so my query should return desired result:
[DL] AND [OS] AND [LG] AND ([PS] OR [PF])

Open in new window

Could you check it again? For a test, try to remove other tables from SELECT:

SELECT *
FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
	LEFT OUTER JOIN [dbo].[DMX_PS] [PS] (NOLOCK) ON DL.ID = [PS].DlID AND PS.MxID = 80
	LEFT OUTER JOIN [dbo].[DMX_PF] [PF] (NOLOCK) ON DL.ID = [PF].DlID AND PF.MxID = 593
WHERE ([PS].DlID IS NOT NULL OR [PF].DlID IS NOT NULL)

Open in new window

Author

Commented:
Rimivis,

 Yes, the PS & PF outer join is the same as the UNION of the PS & PF inner joins.

(SELECT DISTINCT DL.ID FROM [dbo].[NDL_Download] [DL] (NOLOCK)  
INNER JOIN [dbo].[NDL_DownloadMatrix_PS] [DMPS] (NOLOCK) ON DL.ID = [DMPS].DownloadID
WHERE DMPS.MatrixValueID = 80 
UNION 
SELECT DISTINCT DL.ID FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
INNER JOIN [dbo].[NDL_DownloadMatrix_PF] [DMPF] (NOLOCK) ON DL.ID = [DMPF].DownloadID 
WHERE 
DMPF.MatrixValueID = 593)
EXCEPT
SELECT [DL].ID
FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
	LEFT OUTER JOIN [dbo].[NDL_DownloadMatrix_PS] [PS] (NOLOCK) ON [DL].ID = [PS].DownloadID AND PS.MatrixValueID = 80
	LEFT OUTER JOIN [dbo].[NDL_DownloadMatrix_PF] [PF] (NOLOCK) ON [DL].ID = [PF].DownloadID AND PF.MatrixValueID = 593
WHERE ([PS].DownloadID IS NOT NULL OR [PF].DownloadID IS NOT NULL)

Open in new window

Not shown but both EXCEPT directions are compared. innerjoin EXCEPT outerjoin and outerjoin EXCEPT innerjoin

 The challenge is the implicit AND with the LG & OS inner joins that is also included in the execution.

 The objective is to emulate the UNION of the sets
  (PS & OS & LG) on DL
UNION
  (PF & OS & LG) on DL

Author

Commented:
Rimivis,

 Went back and double checked the full query and seems to be working with the inner joins included.

 I must have missed something earlier.

Let me do a little more validations and will accept the outer join solution.

Thanks so much for your help.

Forward Validation
SELECT [DL].ID
FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
    LEFT OUTER JOIN [dbo].[DMXPS] [PS] (NOLOCK) ON [DL].ID = [PS].DlID AND PS.MxVID = 80
    LEFT OUTER JOIN [dbo].[DMXPF] [PF] (NOLOCK) ON [DL].ID = [PF].DlID AND PF.MxVID = 593
    INNER JOIN [dbo].[DMXOS] [DMOS] (NOLOCK) ON DL.ID = [DMOS].DlID 
    INNER JOIN [dbo].[DMXLG] [DMLG] (NOLOCK) ON DL.ID = [DMLG].DlID 
WHERE ([PS].DlID IS NOT NULL OR [PF].DlID IS NOT NULL) AND DMOS.MxVID = 8 AND DMLG.MxVID = 1
EXCEPT
SELECT DISTINCT DM1.DlID as ID
FROM DMX DM1 (NOLOCK) WHERE 1 = 1 AND	
(EXISTS(SELECT DM2.* FROM DMX DM2 (NOLOCK) 
WHERE DM2.MxVID = 80 AND DM2.MxID = 1 AND DM2.DlID = DM1.DlID) 
OR EXISTS(SELECT DM3.* FROM DMX DM3 (NOLOCK) 
WHERE DM3.MxVID = 593 AND DM3.MxID = 4 AND DM3.DlID = DM1.DlID)) AND	
EXISTS(SELECT DM4.* FROM DMX DM4 (NOLOCK) 
WHERE DM4.MxVID = 8 AND DM4.MxID = 2 AND DM4.DlID = DM1.DlID) AND	
EXISTS(SELECT DM5.* FROM DMX DM5 (NOLOCK) 
WHERE DM5.MxVID = 1 AND DM5.MxID = 3 AND DM5.DlID = DM1.DlID)

Open in new window


Reverse validation
SELECT DISTINCT DM1.DlID as ID
FROM DMX DM1 (NOLOCK) WHERE 1 = 1 AND	
(EXISTS(SELECT DM2.* FROM DMX DM2 (NOLOCK) 
WHERE DM2.MxVID = 80 AND DM2.MxID = 1 AND DM2.DlID = DM1.DlID) 
OR EXISTS(SELECT DM3.* FROM DMX DM3 (NOLOCK) 
WHERE DM3.MxVID = 593 AND DM3.MxID = 4 AND DM3.DlID = DM1.DlID)) AND	
EXISTS(SELECT DM4.* FROM DMX DM4 (NOLOCK) 
WHERE DM4.MxVID = 8 AND DM4.MxID = 2 AND DM4.DlID = DM1.DlID) AND	
EXISTS(SELECT DM5.* FROM DMX DM5 (NOLOCK) 
WHERE DM5.MxVID = 1 AND DM5.MxID = 3 AND DM5.DlID = DM1.DlID)
EXCEPT
SELECT [DL].ID
FROM [dbo].[NDL_Download] [DL] (NOLOCK) 
    LEFT OUTER JOIN [dbo].[DMXPS] [PS] (NOLOCK) ON [DL].ID = [PS].DlID AND PS.MxVID = 80
    LEFT OUTER JOIN [dbo].[DMXPF] [PF] (NOLOCK) ON [DL].ID = [PF].DlID AND PF.MxVID = 593
    INNER JOIN [dbo].[DMXOS] [DMOS] (NOLOCK) ON DL.ID = [DMOS].DlID 
    INNER JOIN [dbo].[DMXLG] [DMLG] (NOLOCK) ON DL.ID = [DMLG].DlID 
WHERE ([PS].DlID IS NOT NULL OR [PF].DlID IS NOT NULL) AND DMOS.MxVID = 8 AND DMLG.MxVID = 1

Open in new window