How to write Sub Queries, Co-Related Sub Queries using Nested Select statements.

Problem: How to write nested select statements.

Solution: Subquery

Subquery is a child select statement inside a parent select statement.

Sub queries can be helpful in a way to reduce the code, make it compact query.

Window functions, computing scalar functions and group by can be released using SQL Sub Queries.

Co-related sub queries are queries which reference a outer table column inside the sub select to get related results.

Example:

In this article I am going to show you how we can add a custom error to system messages.

Example 1:

In this example i would like to create a derived table using sub query where inner query is called as a table A. 

Sub queries when specified inside a from clause require column names and also alias the sub query with a name.

If failed to do so this would cause syntax errors.

SELECT A.*

FROM (

                SELECT 1 AS col1 -- alias the column name if using a cast or function output.

                ) a-- name the subquery

Example 2:

Let’s try to extract the list of steps in a SQL server job to count the steps inside a job, to get the total steps for each step I am going to write a nested select query (sub query) to relate to the job_id.

This type of sub-queries are also called co-related sub queries.

USE msdb;  

GO 

SELECT 'Step ' + CAST(Js.step_id AS VARCHAR(3)) + ' of ' +

                (

                SELECT CAST(COUNT(*) AS VARCHAR(5))

                 FROM dbo.sysjobsteps

                 WHERE job_id = sj.job_id           

                ) AS StepofSteps,

                                JS.step_name

FROM dbo.sysjobs sj

INNER JOIN

dbo.sysjobsteps JS

ON sj.job_id =js.job_id

                WHERE sj.name='Purge History'

OUTPUT:

StepofSteps       step_name

Step 1 of 2           Deleting history records

Step 2 of 2           Purge Old Records