Union is not only useful as a pure Select in itself. But there are time when I’ve wanted to have a set of Unions and then use that as a table, and join it to others.

Select

Sub.Column1

,Sub.Column2

,Sub.Column3

,Sub.Column4

From

(

Select

P.Field1

P.Field2

P.Field3

P.Field4

From

PartP P

Where

P.IsDeleted <> 1

Union All

Select

Q.Field1

Q.Field2

Q.Field3

Q.Field4

From

PartQ Q

Where

Q.IsDeleted <> 1

Union All

Select

Q.Field1

Q.Field2

Q.Field3

Q.Field4

From

PartQ Q

Where

Q.IsDeleted <> 1

) As Sub

Left Outer Join TableX X On Sub.Field1 = X.Field1

Left Outer Join TableY Y On Sub.Field1 = Y.Field1

Where

Sub.Field2 In (‘Blue’, ‘Green’, ‘Red’)

And Sub.Field3 In (7, 12)

Order By

Sub.Field1

Advertisements