SubQuery.md

SQL Server Subquery

• Sales orders of the customers who locate in New York
• Nesting subquery
• Subquery in place of an expression
• IN operator
• ANY operator
• ALL operator
• Customers who bought products in 2017
• Customers who did not buy any products in 2017
• FROM clause

sales orders of the customers who locate in New York:

When subquery SELECTs a single column (or a tuple), we may return it AsCollection() or AsSingle(). The correct type propagates out. Thus the return type of SubQuery() method above is ICollection<int?>.

For clarity let's assign the result of the subquery to a local variable (of course the produced SQL is same in either case):

Nesting subquery

SQL Server subquery is used in place of an expression

SQL Server subquery is used with IN operator

In fact we already covered IN operator in the very first example above.

SQL Server subquery is used with ANY operator

SQL Server subquery is used with ALL operator

Just replace ANY with ALL above and run!

customers who bought products in 2017:

customers who did not buy any products in 2017:

ELINQ maps SQL's EXISTS operator to Any():

SQL Server subquery in the FROM clause

Since ELINQ supports variables, this very powerfull feature does not lead to complex nesting.

---

< BACK | HOME