How to use SQL Server nested subqueries

Source: Internet
Author: User
Tags how to use sql server connect sql query how to use sql

Many SQL Server programmers are confused about the use of subqueries (subqueries), especially for nested subqueries (that is, a subquery that contains a subquery). Now, let's look at this question in the right way.

There are two types of seed queries: standard and related. The standard subquery executes once, and the results are fed back to the parent query. The related subquery is executed once per line and retrieved by the parent query. In this article, I'll focus on nested subqueries (nested subqueries) (I'll introduce related subqueries later).

Imagine the problem: you want to generate a list of salespeople who sell flat washers. The data you need is scattered across four tables: Personnel. Contact information (person.contact), human resources, staff (HumanResources.Employee), sales. Sales order title (Sales.SalesOrderHeader), sales, sales order Details (Sales.salesorderdeta IL). In SQL Server, you write the program from the internal pressure (outside-in), but it is very helpful to start with the outer pressure (inside-out), that is, you can resolve one of the required statements at a time.

If you write from inside to outside, you can check the Sales.SalesOrderDetail table and match the product number (ProductNumber) values in the like statement. You connect these lines to the Sales.SalesOrderHeader form, from which you can obtain the Salesperson IDs (Salespersonids). Then use SalesPersonID to connect the SalesPersonID table. Finally, use ContactID to connect the Person.Contact table.

The following are the referenced contents:
Use AdventureWorks;
Go
SELECT DISTINCT C.lastname, C.firstname
From Person.Contact c JOIN humanresources.employee E
On e.contactid = C.contactid WHERE EmployeeID in
(SELECT SalesPersonID
From Sales.SalesOrderHeader
WHERE SalesOrderID in
(SELECT SalesOrderID
From Sales.SalesOrderDetail
WHERE ProductID in
(SELECT ProductID
From Production.Product P
WHERE productnumber like ' fw% '));
Go

This example reveals several wonderful things about SQL Server. You can see that you can replace the SELECT statement with the in () parameter. In this case, there are two applications, so a nested subquery is created.

I am a normalization of standardization, though I do not accept its absurd length. Complexity is increased by the fact that standardization has a variety of queries. In these cases the query becomes very useful, and nested subqueries are even more useful.

When the problems you need are scattered across many tables, you have to put them together again, and you may find that nesting is useful.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.