SQL – Inline Queries

Most of the developers are very familiar with inline queries. Inline queries are basically known as sub queries or Inner Select statements which are always used inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They can be used anywhere in SQL scripts to encounter the conditional expressions. The SQL statement containing an Inline query is also known an outer query or outer select.

How can Inline Queries helpful?

Most of the T-SQL statements which include inline queries can be alternatively formulated as join to encounter the conditional expressions. Other questions can be posed only with inline queries. In T-SQL, there is usually no performance difference between a statement that includes an inline query and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance.

Components of an Inline Query

An inline query has the same features as a normal SQL query. They only persist in the SQL statements and could have the following components –

1. A regular SELECT query including regular select list components from the main table.
2. A regular FROM clause including one or more table, function or view names.
3. An optional WHERE clause to encounter the conditional expressions.
4. An optional GROUP BY clause if your query contains aggregation functions.
5. An optional HAVING clause if your query contains aggregation functions.

Points to be remember 
The SELECT query of an Inline query is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified. There are three basic types of inline queries-

1. Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
2. Are introduced with an unmodified comparison operator and must return a single value.
3. Are existence tests introduced with EXISTS.

To understand their functionality, we will create a table variable to work as the base table for the inline statements as given below- 

---- declare OrderMaster table variable body DECLARE @OrderMaster TABLE ( OrdId INT IDENTITY(100,1), OrdDate VARCHAR(30), CustName VARCHAR(20) ) ---- declare OrderDetails table variable body DECLARE @OrderDetails TABLE ( OrdId INT, ItemId VARCHAR(30), ItemName VARCHAR(20), SellingPrice Decimal(12,4) ) ---- Insert Values INSERT INTO @OrderMaster (OrdDate,CustName) Values (Getdate(),'Ryan Arjun'),(Getdate(),'Bill Trade'), (Getdate(),'Rosy White') ---- Pull Order Data SELECT * FROM @OrderMaster OrdId OrdDate CustName 100 May 28 2016 10:08AM Ryan Arjun 101 May 28 2016 10:08AM Bill Trade 102 May 28 2016 10:08AM Rosy White ----Fill Order Details Insert Into @OrderDetails (OrdId, ItemId, ItemName, SellingPrice) Values (100, 201,'Apple',135.78),(100, 202,'Mango',235.78) ,(101, 203,'Banana',124.50),(101, 204,'Orange',321.15) ,(102, 205,'Banana',124.50),(102, 204,'Orange',321.15),(102, 201,'Apple',135.78) --- Pull Order Details select * from @OrderDetails OrdId ItemId ItemName SellingPrice 100 201 Apple 135.78 100 202 Mango 235.78 101 203 Banana 124.5 101 204 Orange 321.15 102 205 Banana 124.5 102 204 Orange 321.15 102 201 Apple 135.78

Now, we have the tables and want to pull the order and customer wise total sales.

Pull Single Value

There are many ways to do this but we are using inline query to accomplish this task. To pull the single value, we are using inline query within the main SQL statement as given below-

---- Use inline query in the select statement select OrdId, CustName, ---- Inline query for single value [Price] =(Select   [Price]=sum(SellingPrice) from @OrderDetails where OrdId=  Om.OrdId ) from @OrderMaster OM OrdId CustName Price 100 Ryan Arjun 371.56 101 Bill Trade 445.65 102 Rosy White 581.43

Pull Multiple Values with Group By

If we want to pull more than one column then inline query should work as join with the main query as given below:

---- Inline Query as Join SELECT Om.OrdId, Om.CustName, OD.[Item-Qty], OD.Price FROM @OrderMaster OM JOIN ( SELECT  OrdId, [Item-Qty]=COUNT(ItemId), [Price]=SUM(SellingPrice) FROM @OrderDetails GROUP BY ORDID )OD ON OM.ORDID=OD.ORDID OrdId CustName Item-Qty Price 100 Ryan Arjun 2 371.56 101 Bill Trade 2 445.65 102 Rosy White 3 581.43

Pull Value with Where Clause and Group By

If we want to pull more than one column based on some conditional expression then inline query should work as join with the main query as given below:

---- Inline Query as Join SELECT Om.OrdId, Om.CustName, OD.[Item-Qty], OD.Price FROM @OrderMaster OM JOIN (SELECT  OrdId, [Item-Qty]=COUNT(ItemId), [Price]=SUM(SellingPrice) FROM @OrderDetails ---- Conditional Expression WHERE ItemName='Apple' GROUP BY ORDID )OD ON OM.ORDID=OD.ORDID OrdId CustName Item-Qty Price 100 Ryan Arjun 1 135.78 102 Rosy White 1 135.78

Conclusion

It’s very beneficial concept in SQL and we can use them inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They are easily applicable in the function and stored procedure. These features are important in some Transact-SQL statements; the inline-query can be evaluated as if it were an independent query. Conceptually, the inline-query results are substituted into the outer query.