free barcode generator using vb.net Subqueries and Joins in Software

Maker QR-Code in Software Subqueries and Joins

Subqueries and Joins
QR Code ISO/IEC18004 Reader In None
Using Barcode Control SDK for Software Control to generate, create, read, scan barcode image in Software applications.
Print QR Code In None
Using Barcode creator for Software Control to generate, create QR Code image in Software applications.
You may have noticed as you read through this chapter that many of the queries that were written using subqueries could also have been written as multi-table queries, or joins. This is often the case, and SQL allows you to write the query either way. This
QR-Code Reader In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
QR-Code Drawer In Visual C#.NET
Using Barcode drawer for .NET framework Control to generate, create QR Code ISO/IEC18004 image in .NET applications.
- 172 -
Encoding QR In VS .NET
Using Barcode creation for ASP.NET Control to generate, create QR Code ISO/IEC18004 image in ASP.NET applications.
Printing QR Code ISO/IEC18004 In VS .NET
Using Barcode encoder for .NET framework Control to generate, create QR Code 2d barcode image in VS .NET applications.
example illustrates the point: List the names and ages of salespeople who work in offices in the Western region. SELECT NAME, AGE FROM SALESREPS WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION = 'Western') NAME -------------Sue Smith Larry Fitch Nancy Angelli AGE --48 62 49
QR Code Creator In Visual Basic .NET
Using Barcode generator for .NET framework Control to generate, create Quick Response Code image in .NET framework applications.
Generating Bar Code In None
Using Barcode encoder for Software Control to generate, create barcode image in Software applications.
This form of the query closely parallels the stated request. The subquery yields a list of offices in the Western region, and the main query finds the salespeople who work in one of the offices in the list. You'll see on the next page an alternative form of the query, using a two-table join. List the names and ages of salespeople who work in offices in the Western region. SELECT NAME, AGE FROM SALESREPS, OFFICES WHERE REP_OFFICE = OFFICE AND REGION = 'Western' NAME -------------Sue Smith Larry Fitch Nancy Angelli AGE --48 62 49
Code39 Encoder In None
Using Barcode maker for Software Control to generate, create Code-39 image in Software applications.
Making Bar Code In None
Using Barcode encoder for Software Control to generate, create bar code image in Software applications.
This form of the query joins the SALESREPS table to the OFFICES table to find the region where each salesperson works, and then eliminates those that do not work in the Western region. Either of the two queries will find the correct salespeople, and neither one is "right" or "wrong." Many people will find the first form (with the subquery) more natural, because the English request doesn't ask for any information about offices, and because it seems a little strange to join the SALESREPS and OFFICES tables to answer the request. Of course if the request is changed to ask for some information from the OFFICES table: List the names and ages of the salespeople who work in offices in the Western region and the cities where they work. the subquery form will no longer work, and the two-table query must be used. Conversely, many queries with subqueries cannot be translated into an equivalent join. Here is a simple example: List the names and ages of salespeople who have above average quotas. SELECT NAME, AGE FROM SALESREPS
Data Matrix 2d Barcode Generator In None
Using Barcode maker for Software Control to generate, create Data Matrix ECC200 image in Software applications.
Create Code 128C In None
Using Barcode drawer for Software Control to generate, create Code 128 image in Software applications.
- 173 -
ITF-14 Generator In None
Using Barcode generation for Software Control to generate, create UCC - 14 image in Software applications.
Scanning Barcode In VB.NET
Using Barcode Control SDK for .NET framework Control to generate, create, read, scan barcode image in VS .NET applications.
WHERE QUOTA > (SELECT AVG(QUOTA) FROM SALESREPS) NAME -------------Bill Adams Sue Smith Larry Fitch AGE --37 48 62
UPC Symbol Scanner In None
Using Barcode decoder for Software Control to read, scan read, scan image in Software applications.
Create Bar Code In Java
Using Barcode maker for Java Control to generate, create bar code image in Java applications.
In this case, the inner query is a summary query and the outer query is not, so there is no way the two queries can be combined into a single join.
Code 3/9 Generator In Java
Using Barcode generator for Android Control to generate, create ANSI/AIM Code 39 image in Android applications.
Generating Code-39 In Java
Using Barcode creation for Java Control to generate, create Code 3 of 9 image in Java applications.
Nested Subqueries
GS1-128 Maker In Objective-C
Using Barcode encoder for iPad Control to generate, create GTIN - 128 image in iPad applications.
EAN-13 Creator In Java
Using Barcode drawer for Java Control to generate, create EAN-13 image in Java applications.
All of the queries described thus far in this chapter have been "two-level" queries, involving a main query and a subquery. Just as you can use a subquery "inside" a main query, you can use a subquery inside another subquery. Here is an example of a request that is naturally represented as a three-level query, with a main query, a subquery, and a subsubquery: List the customers whose salespeople are assigned to offices in the Eastern sales region. SELECT COMPANY FROM CUSTOMERS WHERE CUST_REP IN (SELECT EMPL_NUM FROM SALESREPS WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION = 'Eastern')) COMPANY --------------First Corp. Smithson Corp. AAA Investments JCP Inc. Chen Associates QMA Assoc. Ian & Schmidt Acme Mfg. . . . In this example, the innermost subquery: SELECT OFFICE FROM OFFICES WHERE REGION = 'Eastern' produces a column containing the office numbers of the offices in the Eastern region. The next subquery:
- 174 -
SELECT EMPL_NUM FROM SALESREPS WHERE REP_OFFICE IN (subquery) produces a column containing the employee numbers of the salespeople who work in one of the selected offices. Finally, the outermost query: SELECT COMPANY FROM CUSTOMERS WHERE CUST_REP IN (subquery) finds the customers whose salespeople have one of the selected employee numbers. The same technique used in this three-level query can be used to build queries with four or more levels. The ANSI/ISO SQL standard does not specify a maximum number of nesting levels, but in practice a query becomes much more time-consuming as the number of levels increases. The query also becomes more difficult to read, understand, and maintain when it involves more than one or two levels of subqueries. Many SQL implementations restrict the number of subquery levels to a relatively small number.
Correlated Subqueries *
In concept, SQL performs a subquery over and over again once for each row of the main query. For many subqueries, however, the subquery produces the same results for every row or row group. Here is an example: List the sales offices whose sales are below the average target. SELECT CITY FROM OFFICES WHERE SALES < (SELECT AVG(TARGET) FROM OFFICES) CITY ------Denver Atlanta In this query, it would be silly to perform the subquery five times (once for each office). The average target doesn't change with each office; it's completely independent of the office currently being tested. As a result, SQL can handle the query by first performing the subquery, yielding the average target ($550,000), and then converting the main query into: SELECT CITY FROM OFFICES WHERE SALES < 550000.00 Commercial SQL implementations automatically detect this situation and use this shortcut whenever possible to reduce the amount of processing required by a subquery. However, the shortcut cannot be used if the subquery contains an outer reference, as in this example: List all of the offices whose targets exceed the sum of the quotas of the salespeople who work in them:
- 175 -
SELECT CITY FROM OFFICES WHERE TARGET > (SELECT SUM(QUOTA) FROM SALESREPS WHERE REP_OFFICE = OFFICE) CITY ----------Chicago Los Angeles For each row of the OFFICES table to be tested by the WHERE clause of the main query, the OFFICE column (which appears in the subquery as an outer reference) has a different value. Thus SQL has no choice but to carry out this subquery five times once for each row in the OFFICES table. A subquery containing an outer reference is called a correlated subquery because its results are correlated with each individual row of the main query. For the same reason, an outer reference is sometimes called a correlated reference. A subquery can contain an outer reference to a table in the FROM clause of any query that contains the subquery, no matter how deeply the subqueries are nested. A column name in a fourth-level subquery, for example, may refer to one of the tables named in the FROM clause of the main query, or to a table named in the FROM clause of the second-level subquery or the third-level subquery that contains it. Regardless of the level of nesting, an outer reference always takes on the value of the column in the "current" row of the table being tested. Because a subquery can contain outer references, there is even more potential for ambiguous column names in a subquery than in a main query. When an unqualified column name appears within a subquery, SQL must determine whether it refers to a table in the subquery's own FROM clause, or to a FROM clause in a query containing the subquery. To minimize the possibility of confusion, SQL always interprets a column reference in a subquery using the nearest FROM clause possible. To illustrate this point, in this example the same table is used in the query and in the subquery: List the salespeople who are over 40 and who manage a salesperson over quota. SELECT NAME FROM SALESREPS WHERE AGE > 40 AND EMPL_NUM IN (SELECT MANAGER FROM SALESREPS WHERE SALES > QUOTA) NAME ----------Sam Clark Larry Fitch The MANAGER, QUOTA, and SALES columns in the subquery are references to the SALESREPS table in the subquery's own FROM clause; SQL does not interpret them as outer references, and the subquery is not a correlated subquery. As discussed earlier, SQL can perform the subquery first in this case, finding the salespeople who are over quota and generating a list of the employee numbers of their managers. SQL can then turn its attention to the main query, selecting managers whose employee numbers appear in the generated list.
- 176 -
If you want to use an outer reference within a subquery like the one in the previous example, you must use a table alias to force the outer reference. This request, which adds one more qualifying condition to the previous one, shows how: List the managers who are over 40 and who manage a salesperson who is over quota and who does not work in the same sales office as the manager. SELECT NAME FROM SALESREPS MGRS WHERE AGE > 40 AND MGRS.EMPL_NUM IN (SELECT FROM WHERE AND MGRS.REP_OFFICE) NAME ----------Sam Clark Larry Fitch The copy of the SALESREPS table used in the main query now has the tag MGRS, and the copy in the subquery has the tag EMPS. The subquery contains one additional search condition, requiring that the employee's office number does not match that of the manager. The qualified column name MGRS.OFFICE in the subquery is an outer reference, and this subquery is a correlated subquery.
Copyright © OnBarcode.com . All rights reserved.