Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries

Question

Post reply

Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

I didn't realize you could use a subquery in all those places such as the HAVING clause.

himalaya.dua SSC Rookie Points: 27 More actions · Answer 2

Using the subquery in Function call

it could be simply--

SELECT SalesOrderID

,OrderDate

,DATEDIFF(DAY, OrderDate, (SELECT MAX(OrderDate)

FROM Sales.SalesOrderHeader)

) AS DayDiff

FROM Sales.SalesOrderHeader

drew.allen SSC Guru Points: 76776 More actions · Answer 3

The answer to question 1 should be "None of the above." By definition, a correlated subquery is a subquery. Since none of the answers applies to ALL subqueries, there is no valid answer to question 1.

You also missed another case where subqueries can return multiple rows with a single column: on the right side of a conditional expression when used with the keyword SOME, ANY, or ALL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Bangaaram SSC-Addicted Points: 465 More actions · Answer 4

Could someone shed more light on the following code? What kind of scenario can this be used in a real world?

SELECT TOP (SELECT TOP 1 OrderQty

FROM [Sales].[SalesOrderDetail]

ORDER BY ModifiedDate) *

FROM [Sales].[SalesOrderDetail]

WHERE ProductID = 716;

-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:

Bangaaram SSC-Addicted Points: 465 More actions · Answer 5

Upon my execution of Listing 12, I got the following results. So, I am not sure if I should agree with of using a JOIN is same as Subquery.

(1076 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 3310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 111 ms.
(1076 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 3310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 80 ms.

PS: I am using SQL Server 2014.

-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:

jetboy2k Mr or Mrs. 500 Points: 529 More actions · Answer 6

What other Stairways should one take prior to this one? There's a reference to the Stairway to T-SQL DML Stairway, which I've finished, but in this article, there is also this statement:

Here are some other things to consider when using a subquery:

ntext, text and image data types are not allowed to be returned from a subqueries

The ORDER BY clause cannot be used in a subquery unless the TOP operator is used

Views that use a subquery can’t be updated

COMPUTE and INTO clauses cannot be used in a subquery

However, I've done a search on all of the Stairway to T-SQL DML articles, and there is no mention or example of the COMPUTE clause. Is there another Stairway to read prior to this one? Or is there a basic set of T-SQL commands we should know prior to starting this one?

Thanks.

jetboy2k Mr or Mrs. 500 Points: 529 More actions · Answer 7

@DataJoe - I don't see anywhere that the author says what the number returned to the TOP function actually is. The example just shows that some number will be returned and used as the argument to TOP, not what that number is.

Where does it say that the number is 3?

Katerine459 SSC Eights! Points: 945 More actions · Answer 8

jetboy2k (9/5/2016)
What other Stairways should one take prior to this one? There's a reference to the Stairway to T-SQL DML Stairway, which I've finished, but in this article, there is also this statement:
Here are some other things to consider when using a subquery:
ntext, text and image data types are not allowed to be returned from a subqueries
The ORDER BY clause cannot be used in a subquery unless the TOP operator is used
Views that use a subquery can’t be updated
COMPUTE and INTO clauses cannot be used in a subquery
However, I've done a search on all of the Stairway to T-SQL DML articles, and there is no mention or example of the COMPUTE clause. Is there another Stairway to read prior to this one? Or is there a basic set of T-SQL commands we should know prior to starting this one?

I'm guessing that he's just listing all clauses that cannot be used in a subquery. Since COMPUTE is a clause that exists, and cannot be used in a subquery, it's included in the list. I don't think he's trying to imply that it's a clause that you should already know all about. 🙂