You are currently viewing archive for 14 January 2008
Category: Study
Posted by: hajime osako
Types of Subqueries

Scalar subqueires return a single value.
Row subqueries return a single row.
Colulmn subqueires return a single column.
Table subqueires return a result set.

Multiple table UPDATE and DELETE statements

UPDATE t1, t2 SET = WHERE =;
DELETE t1 FROM t1, t2 WHERE =;

The ODER BY and LIMIT clauses are not allowed for multiple table UPDATE and DELETE statements.

Using ALL, ANY, and SOME

SELECT Name, Population FROM Country WHERE Population < ALL (SELECT AVG(Population) FROM Country GROUP BY Continent) ORDER BY Name;

ANY and SOME may be equivalent to IN except IN can't be combined with any comparison operators such as '=' or '><'.
NOT IN is an alias of '>< ALL'.

Comparison Using Row Subqueries

The row subquery must return a single row.

SELECT City.Name FROM City WHERE [ROW](City.ID, City.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland');

* ROW is optional.

Row constructors can be used only for equality comparison using the = operator.

Category: Linux tips
Posted by: hajime osako