• 6 Справочник по языку MySQL
  • 6.3 Функции, используемые в операторах SELECT и WHERE
    • 6.3.1 Операторы и функции общего назначения
      • 6.3.1.1 Круглые скобки
      • 6.3.1.2 Операторы сравнения
      • 6.3.1.3 Логические операторы
      • 6.3.1.4 Функции потока управления программой

Additional languages

• English
• French
• German
• Japanese
• Portuguese

Buy this Reference Manual in softcover from Barnes & Noble!

Additional formats

• HTML, one page per chapter, tarball
• HTML, all on one page, tarball
• HTML, all on one page, ZIP

User Comments

If you are searching data and you want to show the result of the search with one column above the other you can use the following (difficult to explain, just read the example):

keyword is "John"

SELECT id, lastname, firstname, IF(lastname LIKE '%John%', 0, 1) as ordervalue FROM customers WHERE lastname LIKE '%John%' OR firstname LIKE '%John%' ORDER BY ordervalue, lastname, firstname;

Now you will first get all the entries where the lastname contains "John" (Johnson) alphabetically listed and then the entries where the firstname contains "John".

Don't forget that the clauses work in the WHERE part, too! Consider this example:
SELECT * FROM table WHERE IF('$ids'='', type IS NOT NULL, type IN ($ids));
This query (constructed from within PHP) on a table with type defined as NOT NULL returns rows where type's value doesn't matter if $ids contains an empty string (note the apostrophes), however it does matter if $ids contains a string like "2,4". Then only rows where type is IN (2,4), i.e. type is 2 or 4, are returned.
I use this construct in a query that depends on whether the client has cookies set or not. Works fine!

If you have a table of categories keyed by a `topic_id` and a subcategory is simply defined as a category with a `parent_id` referring to another category's `topic_id`, and you have a second table of items (perhaps products) that fit into either a single category or single sub category you can easily print out the item's categegory and sub category (if it has one) in one nice query.

SELECT a.*,
CASE
WHEN s.`parent_id` IS NULL
THEN s.name
ELSE t.name
END AS `topic_name` ,
CASE
WHEN s.`parent_id` IS NOT NULL
THEN s.name
END AS `subtopic_name`
FROM `db`.`items` a
LEFT JOIN `db`.`category_topics` s ON a.topic_id = s.topic_id
LEFT JOIN `db`.`category_topics` t ON t.topic_id = s.parent_id

Note that `a` is the table of items. `a`.`topic_id` refers to a an entry in the `category_topics` table. `s` is the inital join of the `category_topics` on the `a`.`topic_id`. Next we join `category_topics` again as table `t` to basically check if there is a parent category entry. The case statement says that if there is a parent category entry, then that is the actualy topic to use, not the entry from the initial join. Likewise, if there is not an parent category entry then then topic is the initally selected one from s. A second case is needed to determine the actual subtopic.

This is my first stab at Flow control in SQL, since i usually just use PHP. Hope this helps others.

Blaine Garrett
Webmaster of the Art Attack

if you're doing a LEFT JOIN with tables in a "binary" way such that the return is either a NULL or some other value indicating that a record is "on" or "off", you will need to do something like this if you wish to sort by that field as an "on/off" value, or else your sorting will take the "value" into effect (obviously).

SELECT p.scan_id, f.family, p.name, IF( pe.plugin_id IS NULL, '0','1') as exclude FROM Swordfish.pluginlist as p LEFT JOIN plugin_family as f on f.id=p.family LEFT JOIN plugin_exclusion_t as pe ON pe.company_id = 1 AND pe.plugin_id = p.scan_id ORDER BY exclude DESC, p.family, p.name

this would lump all the excluded ones at the top, then sorted by family and name. otherwise, without the check, it would sort by the excluded _value_ first, and this is NOT the result you wanted.

Here's something that utilises if(1,2,3). It's part of the display calendar for a small art gallery. There are displays once in two months and they last about 1 month.

The meaning of this query is to find either the current display or, if currently there is no display, find the next coming-up display.

1. we start with the usual stuff:

SELECT display_id, startdate, enddate, artist, display_name, description, pict1descr, pict2descr, pict3descr,

2. then we have the peculiar looking if-statement:

if(TO_DAYS(now()) BETWEEN TO_DAYS(startdate) AND TO_DAYS(enddate),0,1) as goingon

3. and the rest:

FROM calendar where TO_DAYS(now()) BETWEEN TO_DAYS(startdate) AND TO_DAYS(enddate) OR TO_DAYS(now()) <= TO_DAYS(startdate) ORDER BY goingon, startdate asc LIMIT 1";

1. self-explanatory.

2. if we try to put the if statement in words: 'create an extra column for each entry (in this case in the end there is only one because of LIMIT 1) that has either 1 or 0 depending on whether TO_DAYS(now()) is between the 'startdate' and 'enddate' of particular entry.

3. Then we find every entry where TO_DAYS(now()) is BETWEEN 'startdate' AND 'enddate' OR TO_DAYS(now()) is either smaller or equal to 'startdate'. Then we sort those little friends of ours so that on top of the list is either the display that is currently going on (that is, startdate is smaller and enddate is bigger than today's date) or the one that is the next display to start (that is, startdate is equal to or bigger than today's date). They are in ascending order with top priority on the 'goingon' column, followed by the 'startdate' column. Then we chop off the rest, cause we only need one entry.

I first thought that i couldn't put this into one query, which would suck 'cause i already got two other queries on that page. I really enjoy the straight 45-degree learning curve of MySQL. Thanks, Documentation and Jochem Claver!

comments/corrections please email tapani.jalonen@arcada.fi

Add your own comment.