The SubQuery

only for RuBoard

A SubQuery is a SELECT query that's nested inside a top-level SELECT , DELETE , UPDATE or INSERT statement, a HAVING or WHERE clause, or inside another SubQuery. A SubQuery must be constructed so it only will return a single value. Otherwise you will get an error because it is executed for each row returned in the result set and one row cannot have multiple values for one field. The following example contains a SubQuery that will be used to select all the rows from the Products table that are supplied by Exotic Liquids:

 1: SELECT * 2: FROM Products 3: WHERE SupplierID = 4: (SELECT SupplierID FROM Suppliers 5: WHERE CompanyName = 'Exotic Liquids') 

Essentially, you're doing a SELECT statement that will retrieve all fields from the Products table. Then you are stating that you only want to see Exotic Liquids products. This is achieved by stating that the SupplierID must be equal to the results of a SubQuery against the Suppliers table, which returns Exotic Liquids SupplierID value. Now try the following code example with a SubQuery that returns more than one row in its result set.

 SELECT * FROM Products WHERE SupplierID = (SELECT SupplierID FROM Suppliers) 

When the preceding code is executed you will get the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression .

Not only can a SubQuery be used in the FROM clause, it can also be used in the SELECT clause. The following example returns all the rows in the Suppliers table for Exotic Liquids. Additionally, it will return how many products they currently supply by using the SubQuery :

 1: SELECT *,(SELECT Count(*) FROM Products WHERE SupplierID = '1') AS [Number Of 2: Products] 3: FROM Suppliers 4: Where SupplierID = '1' 

The result of your SubQuery doesn't have a field name assigned to it by default, but you can assign it your own name by using the AS clause. In this example I used "Number Of Products" as the field name. This clause can be used to change the name of a result set column regardless of whether column was created by a SubQuery . The new name is put into brackets because there are spaces in the name and an error would occur. Without the brackets, you'll receive an error.

only for RuBoard