SqlTutorial-1 : Lesson-7 : Class-1
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Sql-SubQueries: Nested and Co-related

Microsoft SQL Server Training Online Learning Classes Sql Sub Queries Nested Co-related

A subquery is a Sub-SELECT nested inside another query. // OR //
Defining a SELECT query within another query is called nested query.
INDEX
  1. Introduction
    1. What are Sub-Queries
    2. Restrictions
  2. Nested Sub-Queries
    1. Sub-Queries in SELECT(Inline-Views)
    2. Sub-Queires in FROM-clause (Inline-Views)
    3. Sub-Queries in WHERE-Clause
  3. Co-related Sub-Queries
    1. Cor-related Vs Nested
    2. Finding N-Max examples


1. Introduction

1.1: What is Sub-Query?

A Subquery also called INNER QUERY / INNER SELECT / SUB-SELECT.
  1. The Parent-query i.e, outer-query can be any DML statement but child-query(sub-query) must be only SELECT-statement. For example, an UPDATE statement or INSERT-statement or DELETE-statement can contain SELECT-statement, but reverse is not possible. 
  2. You can write sub-query in SELECT-clause, FROM-clause and WHERE clause. 
  3. Defining sub-query followed by SELECT and FROM clauses is said to be in-line view. Hence, there are 3 types of sub-queries.
    • In-line view queries (Derived Table) 
    • Normal Sub-queries 
    • Co-related sub-queries.
  4. A sub-query is subject to the following restrictions:
    • A view created by using sub-query cannot be updated. 
    • If sub-query returns single value, then you can compare it with comparison operator. Otherwise (returning multiple values) use ANY, SOME, ALL, IN.
    • If sub-query is used with comparison operator, it must include only one column name/expression(except that EXISTS and IN operate on SELECT *) 
    • The COMPUTE, ORDER BY and INTO clauses cannot be specified in sub-query. 
    • The ntext, text, and image type columns cannot be included into the subqueries.

1.2: Restrictions

A subquery have following restriction:
  1. A Sub-Query cannot use DISTINCT key if it includes GROUP-BY
  2. A Sub-Query cannot use COMPUTE and INTO clauses
  3. A Sub-query can use ORDER-BY if it also have TOP()
  4. A Sub-query generated view is cannot be Updated.
  5. A Sub-query cannot includes the columns of type: NTEXT, TEXT, and IMAGE.
  6. A Sub-query should be compared with using:
    1. ANY, SOME, or ALL
    2. EXISTS or NOT EXISTS

2.Nested Sub-Queries


2.1. Sub-queries in SELECT clause

Examples:

  1. Getting Max and Min salaries of employees simultaneously.

    SELECT (SELECT MAX(col1) FROM T1) AS Maximum ,
                 (SELECT MIN(col1) FROM T1) AS Minimum
  2. Doing calculation with Sub-query
    Find how meny employess are there those salary is less than maximum salary.

    SELECT (Select Max(salary) from Employee) - Salary
    FROM Employee

2.2.Sub-Queires in FROM-clause (Inline-Views)

    Examples:

  1. Simple in-line view ( Creating a derived table).

    SELECT *
    FROM (SELECT 'Fred' As FirstName, 'Flintstone' As LastName)
  2. Getting Maximum value of multiple tables(Complex in-line view)

    SELECT *
    FROM (SELECT 'Fred' As FirstName, 'Flintstone' As LastName)
  3. Performing calculations on inline-view

    SELECT col1+col2 as Total
    FROM (SELECT M1 as col1, M2 as col2 FROM table1)
  4. In-line view with JOIN operation.

    SELECT *
    FROM Table1 t1
        INNER JOIN (Select * from Table2) t2 ON t1.col1 = t2.col1

  5. Equals to
    SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.col1=t2.col1

  6. Store the result of a JOIN operation in new table (SELECT INTO + Derived Table)

    SELECT *
    INTO newJoinTable
    FROM (SELECT * FROM T1 INNER JOIN T2 ON T1.col1 = T2.col1) AS T
  7. Finding the employee who is getting Maximum salary using JOIN.

    SELECT *
    FROM Employee e
         INNER JOIN (Select Max(salary) as m From Employee) M
              ON e.salary = M.m
  8. Find whether or not two columns of a table are equal.

    Step-1: In the In-line view compare the two columns of the table(s) returns true or false.
    Step-2: If you get all ‘true’ then the columns are equal otherwise un-equal.

    SELECT CASE
             WHEN 'true'=ALL (
                      SELECT CASE WHEN (col1 = col3)
                              THEN 'true'
                              ELSE 'false'
                           END
              FROM Table
             )
            THEN 'equals'
             ELSE 'unequal'
       END
    AS Result


