Nested Select ... From a simulated full outer join

Question

So I have a query; a simulated FULL OUTER JOIN like this:

SELECT * FROM SupplierInfo
LEFT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId
UNION
SELECT * FROM SupplierInfo
RIGHT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId;

However this pulls out a rather large selection, and I only need 2 - 3 fields: SupplierPartNo and MajorEquipmentNo. I have tried something like this:

SELECT SupplierPartNo, MajorEquipmentNo FROM(
  SELECT * FROM SupplierInfo
  LEFT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId
  UNION
  SELECT * FROM SupplierInfo
  RIGHT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId
);

But that gives me an error Every derived table must have its own alias: SELECT SupplierPartNo, MajorEquipmentNo FROM( SELECT....

How can I do a SELECT from a previous selection?

score 1 · Accepted Answer · answered Mar 21 '14 at 05:33

If you want those two particular fields from your dataset, you can directly query like this:

SELECT SupplierInfo.SupplierPartNo, MajorEquipment.MajorEquipmentNo 
FROM SupplierInfo
LEFT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId
UNION
SELECT SupplierInfo.SupplierPartNo, MajorEquipment.MajorEquipmentNo 
FROM SupplierInfo
RIGHT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId

If you still want to go with your derived table approach(which I will suggest not to because of the performance) then you can query it like:

SELECT a.SupplierPartNo, a.MajorEquipmentNo FROM (
SELECT * FROM SupplierInfo
LEFT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId
UNION
SELECT * FROM SupplierInfo
RIGHT JOIN MajorEquipment ON SupplierInfo.SupplierId = MajorEquipment.MEId
) a

Nested Select ... From a simulated full outer join

1 Answers1