Can a select statement include nested result sets?

Question

Given the following tables and sample data:

create table Orders ( ID int not null primary key, Customer nvarchar(100) not null); create table OrderItems ( ID int not null primary key, OrderID int not null foreign key references Orders(ID), Product nvarchar(100) not null); insert into Orders values (1, 'John'); insert into Orders values (2, 'James'); insert into OrderItems values (1, 1, 'Guitar'); insert into OrderItems values (2, 1, 'Bass'); insert into OrderItems values (3, 2, 'Guitar'); insert into OrderItems values (4, 2, 'Drums'); 

I would like to know if I can query the parent table Orders , and also get the child table OrderItems as a nested result in the parent result. Something like that:

 | ORDER.ID | ORDER.CUSTOMER | ORDER.ORDERITEMS | ------------------------------------------------------------------ | | | ORDERITEMS.ID | ORDERITEMS.PRODUCT | | | |------------------------------------- | 1 | John | 1 | Guitar | | | | 2 | Bass | | 2 | James | 3 | Guitar | | | | 4 | Drums | 

This question, which I mean (which does not work in SQL Server), looks something like this:

 -- doesn't work, but shows the intent to have nested result sets select o.OrderID [Order.ID], o.Customer [Order.Customer], (select oi.ID [OrderItems.ID], oi.Product [OrderItems.Product] from OrderItems oi where o.ID = oi.OrderID ) [Order.OrderItems] from Orders o; 

This is just a conceptual question as I try to think of ways to get related data with minimal duplication (as opposed to what happens with join , for example).

SQL feed here .

UPDATE

I learned from this answer that Oracle supports it with cursor expressions:

 select o.*, cursor(select oi.* from OrderItems oi where o.ID = oi.OrderID) as OrderItems from Orders o; 

Answer 1

No. This is really impossible.

SQL Server does not support nested relationships and NF²

Although you can use the FOR XML PATH to return it hierarchically.

 SELECT ID AS [@ID], Customer AS [@Customer], (SELECT ID AS [@ID], OrderID AS [@OrderID], Product AS [@Product] FROM OrderItems WHERE OrderItems.OrderID = o.ID FOR XML PATH('OrderItems'), TYPE) FROM Orders o FOR XML PATH('Order'), ROOT('Orders') 

Returns

  <Orders> <Order ID="1" Customer="John"> <OrderItems ID="1" OrderID="1" Product="Guitar" /> <OrderItems ID="2" OrderID="1" Product="Bass" /> </Order> <Order ID="2" Customer="James"> <OrderItems ID="3" OrderID="2" Product="Guitar" /> <OrderItems ID="4" OrderID="2" Product="Drums" /> </Order> </Orders> 

This does not repeat parent Orders