SQL Application and Development: (vii) Data manipulation · Check (iii) Access and modification of data using sub-queries

3. Accessing and modifying data using subqueries

Subqueries and join queries provide a way to access data from multiple tables using a single query. Subqueries provide an efficient way to represent the conditions of a WHERE clause, based on other results. A subquery is a SELECT statement that is defined in a SELECT, INSERT, UPDATE, or Delect statement, or in another subquery. The SELECT statement for a subquery can point to a different table than an external query.

A nested subquery or a nested SELECT statement refers to a SELECT statement that contains one or more subqueries. A subquery can be nested inside a WHERE or HAVING clause or other subquery of an external SELECT, INSERT, UPDATE, or Delect statement. If multiple layers are nested, the most internal queries are always evaluated first. subqueries also become internal queries, or any number of subqueries can be nested. Any place where an expression can be used can use a subquery, as long as it returns a single value.

3.1 Subqueries that return multiple rows

A subquery is implemented in a WHERE clause in a SELECT statement, and the subquery in the WHERE clause can be divided into two categories: a subquery that returns multiple rows, and a subquery that returns only one value.

3.1.1 Using the In keyword

You can use the In keyword to determine whether the value of a specified column in a table is contained in a defined list, or in another table. In the former case, you can specify the column name, the in keyword, and a list of values to use to specify the column, in which case you can specify the column name, the in keyword, and the subquery that references the other table.

For example, from the database "Sales Management System", query (No) received the customer's information about the salesman.

SELECT salesman name, home address, telephone

From salesman information

WHERE salesman number (not) in

(SELECT the salesman number

From customer information)

3.1.2 Using the EXISTS keyword

In some cases, you only need to return a true or False value. The EXISTS keyword only focuses on whether the subquery returns rows. If the subquery returns one or more rows, then exists returns to True, otherwise false. A subquery must be a true value that is used to compare the values of two columns in different tables.

When a subquery is introduced using the EXISTS keyword, it is the equivalent of testing for the existence of a single query. Its purpose is to test the presence of rows returned by the subquery in the WHERE clause. The exisits subquery does not actually produce any data, it only returns TRUE or false.

For example, in the database "Sales Management system", Query the "Vendor Information" table, "vendor number" is 1006 of the supplier's information about the goods provided.

SELECT product name, origin, Unit price

From product information

WHERE exisits

(SELECT *

From supplier information

WHERE Supplier Number = commodity information. Vendor number

and supplier number =1006)

Not exists and exists instead, if the subquery does not return rows, the WHERE clause in not EXISTS is satisfied, that is, the NOT EXISTS query succeeds when the subquery returns rows.

For example, in the "Jewelry sales system", the query in the "Sales information" does not provide jewelry and its city is not a Beijing-based jeweler's information, and according to the "jeweler City" to sort.

SELECT jeweler name, jeweler's address, city of jeweler, telephone

From Jewelers information

WHERE not EXISTS

(SELECT *

From Sales information

WHERE Jeweler Number = Jeweler information. Jeweler number

)

And Jewelers ' City <> ' Beijing '

ORDER by Jeweler City

3.1.3 Using comparison operators

A subquery can be introduced by a comparison operator. The subquery introduced by the comparison operator and some keywords returns a list of values, just as a subquery is introduced with the keyword in.

The comparison operators that SQL supports for use in subqueries are any, some, and all. The any and some keywords focus only on whether there are return values that meet the search criteria, they have the same meanings, and can be replaced with each other. The all keyword only focuses on whether all return values satisfy the search criteria.

For example, in the database "Sales management system" in the "Outbound Order Details" table, the "Outbound commodity amount" is greater than any one of the "Inbound Order Details" table in the "Inbound commodity amount" is higher than 10000 of the relevant information.

SELECT *

From out of stock list details

WHERE Outbound Order Item amount > any

(SELECT Inbound Item Amount

From inbound list Details

WHERE Inbound Merchandise amount > 10000 )

In order to facilitate learning the use of the situation, we carry out a comparative study, the following is a similar example, in the database "Sales management system" in the "Inventory Details" table, query "Outbound commodity amount" is greater than all "inbound Order Details" table "Inbound commodity amount" less than 10000 Information about the outbound order.

SELECT *

From out of stock list details

WHERE out of stock Item amount > All

(SELECT Inbound Item Amount

From inbound list Details

WHERE Inbound Merchandise Amount < 10000 )

3.2 Subqueries that return a single value

Such a subquery returns only one value, and then compares a column value to the value returned by a single subquery, which can be used when comparing operators.

A subquery introduced by an unmodified comparison operator (a comparison operator that is not followed by any or all) must return a single value instead of a list of values.

For example, in the "Jewelry sales system", the query and the "consumer number" of 27 of consumers in a city with the jeweler information, required to be listed as "Jeweler name", "Jeweler's address", "City of Jewelers" and "phone" in the form of return query results.

SELECT jeweler name, jeweler address, Jewel Hill City, telephone

From Jewelers information

Where Jeweler's City =

(SELECT Consumer City

From Customer information

WHERE Consumer number =27

)

Because the aggregation function can return a single value, the aggregation function can be included in the sub-good wipe.

For example, in the "Commodity information" table in the database "Sales Management system", the information about items with a unit price greater than the average price is queried. Requires that the results of the query be returned in the form of a column "trade name", "origin", and "unit price".

SELECT product name, origin, Unit price

From product information

WHERE Price >

(SELECT AVG (unit price)

From product information

)

3.3 Using related subqueries

In the previously described subquery, SQL only evaluates the subquery one at a time, then replaces the sub-query results in the search criteria and evaluates the external query based on the values of the search criteria. However, some subqueries perform procedures that depend on the merits of external queries. The result is a recurring subquery that executes once for each row selected by the external query. Such a subquery is called a correlated subquery.

Because correlated subqueries depend on external queries for their results, they cannot be evaluated separately. The WHERE clause of the related subquery references the table in the FROM clause of the outer query. That is, a related subquery is a table in the FROM clause of a reference external query that contains a table in an external query. In other words, a correlated subquery is a subquery that contains a reference to a table in an external socialize, and it cannot be evaluated before an external query.

For example, in the "Sales Management System", query the "supplier name" for "Beijing Century Sunflower" information about the goods provided. Requires that the results of the query be returned in the form of a column "trade name", "origin", and "unit price".

SELECT product name, origin, Unit price

From product information

WHERE ' Beijing century Sunflower ' in

(SELECT supplier Name

From supplier information

WHERE commodity information. Vendor number = Supplier information. Supplier number

)

In related subqueries, you can also specify table names for tables, using aliases instead of table names. However, if you are making related subqueries for the same table, you must specify an alias for the table.

For example, in the "Jewelry marketing system" of the "Customer information" table, to find out where customers live in different cities.

SELECT DISTINCT A. The city where the consumer is located

From Customer information A

where A. Consumer city in

(SELECT B. The city where the consumer resides

From Customer information B

WHERE A. Consumer number <>b. Consumer number

)

The statement of the above subquery is equivalent to the following self-join query statement (see here for an introduction to self-join), after the self-join query statement is run, its query result is exactly the same as the result of the subquery statement above.

SELECT DISTINCT A. The city where the consumer is located

From customer information A INNER JOIN Customer Information B

On A. Consumers in the city of =b. The city where consumers reside

and A. Consumer number <>b. Consumer number

3.4 Using nested subqueries

The SELECT statement, which has only one subquery, is described earlier, however, you can also include multiple subqueries in a SELECT statement, that is, a subquery that also contains other subqueries, which are called nested subqueries.

One way to use multiple subqueries in a SELECT statement is to make them different parts of the statement. For example, a WHERE clause might contain two keywords to bootstrap two subquery statements. Another way to use multiple subqueries in a SELECT statement is to nest one subquery into another subquery.

For example, in the database "Sales management system", to query customers from June 1, 2005 to December 31, 2005 when the purchase of goods, the reception of the customer's salesman information. Required to return the results of the query in the form of "salesman name", "Home Address" and "phone".

SELECT salesman name, home address, telephone

From salesman information

WHERE salesman number in

(SELECT the salesman number

From Customer information

WHERE customer number in

(SELECT customer number

From out of stock list information

WHERE out of the library date between ' 2005-6-1 ' and ' 2005-12-31 '

)

)

The above statements run in the following order: Green, purple, blue, that is, from inside to outside, successive queries.

3.5 Modifying Data using subqueries

Subqueries can also be used to modify data in a database. Using a subquery to modify the data is mainly implemented by the following 3 keywords to implement INSERT, update, and delete.

3.5.1 inserting data

The INSERT statement can add data to an existing table. It can insert data directly into a table, or you can insert data into an implicit table with a view. If you are using a subquery in an INSERT statement, you must use it as a value defined in the VALUES clause.

For example, in the database "Sales management system" in the "Salesman information" table to add a salesperson's new row of data, the line of data "salesman number" is 1009, "salesman name", "Home Address" and "telephone" from the "Customer Information" table "customer number" is 1008 "Customer name", " Customer address "and" Contact phone ".

INSERT into clerk Information VALUES

(1009,

(SELECT Customer Name

From Customer information

WHERE customer number =1008 ),

(SELECT customer Address

From Customer information

where customer number =1008),

(SELECT Contact Phone

From Customer information

WHERE customer number =1008)

)

Then execute the following statement to view:SELECT * from salesman information

When you insert data into a table by using a subquery in an INSERT statement, you must determine that the result of the subquery returns only one value. If more than one value is returned for the query result, an error occurs, and the single value returned in the subquery must match the data type of the destination column and other restrictions.

3.5.2 Updating data

The UPDATE statement allows you to modify existing data in the table. As with the INSERT statement, you can modify the data in the table directly. If the view is updatable, it can also be modified through the view. To use subqueries in an UPDATE statement, a subquery is introduced by a WHERE clause.

For example, in the last example, the "Salesman information" table in the database "Sales management system" to add the line of data corresponding to the "salesman name", "Home Address" and "telephone" respectively changed to "Zhaochi", "Xicheng District in Beijing" and "13585452343".

UPDATE salesman Information

SET salesman name = ' Zhaochi ', home address = ' Xicheng District, Beijing ', tel = ' 13585452343 '

WHERE Salesman name =

(SELECT Customer Name

From Customer information

WHERE Customer name = ' Xue Red Forest '

)

Then execute the following statement to view:SELECT * from salesman information

3.5.3 Deleting data

The DELETE statement implements the ability to delete data from a database table. Using a subquery in the WHERE clause in the DELETE statement is similar to the UPDATE statement.

For example, delete the row of data inserted in the "Salesman information" Table of the database "Sales management system".

DELETE salesman Information

WHERE Salesman name =

(SELECT Customer Name

From Customer information

WHERE Customer number =1008

)

4. Learning Summary

Database check operation is the key learning part of the database, in these parts of the process of learning and summary, deeply weak, regardless of the knowledge system or the complexity of the related links, are very important, but also the most difficult to understand and grasp.

Oneself is also used for half a month to the database of the summary of the operation of the last time to summarize out, do a share, of course, have to say, for the database of related knowledge, hope to practice, in the operation to understand the usage, in order to better grasp its essentials, but also hope that they can do better in the future.

SQL Application and Development: (vii) Data manipulation · Check (iii) Access and modification of data using sub-queries