2.3: Sub-Queries in WHERE-Clause

Generally, subquery take one of these formats:
  • WHERE expr [NOT] in (Subquery)
  • WHERE expr comp_operator [ANY|ANY|SOME|ALL](Subquery)
  • WHERE [NOT] exists(subquery)

Examples:

  1. Find name of employee getting maximum salary. (SELECT + SELECT)

    SELECT ename FROM Employee WHERE salary =(SELECT MAX (salary) FROM Employee)
  2. Comparing with operators

    If Sub-query returns single value, then you can compare it with =, !=, <. <= etc. But when sub-query returns multiple values, use SOME, ANY, ALL, IN operators to perform comparisons.
    SELECT * FROM employee WHERE id IN(SELECT id FROM title)

    Finding Maximum of salary without using MAX() function.

    SELECT Salary FROM Employee where salary >=All(select salary from Employee)
  3. Inserting values from existing table (INSERT + SELECT)

    -- Copying all columns
     INSERT INTO T5 Select * from T4
    -- Copying selected columns
     INSERT INTO T5(col1,col2) SELECT col1, col2 FROM T4
  4. Deleting highest salaried employee (SELECT + DELETE)

    DELETE FROM Employee WHERE salary=(select Max(salary) from Employee)
  5. Interchange the Max salary and Mini salary. (SELECT + UPDATE)

    UPDATE Employee
     SET salary=CASE
              WHEN salary=(select Max(salary) FROM Employee)
                 THEN (select Min(salary) FROM Employee) 
              WHEN salary=(select Min(salary) FROM Employee) 
                THEN (select Max(salary) FROM Employee)
     ELSE salary END
  6. Multiple levels of Nested queries is possible.

    SELECT * FROM Employee
     WHERE ID IN ( SELECT ID FROM Title 
     WHERE ID IN (SELECT id FROM Employee 
    WHERE Start_Date > '3-1-2003') )


3. Co-Related Sub-Queries

3.1: Co-Related Sub-queries Execution

  1. First Outer query executes and submit values to the inner query.
  2. Then, Inner query executes by using value returned by outer-query.
  3. The condition applied on outer query checked.

The mapping of iterations
1 MainQuery : N Sub-query : 1 Main query Condition
1 MainQuery : N Sub-query : 1 Main query Condition.. M times.

3.2: Example: Find the N-Maximum of salary

Microsoft SQL Server Training Online Learning Classes Sql Sub Queries Nested Co-related
  1. 1st Maximum.

    SELECT Salary
    FROM Employee E1
    WHERE 0 = (SELECT COUNT(*)    --Read as “Selected value is less than 0 no. elements” 
               FROM Employee E2
               WHERE E1.salary <E2.Salary)
  2. 2nd Maximum.

    SELECT Salary
    FROM Employee E1
    WHERE 1= (SELECT COUNT(*) --Read as “Selected value is less than 1 no. elements”
              FROM Employee E2
              WHERE E1.salary <E2.Salary)
  3. Generic Nth Maximum

    SELECT Salary
    FROM Employee E1
    WHERE N-1 = (SELECT COUNT(*)
                 FROM Employee E2
                 WHERE E1.salary <E2.Salary)

4. Subqueries vs Correlated Subqueries:
    Difference between Subquery and correlated subquery

The main difference between Normal Sub-query and Co-related sub-query are:
  • Looping

    Co-related sub-query loop under main-query; whereas normal sub-query; therefore correlated Subquery executes on each iteration of main query. Whereas in case of Nested-query; Subquery executes first then outer query executes next. Hence, the maximum no. of executes are NXM for correlated subquery and N+M for subquery.
  • Execution:

    Correlated uses feedback from outer query for execution whereas Nested Subquery provides feedback to Outerquery for execution. Hence, Correlated Subquery depends on outer query whereas Nested Sub-query does not.
  • Performance:

    Using Co-related sub-query performance decreases, since, it performs NXM iterations instead of N+M iterations. ¨ Co-related Sub-query Execution.

