Delphi Database, Delphi Components from ComponentAce
Products Download Order Contact us
Subqueries
Previous  Top  Next



Absolute Database SQL Engine supports correlated and non-correlated subqueries in SELECT, UPDATE and DELETE statements.
You can use the following types of subqueris:

Nested queries in the SELECT FROM clause

Usually SELECT uses tables to get records, but it is possible to use a subquery instead of a table.

Example:
/* The subquery retrieves count of unique names in table "Employee", then function "count" counts quantity of these names.*/  
SELECT COUNT(*) UniqueNamesCount FROM (SELECT DISTINCT FirstName FROM Employee)  

Subqueries in the WHERE clause

A subquery works like a search condition to restrict the number of rows returned by the outer, or "parent" query.

Examples:

/* The query returns all venues where no event will take place. */  
SELECT Venue FROM venues v WHERE NOT EXISTS (SELECT * FROM events WHERE VenueNo=v.VenueNo)  

/* The query returns only those venues where some event will occur.*/  
SELECT Venue FROM venues WHERE VenueNo IN (SELECT VenueNo FROM events)  

/* This query returns events that will occur on venues with capacity lower than in venues 3 and 4.*/  
SELECT e.Event_Name, v.Capacity FROM events e, venues v WHERE e.VenueNo=v.VenueNo AND  
v.Capacity < ALL (SELECT Capacity FROM venues WHERE VenueNo BETWEEN 3 AND 4)  

/*This query returns events that will occur on venues with capacity more than in venue 3 or 4.*/  
SELECT e.Event_Name, v.Capacity FROM events e, venues v WHERE e.VenueNo=v.VenueNo AND  
v.Capacity > ANY (SELECT Capacity FROM venues WHERE VenueNo BETWEEN 3 AND 4)  

/* This query shows all venues where only one event will occur.*/  
SELECT Venue FROM venues v WHERE 1 = (SELECT COUNT(*) FROM events WHERE VenueNo=v.VenueNo)  

Subqueries in the SELECT fields list

A subquery works like an advanced expression.

Example:

/* For each entry in table "Venues" the query counts quantity of events from table "Events" and places this number in a column named "EventsCount".*/  
SELECT Venue, (SELECT COUNT(*) FROM Events WHERE VenueNo = v.VenueNo) EventsCount FROM Venues v ORDER BY EventsCount DESC  

Subqueries in the UPDATE statement

A subquery works like an advanced expression allowing to assign data from another table.

Examples:

/* The query works as follows. First, only those entries are picked that have "Ticket_price" 5 of less. Then corresponding venue is taken, its capacity is multiplied by two and set as the new ticket price. Notice that NULL fields are not affected.*/  
UPDATE Events e SET Ticket_price = (SELECT Capacity FROM Venues WHERE VenueNo=e.VenueNo)*2 WHERE Ticket_price <= 5  
 
UPDATE Orders SET (ShipToAddr1,ShipToAddr2) = (SELECT Addr1, Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)  


        © 2003 - 2017 ComponentAce  | .net zip component | barcode for .net | delphi zip component | delphi database Feb 19, 2017