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 t1.name = t2.name WHERE t1.id = t2.id;
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;

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.