By Trudy Pelzer
Translation: Guipei
With the release of MySQL version 4.1, there are now two ways to use a separate query to get data from multiple data sheets: use connection (Join) or subscriber (SUBQUERY). Let us explain that you have a form:
CREATE TABLE Clients
Clno Int,
FName Varchar (15),
Lname varchar (15),
Job Varchar (15),
Account_balance Decimal (7, 2));
Insert Into Clients Values
(10, 'sam', 'smith', 'Auditor', 5525.75),
(20, 'James', 'Jones', 'Manager', 8960.25);
Create Table Firms
Clno Int,
Company VARCHAR (15),
City VARCHAR (15));
Insert Into Firms Values
(10, 'abc co', 'leduc'),
(20, 'DEF LTD', 'Nisku'),
(30, 'GHI INC', 'Nisku');
Below we use the connection (Join) to get the data of the ID 10 in the client table.
SELECT
FName, Lname, City, Job, Company, Account_balance
From Clom Clients C, Firms F
WHERE C.CLNO = F.clno
And c.clno = 10;
But you are not always able to query the data you need by using the connection (Join). For example, if you need the customer information in the largest account table. The following query looks like it should be able to return the data you need correctly, but it actually happens:
SELECT
FName, Lname, City, Job, Company, Account_balance
From Clom Clients C, Firms F
WHERE C.CLNO = F.clno
And c.account_balance = max (c.account_balance);
The reason for the error is: Invalid Use of group function, this aggregate function, Max, does not allow it to appear in the WHERE clause. This is the reason why the child query is born. In this article, I add a short description to the subqueries of the MySQL database system.
Subqueries are included in parentheses to select statements
Simple, subquery is a query statement written in another SQL statement (in general, it is a SELECT statement, but not always like this). It is a case where the query statement is in the middle of the query statement is to see if the query statement is in parentheses.
Select * from clients where clno in - Outer Query
(Select Clno from firms where city = 'leduc'); - Inner Query
Select * from clients where clno in - External query
(Select Clo from firms where city = 'leduc'); - internal query
This query will return to the record of all cities in Clients 'LEDuc' in Clients. In order to get the result, DBMS first obtains the result of the internal query, and finds a record of all cities in all cities in all tables. Then compare the table Clients, returns every line that meets the CLNO value. Because there is only one value compliant, it is actually equivalent to the following statement: SELECT * from clients where clno = 10;
Of course, this query statement can also be written as follows:
SELECT
C.clno, Fname, Lname, Job, Account_balance
From Clom Clients C Inner Join Firms F Using (ClN)
WHERE city = 'leduc';
At present, the problem mentioned above can be solved by subsis (query records of these customers' CLNO values):
SELECT FNAME, LNAME from Clients Where ClNo =
SELECT MAX (CLNO) from firms;
If the internal query returns an empty result set, the subquery will return an error, for example, see the following query:
Select * from clients where clno =
(Select Clno from firms where city = 'gibbons');
If the internal query is running separately, it is clear that the result is empty: because there is no city in this table to be a record of 'Gibbons'. However, the empty result set cannot be compared as a value. SQL standard requirements subquering set, empty result set, or empty. Because there is no thing equal to NULL, the query returns to the empty message.
In general, subqueries are nesting in other queries, MySQL supports subqueries nested in other SQL statements, this is a good feature.
Types of Subqueries
Subquerical type
There are three types of subquery, distinguishing between their methods, based on the number of columns and lines of the return result.
If a subquery returns only one row of data, it is a quantity grade query. A quantitative grade query can be used in any place for the data value (a column value or text). It is often used in the WHERE clause, followed by the comparison operation symbol.
If the subquery returns multiple columns and is a row of records, it is called a row-level subquery. Row-level subqueries are the development of quantitative grade queries, which can be used in any place suitable for quantitative levels.
Finally, if a subquery returns multiple columns and multi-line, it is called a table level subquery. The table-level subquery can be used as a query operation involved in the table, which is included in the FROM clause. It is often used in the WHERE clause, which is used later using IN or Exist or a determined comparison operation (determined comparison operation is a comparison operation by using Some, ALL, or ANY).
Quantity grade query and table level subquery are very small. There is often a case where a quantity level query is written, but the child query returns multiple record lines. Suppose we have two tables and some recorded data.
Insert Into Clients Values
(10, 'Sam', 'Smith', 'Auditor', 5525.75);
Insert Into Firms Values
(10, 'ABC CO', 'LEDUC'), (30, 'GHI INC', 'Nisku');
Because the table firms has two lines of records in this query return:
Select * from clients where clno <(select Clno from firms);
wrong reason:
"Subquery Returns More Than 1 ROW"
This situation has two solutions. The first is to modify the query allows it to include any value of the external query:
Select * from clients where clno SELECT Clom Firms; Here, the first comparison is (10 <10), returned to false, but the second comparison (10 <30), returns TRUE, so the subquery returns True, using the result of Clno 10 comparison, using any The rules are as follows: Any returns true if there is at least one of the comparison operations of the child query. Any returns false, if the child query is returned to a blank line, or any line is false. The synonym of Any is Some, which is equivalent to use = any. The second method is to modify the query, use the all containing subqueries, comparing the external query results using the value of each subquery. Select * from clients where clno SELECT Clom Firms; Here, the first comparison or returning false, and the second returns True, and finally, the result of the subquery is false, so the query returns a blank line. The rules that use all are: Returns True, if the subquery returns to the empty line record, or every result of the subquery is True. Returns false, if the child query has at least one comparison result returns false. Does The Subquery Return AT Least One Row? Is the child query required to return a line? Sometimes, the result of the query is whether the subquery has a result returns. Use [NOT] EXISTS to determine if the result set is empty. If your subquerix returns at least one line, use EXISTS to return true; otherwise, return to false. There is also a usage, not exists, if the subquery result is empty, it returns True, otherwise, return to TRUE. Under normal circumstances, the subquery follows behind [NOT] EXISTS, starting with SELECT *. Here, the asterisk (*) does not mean list all columns, but is represented as some columns. Here is a small example that returns the results of all client tables. Select * from clients where exists SELECT * FROMs); In this example, the WHERE clause returns True because the table firms is not empty. Usually [NOT] EXISTS will be used in more complex situations, suppose you have a data table: Create Table Passengers Name varchar (15), Compartment Int); Insert Into Passengers Values ('Smith', 20); Insert Into Passengers Values ('Jones', 25); CREATE TABLE CARS Compartment Int, Class varchar (10)); INSERT INTO CARS VALUES (20, 'First'); Here is the query of this type: Select * from Cars C1 Where not exists (Select * from passengers p1 where not exists (SELECT * from country Cars C2 Where c2.comPartment = p1.comPartment And C2.Compartment = c1.comPartment)); The role of this query is to ask for all passengers who have passengers. In order to understand the meaning of this result set, we assume that Smith was car, Jones 25, but did not include 25 cars in the CARS table. This will have a passenger-Jones-take a car that does not exist. (In the standard database, there is no existence, you can constrain the data integrity of these two tables by using the primary key / foreign key. The second NOT EXISTS sub-query in this query, returns TRUE for passengers Jones. In addition, a passenger-Jones here did not take the No. 20 car. So the first NOT EXISTS subquery returns False here. Because there is no other car here, this query is empty (return to zero) - There is no car every passenger ride. Other usage of child query In SQL standard 1999, MySQL provides enhanced subquery support. The row-level subquery mentioned earlier, now supports the value of multiple columns at a time: SELECT ROW ('Smith', 'Auditor') = (SELECT LNAME, Job from Clients Where ClNo = 10); In this example, the subquery contains data that is 'Smith' and 'Auditor'. When these data and the value of the ROW are compared, their content is the same, and therefore returns 1 (TRUE). You can also put a subquery as a table name, put it behind the FROM clause (just like the usage in Oracle, as an internal view): SELECT * FROM (Select * from clients where job like 'a%') AS CL; In order to obtain the result of this query, the MySQL server is familiar with the result of obtaining the subqueries, and then assigns the result set to an alias (CL, in this example). Then use the external query. In fact, the above example can be decomposed as follows: SELECT * from HL; Here CL is a temporary result set for a subquery: Select * from clients where job like 'a%'; When the child query is used in the FROM clause, the keyword AS is forced; the intermediate temporary result must be named as an external query being referenced. User query modified data Subqueries have such a function: used to modify the data of the database. That is to say, you can put the child in the delete, insert, update, or the replace statement, for example: Update clients set account_balance = (Select Sum (Amount) from Accounts Where ClNo = Clients.Clno); This UPDATE statement modifies the Account_balance data for each customer, by calculating the client account in the Account table. Here is a prompt: there is currently no subquery of the same table to modify the data in this table. to sum up: Subprints added in MySQL version 4.1, now supports quantities, rows, and tables. Comparison Operation Symbol = <> <<=>> = can be used in subqueries, and [NOT] IN and [NOT] EXISTS clause can be used. Table level subqueries can use short sentences any / some or all to compare. Subqueries can be used to modify data.
