http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html
This is The Sixth of Our On-Going Series of Articles That Explain Some The New Features in Mysql 4.1, Which Is Now Available As a Generally-Available (Ga, or Production) Release.
Discuss this article in our online forums!
By Trudy Pelzer
Effective with MySQL Version 4.1, There, ass.
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');
The Following Query Uses a Join To Get All Available Information For Client Number 10:
SELECT
FName, Lname, City, Job, Company, Account_balance
From Clom Clients C, Firms F
WHERE C.CLNO = F.Clno
And c.clno = 10;
But it is not always possible to use a join to get the information you may need. For example, suppose you need all available information on the client with the largest account balance. The following query, which may look as if it should return the Required Information, Instead Returns an Error:
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 - invalid use of group function - is that the aggregate function, MAX, is disallowed in the WHERE clause as shown This is where the second method of getting data from multiple tables in a single query -. The subquery - comes To the rescue. in this Article, I'll Briefly Describe the Subquery FunctionAlity Added To MySQL in Version 4.1suBQueries Are Selects Inside ParentheSes
Simply put, a subquery is a SELECT statement that is written inside another SQL statement (which is often, but does not have to be, another SELECT). To distinguish the subquery (or inner query) from its enclosing query (or outer query) , IT Must Be Enclosed within ParentheSes. Here is an esample:
Select * from clients where clno in - Outer Query
(Select Clno from firms where city = 'leduc'); - Inner Query
This query will return all rows of the clients table which have the same clno value as the rows of the firms table having a city value equal to 'leduc'. To get the result, the DBMS first evaluates the inner query, to find the clno value for every row in the firms table where city is equal to 'leduc'. It then compares these clno values to the rows of the clients table, returning every row where the clno values match. Since there is only one row in firms which matches The Subquery Condition, The Subquery Example - IN Effect - Is Equivalent to this Query:
Select * from clients where clno = 10;
The Subquery Example Can Also, Of Course, Be Written As a Join:
SELECT
C.clno, Fname, Lname, Job, Account_balance
From Clom Clients C Inner Join Firms F Using (ClN)
WHERE city = 'leduc';
HOWEVER, AS ALREADY NOTED, The Same Can NOT BE SAID of this Subquery (Translation: Which Client Has THE HIGHEST Clno Value?): SELECT FNAME, LNAME FROM Clients Where ClNo =
SELECT MAX (CLNO) from firms;
If The Inner Query Returns An Empty Set, The Result of A Subquery May Appear To BE IncorRect. For Example, Consider this Query:
Select * from clients where clno =
(Select Clno from firms where city = 'gibbons');
If The Inner Query Is Run Alone, IT Is Clear That The Result IS ZERO ROWS: TheRE NO ROWS IN TO 'GIBBONS'. But "EMPTY SET" cannot be compared to the value of a color. The SQL Standard Thus Requires That The Result of A Subquery, Which Evaluates To Zero Rows, Is Null. And Since Nothing IS Equal To Null, The Query Returns An "EMPTY SET" Message (IE Zero Rows).
IT IS Common To Say That The Subquery Is Nested In The Nesting of Subqueries Withnin Other Subqueries, To A Great Depth.
Types of Subqueries
There Are Three Types of Subqueries, Distinguished from One Another by The Result - How Many Rows and Columns - They Return.
If a subquery can return exactly one column and one row, it is known as a scalar subquery. A scalar subquery is legal everywhere that a regular scalar value (eg a column value or literal) is legal in an SQL statement. It is usually found in a WHERE clause, immediately after a comparison operator. If a subquery can return multiple columns and exactly one row, it is known as a row subquery. A row subquery is a derivation of a scalar subquery and can thus be used anywhere that a scalar subquery can be used. Finally, if a subquery can return multiple columns and multiple rows, it is known as a table subquery. A table subquery is legal everywhere that a table reference is legal in an SQL statement, including the FROM clause of a SELECT . It, too, is usually found in a WHERE clause, immediately after an iN or EXISTS predicate or a quantified comparison operator. (A quantified comparison operator is a comparison operator used with either the SOME, ALL, or ANY quantifiers.) The difference Bo tween scalar and table subqueries can be subtle Here's a problem that arises when a subquery is written as a scalar subquery, but the subquery result contains multiple rows Assume our two tables have only these rows..:
Insert Into Clients Values
(10, 'Sam', 'Smith', 'Auditor', 5525.75);
Insert Into Firms Values
(10, 'ABC CO', 'LEDUC'), (30, 'GHI INC', 'Nisku');
Since The Firms Table Has Two Rows, This Query:
Select * from clients where clno <
SELECT Clom Firms;
Fails with: "Subquery Returns More Than 1 Row"
There are two solutions to this The first is to change the query to include a table subquery quantified by ANY, to compare the outer query results with any subquery value:. SELECT * FROM clients WHERE clno SELECT Clom Firms; In this case, the Comparison for the first client is false (10 <10), but is true for the second client (10 <30), Ando the Subquery Result IS "True" for ClNo 10. The rules for Any Are AS Follows: ANY returns "true" if the comparison operator is "true" for at least one row returned by the subquery. ANY returns "false" if the subquery returns zero rows or if the comparison operator is "false" for every row returned by the subquery . Some is a synonym for any; use in is equivalent to using = any. THE Second Solution To The Problem Is To Change The Query To Include A Table Subquery Quantified by All, To Compare The Outer Query Results with Every Subquery Value: Select * from clients where clno SELECT Clom Firms; In this case, the commit client and true for the second client - but this time, The Subquery Result is "false" and so the query returns zero rows. The rules for all Are: ALL returns "true" if the subquery returns zero rows or if the comparison operator is "true" for every row returned by the subquery. ALL returns "false" if the comparison operator is "false" for at least one row returned by the subquery . Does The Subquery Return AT Least One Row? Sometimes, the only information needed from a subquery is whether it returns any rows at all The [NOT] EXISTS predicate tests for a non-empty set EXISTS returns "true" if the subquery returns at least one row;.. Otherwise, it returns "false" NOT EXISTS is the complement - it returns "true" if the subquery returns zero rows;. otherwise, it returns "false" By tradition, a subquery following [NOT] EXISTS begins with SELECT * In this case, the.. asterisk (*) is not a shorthand for "list of all columns", instead it stands for "some column" -. and the result returned by each subquery is normally correlated with the result of the outer query to which it belongs Here's an extremely Trivial Example, Which Returns All Client Values: Select * from clients where exists SELECT * FROMs); The WHERE CLAUSE INLY EXAMPLE IS "TRUE" Only Because. But [NOT] EXISUALLY Used to form more complicated queries. Assume you have the folding tables: 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's An Example of The Classic Fort Question: 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)); THIS, TOESTING PASSENGERS ARE RIDING. To understand the result, consider That Smith Is in Car 20 and Jones in Car 25 - But That The Cars Table Doesn't Contain A Row for Car 25. This Means that there is one passenger - Jones - who is riding in a non-existent car (Of course, in a properly set up database, this situation could not exist;. one would define primary key / foreign key relationships between the two tables to ensure data integrity) The second NOT EXISTS subquery in the example, thus, is always "true" for passenger Jones.In addition, of course, there is one passenger -. Jones, again - who is not riding in car 20, and therefore The First NOTESTS SUBQUERY IN The Example IS "false". And Since The Result of The Query IS An Empty Set (Zero Rows) - There Are no Cars in Which Every Passenger is riding. Other Uses of Subqueries The SQL Standard, effective with SQL: 1999, requires increased subquery support, which MySQL provides The row subqueries alluded to earlier are an example Thus, it is now possible to compare multiple columns at a time..: SELECT ROW ('Smith', 'Auditor') = (SELECT LNAME, Job from Clients Where ClNo = 10); The subquery in this example returns a row containing the values 'smith' and 'auditor'. When these values are compared to the ROW values in the outer query, they are found to be equal and so the query returns 1 (true). You Can Also Put A Subquery, Rather Than A Simple Table Name (Those of You Familiar WIUERY): SELECT * FROM (Select * from clients where job like 'a%') as cl; to get the result of this query Evaluates The Outer SELECT. IN EFFECT, The Above Example Ends Up Being Interpreted AS: SELECT * from HL; WITH TABLE CL Being a Temporary Result Set Created with The Subquery: Select * from clients where job like 'a%'; When a Subquery Is Placed in The from Clause, The AS Data Changes with Subqueries Subqueries Have Other Use: The Data In The Database. That IS, You CAN Put A Subquery In A Delete, Insert, Update, or Replace Statement. Here is an esample: Update clients set account_balance = (Select Sum (Amount) from Accounts Where ClNo = Clients.Clno); ............. There is one caveat: it is not currently possible to modify a Table and select from the Same Table in A Subquery. Summary Subqueries Are New To MySQL in Version 4.1, Which Now Supports Scalar, Row, and Table Subqueries. The Usual Comparison Operators - = <> <=>> = - can be used with subspary, as can the [not] in and NOT] EXISTS PREDICES. TABLE SUBQUERIES CAN BE COME OR All. Subqueries can be used to make data. MySQL HAS ADDED STRUCTURE TO SQL!
