Learning Mysql

The
ALL
keyword has a few tricks and traps If it’s false for any value, it’s false. Suppose that table a
contains a row with the value 14. Suppose table b
contains the values 16, 1, and NULL. If you check whether the value in a
is greater than
ALL
values in b, you’ll get false, since 14 isn’t greater than 16. It doesn’t matter that the other values are 1 and NULL If it isn’t false for any value, it isn’t true unless it’s true for all values. Suppose that table a
again contains 14, and suppose b
contains 1 and NULL. If you check whether the value in a
is greater than
ALL
values in b, you’ll get
UNKNOWN
(neither true or false)
because it can’t be determined whether
NULL
is greater than or less than 14.
• If the table in the subquery is empty, the result is always true. Hence, if a
contains
14 and bis empty, you’ll get true when you check if the value in a
is greater than
ALL
values in b
When using the
ALL
keyword, be very careful with tables that can have
NULL
values in columns consider disallowing
NULL
values in such cases. Also, be careful with empty tables.
Writing row subqueries
In the previous examples, the subquery returned a single, scalar value (such as an artist_id
) or a set of values from one column (such as all of the engineer_name values).
This section describes another type of subquery, the row subquery that works with multiple columns from multiple rows.
Suppose you’re interested in whether an engineer has been a producer for the same length of time. To answer this need, you must match both names and years. You can easily write this as a join query:
mysql> SELECT producer_name, producer.years FROM

Download 4.24 Mb.

Share with your friends: