We define what subquery means and show the different subqueries that can be done.
A subquery is a SELECT statement nested within a SELECT, SELECT…INTO, INSERT…INTO, DELETE, or UPDATE statement or within another subquery. You can use three forms of syntax to create a subquery:
comparison (sql statement) expression IN (sql statement) EXISTS (sql statement)
Where:
comparisonIs an expression and a comparison operator that compares the expression with the result of the subquery. expressionIs an expression by which the result set of the subquery is searched. SQL statement It is a SELECT statement, which follows the same format and rules as any other SELECT statement. It must go in parentheses.
A subquery can be used instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, a SELECT statement is used to provide a set of one or more specified values to evaluate in the WHERE or HAVING clause expression.
The ANY or SOME predicate, which are synonymous, can be used to retrieve records from the main query that satisfy the comparison with any other records retrieved in the subquery. The following example returns all products whose unit price is greater than any product sold at a discount of 25 percent or more:
SELECT *
DESDE
Products
WHERE
Unit price
ANY
(
select
Unit price
DESDE
Order Detail
WHERE
Discount = 0 .25
)
The ALL predicate is used to retrieve only those records from the main query that satisfy the comparison with all the records retrieved in the subquery. If ANY is changed to ALL in the example above, the query will return only those products whose unit price is greater than all products sold at a discount of 25 percent or more. This is much more restrictive.
The IN predicate is used to retrieve only those records in the main query for which some records in the subquery contain an equal value. The following example returns all products sold at a discount of 25 percent or more:
SELECT *
DESDE
Products
WHERE
ProductID
IN
(
select
ProductID
DESDE
Order Detail
WHERE
Discount = 0.25
)
Conversely, NOT IN can be used to retrieve only those records from the main query for which there are no records from the subquery that contain an equal value.
The EXISTS predicate (with the optional NOT keyword) is used in true/false comparisons to determine if the subquery returns any records. Suppose we want to retrieve all those customers who have placed at least one order:
select
Customers.Company, Customers.Phone
DESDE
Customers
WHERE EXISTS (
select
DESDE
orders
WHERE
Orders.IdOrder = Customers.IdCustomer
)
This query is equivalent to this one:
select
Customers.Company, Customers.Phone
DESDE
Customers
WHERE
idClientes
IN
(
select
Orders.CustomerId
DESDE
orders
)
You can also use table name aliases in a subquery to refer to tables listed in the FROM clause outside of the subquery. The following example returns the names of employees whose salary is equal to or greater than the median salary of all employees with the same title. The Employees table has been given the alias T1:
select
Last name, First name, Title, Salary
DESDE
AS employees T1
WHERE
salary =
(
select
Avg(Salary)
DESDE
Employees
WHERE
T1.Title = Employees.Title
)
ORDER BY Title
In the example above, the AS keyword is optional.
select
Surname, Name, Position, Salary
DESDE
Employees
WHERE
Position LIKE ‘Agent Come*’
AND
ALL salary
(
select
Salary
DESDE
Employees
WHERE
Position LIKE ‘*Boss*’
OR
Title LIKE ‘*Director*’
)
(Gets a list with the name, title, and salary of all sales agents whose salary is greater than all managers and directors.)
SELECT DISTINCT
ProductName, Price_Unit
DESDE
Products
WHERE
PriceUnit =
(
select
Unit price
DESDE
Products
WHERE
ProductName = ‘Aniseed syrup’
)
(Obtains a list with the name and unit price of all products with the same price as aniseed syrup.)
SELECT DISTINCT
NameContact, NameCompany, PositionContact, Telephone
DESDE
Customers
WHERE
CustomerID IN (
SELECT DISTINCT CustomerID
FROM Orders
WHERE OrderDate )
(Gets a list of the companies and contacts of all customers who have placed an order in the second quarter of 1993.)
select
Name surname
DESDE
AS E Employees
WHERE DOES IT EXIST?
(
SELECT *
DESDE
Orders AS OR
WHERE O.EmployeeID = E.EmployeeID
)
(Select the name of all employees who have reserved at least one order.)
SELECT DISTINCT
Orders.Id_Product, Orders.Quantity,
(
select
Products.Name
DESDE
Products
WHERE
Products.IdProduct = Orders.IdProduct
) AS TheProduct
DESDE
orders
WHERE
Orders.Quantity = 150
ORDER BY
Orders.Id_Product
(Retrieves the Product Code and Quantity Ordered from the orders table, extracting the product name from the products table.)
select
NumFlight, Seats
DESDE
flights
WHERE
Origin = ‘Madrid’
AND Exists (
SELECT T1.FlightNum FROM Flights AS T1
WHERE T1.PlazasLibres > 0 AND T1.NumVuelo=Flights.NumVuelo)
(Recovers flight numbers and capacities of those flights to Madrid and free seats
Now suppose we have a table with the identifiers of all our products and the stock of each of them. In another table are all the orders that we have pending to serve. It is about finding out which products we cannot serve due to lack of stock.
select
Backorders.Name
DESDE
OrdersPending
GROUP BY
Backorders.Name
HAVING
SUM(PendingOrders.Quantity (
select
Products.Stock
DESDE
Products
WHERE
Products.IdProduct = PendingOrders.IdProduct
)
)
Suppose that in our table of employees we want to find all women whose age is older than any man:
select
Employees.Name
DESDE
Employees
WHERE
Sex = ‘M’ AND Age > ANY
(SELECT Employees.Age FROM Employees WHERE Sex =’H’)
or what would be the same:
select
Employees.Name
DESDE
Employees
WHERE
Sex = ‘M’ AND Age >
(SELECT Max( Employees.Age )FROM Employees WHERE Sex =’H’)
The following table shows some examples of the ANY and ALL operators
Value 1 Operator Value 2 Result 3 > ANY (2,5,7) True 3 = ANY (2,5,7) False 3 = ANY (2,3,5,7) True 3 > ALL(2,5,7) False 3 < ALL(5,6,7)False
The =ANY operation is equivalent to the IN operator, both return the same result.
Facebook Comments
Disqus