|
|
|
|
| |
SELECT * FROM president WHERE birth = MIN(birth);
That doesn't work because you can't use aggregates in WHERE clauses. (The WHERE clause determines which records to select, but the value of MIN() isn't known until after the records have already been selected.) However, you can use a subselect to produce the minimum birth date as follows:
SELECT * FROM president
WHERE birth = (SELECT MIN(birth) FROM president);
EXISTS and NOT EXISTS subselects. These forms of subselects work by passing values from the outer query to the inner one to see whether they match the conditions specified in the inner query. For this reason, you'll need to qualify column names with table names if they are ambiguous (appear in more than one table). EXISTS and NOT EXISTS subselects are useful for finding records in one table that match or don't match records in another.
Refer once again to our t1 and t2 tables:
The following query identifies matches between the tables—that is, values that are present in both:
mysql> SELECT i1 FROM t1
-> WHERE EXISTS (SELECT * FROM t2 WHERE t1.i1 = t2.i2);
With these forms of subselect, the inner query uses * as the output column list. There's no need to name columns explicitly because the inner query is assessed as true or false based on whether or not it returns rows, not based on the particular values that the rows may contain. In MySQL, you can actually write pretty much anything for the column selection list, but if you want to make it explicit that you're returning a true value when the inner SELECT succeeds, you might write the queries like this:
SELECT i1 FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.i1 = t2.i2);
SELECT i1 FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.i1 = t2.i2);
IN and NOT IN subselects. The IN and NOT IN forms of subselect should return a single column of values from the inner SELECT to be evaluated in a comparison in the outer SELECT. For example, the preceding EXISTS and NOT EXISTS queries can be written using IN and NOT IN syntax as follows:
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