80 comments:

  1. Thanks for sharing this nice blog..Its really useful information..

    DOT NET Training in Chennai

    ReplyDelete
  2. Thanks for sharing this nice information with us.It is really very nice blog..
    Training SQL Server

    ReplyDelete
  3. Nice blog post, thanks for the sharing. you have mentioned approx everything for the beginners of dot net. this helps to those candidates who are looking for the Dot Net Training Institute in Laxmi Nagar

    ReplyDelete
  4. Microsoft ASP.NET Training in Delhi- A good professional need to update regularly as per trend of market, to keep their skill sets update and industry relevant. To keep up updated with the changing requirements of the IT Industry, RKM IT Institute helps to BCA, MCA, BE, B. Tech and other IT students for their Industrial live Project Training. RKM IT Institute is providing Live Projects Training on .Net, Java, PHP, SQL Server and Oracle technologies. RKM IT Institute provides 6 month project based training and 6 week summer training projects.


    ReplyDelete
  5. There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

    ReplyDelete
  6. I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    SalesForce Training in Chennai

    ReplyDelete
  7. Pretty article! I found some useful information in your blog, it was awesome to read,thanks for sharing this great content to my vision, keep sharing..
    Unix Training In Chennai

    ReplyDelete
  8. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
    Android Training In Chennai In Chennai

    ReplyDelete
  9. SAP Training in Chennai
    This post is really nice and informative. The explanation given is really comprehensive and informative..

    ReplyDelete
  10. Oracle Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  11. Selenium Training in Chennai
    Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    ReplyDelete
  12. Data warehousing Training in Chennai
    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

    ReplyDelete
  13. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Websphere Training in Chennai

    ReplyDelete
  14. Oracle DBA Training in Chennai
    Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

    ReplyDelete
  15. This is really an awesome article. Thank you for sharing this.It is worth reading for everyone. Visit us:
    Oracle Training in Chennai

    ReplyDelete
  16. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    Oracle DBA Training in Chennai

    ReplyDelete
  17. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    Oracle RAC Training in Chennai

    ReplyDelete
  18. Wonderful tips, very helpful well explained. Your post is definitely incredible. I will refer this to my friend.
    SalesForce Training in Chennai

    ReplyDelete
  19. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    Java Training in Chennai

    ReplyDelete
  20. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    PHP Training in Chennai

    ReplyDelete
  21. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..
    Android Training in Chennai

    ReplyDelete
  22. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    SAP Training in Chennai

    ReplyDelete
  23. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Hadoop Training in Chennai

    ReplyDelete
  24. It is really very helpful for us and I have gathered some important information from this blog.If anyone wants to Selenium Training in Chennai reach Greens Technology training and placement academy.
    selenium Training in Chennai

    ReplyDelete
  25. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Hadoop Training In Chennai | oracle apps financials Training In Chennai | advanced plsql Training In Chennai

    ReplyDelete
  26. I have read your blog its very attractive and impressive. I like your blog.
    dotnet training in chennai

    ReplyDelete
  27. i wondered keep share this sites .if anyone wants realtime training Greens technolog chennai in Adyar visit this blog..
    sas training in chennai

    ReplyDelete
  28. Excellent information with unique content and it is very useful to know about the information based on blogs.
    sas training in chennai

    ReplyDelete
  29. Really informative blog! Thanks for sharing.
    SQL server online Tutorial course from TechandMate provides a descriptive learning of the Database concepts along with the working of the relational databases. https://goo.gl/eKIb8F

    ReplyDelete
  30. Very well explained. Easy to understand.
    "SQL Server 2016 Training | MS SQL
    Corporate Training
    teaches you basic concepts of relational databases and the SQL programming language.

    ReplyDelete

  31. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  32. Pretty section of content. I simply stumbled upon your site and in accession capital to say that I get actually loved to account your blog posts.
    PHP Training in Chennai

    ReplyDelete
  33. Thanks for writing this in-depth post. You covered every angle. The great thing is you can reference different parts.
    Dot Net Online Training Hyderabad

    ReplyDelete
  34. Thanks for writing this in-depth post. You covered every angle. The great thing is you can reference different parts Gexton Education .

    ReplyDelete
  35. This is very nice blog,and it is helps for student's.Thanks for info
    .Net Online Training

    ReplyDelete
  36. It's so nice article thank you for sharing a valuable content
    Sql Server dba online training

    ReplyDelete
  37. It is very good blog and useful for students and developer ,
    Thanks for sharing this amazing blog,
    .Net Online Training Hyderabad

    ReplyDelete

  38. I wish to show thanks to you just for bailing me out of this particular trouble.As a result of checking through the net and meeting techniques that were not productive, I thought my life was done.
    Advanced Selenium Training in Chennai

    ReplyDelete
  39. Thanks a lot very much for the high your blog post quality and results-oriented help. I won’t think twice to endorse to anybody who wants and needs support about this area.
    uipath training institute in chennai

    ReplyDelete
  40. Appreciation for really being thoughtful and also for deciding on certain marvelous guides most people really want to be aware of.
    Best selenium training Institute in chennai

    ReplyDelete
  41. It is very good blog and useful for students and developer , Thanks for sharing

    .Net Online Training

    ReplyDelete
  42. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp great deal more around this condition.

    Amazon Web Services Training in Chennai


    Best Java Training Institute Chennai


    ReplyDelete
  43. Awesome article. It is so detailed and well formatted that i enjoyed reading it as well as get some new information too..

    Weblogic Application Server training

    ReplyDelete
  44. I Just Love to read Your Articles Because they are very easy to understand.Very Helpful Post And Explained Very Clearly About All the things.Very Helpful. Coming To Our Self We Provide Restaurant Equipment Parts Through Out US At Very Affordable Prices And Also We Offer Same Day Shipping In US.We Offer Only Genuine Products.Thanks For Posting.HAve a Nice Day!

    ReplyDelete
  45. Super Indeed A Great Article Thanks for Posting and Sharing Very Useful and helpful Urgent Care Services Provided by Us.I just Want to share this blog with my friends and family.A worthy blog...Keep On posting New posts,I Will follow this blog regularly..

    ReplyDelete
  46. hi,
    I am looking for an expert remote help on SSIS for a project in Canada. Remote help is good enough. contact: ts012368@yahoo.ca

    ReplyDelete
  47. It's amazing blog And useful for me Thanks
    .Net Online Training

    ReplyDelete
  48. The information you provided in the article is useful and beneficial US Medical Residency Really Thankful For the blogger providing such a great information. Thank you. Have a Nice Day.

    ReplyDelete
  49. Thank you for your guide to with upgrade information.
    Dot Net Online Course Bangalore

    ReplyDelete
  50. Thanks a lot very much for the high quality and results-oriented help.
    Dot net training in Hyderabad!

    ReplyDelete
  51. Thank you for your guide to with upgrade information.
    Sql server DBA Online Training

    ReplyDelete
  52. This comment has been removed by the author.

    ReplyDelete

  53. Really it was an awesome article… very interesting to read…
    Thanks for sharing.........

    ms dotnet online training in ammeerpet

    ReplyDelete
  54. This concept is a good way to enhance the knowledge.thanks for sharing. please keep it up Java online training Bangalore

    ReplyDelete
  55. This Blog Provides Very Useful and Important Information. I just Want to share this blog with my friends and family members. Tibco Certification Training

    ReplyDelete
  56. Thanks For Sharing Such an Important and Useful Content On Salesforce Certification Training

    ReplyDelete
  57. I recently completed this course at ExcelR. I found this course very demanding. I learned a lot in this course. I was particularly impressed with the trainers which is the best feature of ExcelR. There is a wide breadth of topics covered in a short period of time. Love ExcelR.
    Microsoft Project Training In Hyderbad

    ReplyDelete
  58. Nice information thank you,if you want more information please visit our link selenium Online Training Bangalore

    ReplyDelete
  59. Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us.
    AWS Online Training

    ReplyDelete
  60. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.

    java training in omr

    java training in annanagar | java training in chennai

    java training in marathahalli | java training in btm layout

    java training in rajaji nagar | java training in jayanagar

    ReplyDelete
  61. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    python training in chennai | python training in bangalore

    python online training | python training in pune

    python training in chennai | python training in bangalore

    python training in tambaram |

    ReplyDelete
  62. Hello I am so delighted I found your blog, I really found you by mistake, while I was looking on Yahoo for something else, anyways I am here now and would just like to say thanks for a tremendous post. Please do keep up the great work.

    python training in annanagar | python training in chennai

    python training in marathahalli | python training in btm layout

    python training in rajaji nagar | python training in jayanagar

    ReplyDelete
  63. Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

    rpa training in marathahalli

    rpa training in btm

    rpa training in kalyan nagar

    rpa training in electronic city

    rpa training in chennai

    rpa training in pune

    rpa online training

    ReplyDelete
  64. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..

    rpa training in Chennai

    rpa training in anna nagar | rpa training in marathahalli

    rpa training in btm | rpa training in kalyan nagar

    rpa training in electronic city | rpa training in chennai

    rpa online training | selenium training in training

    ReplyDelete