Blog

From a question on Freenode.

SELECT * FROM theTable;

+----+----------+
| id | data     |
+----+----------+
|  1 | middle   |
|  2 | first    |
|  3 | showLast |
+----+----------+

Fair enough. Let’s order it by `data`.

SELECT * FROM theTable ORDER BY data;

+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

What??? Maybe the server didn’t quite understand.

SELECT * FROM theTable ORDER BY data ASC;

+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

How did that happen? I assure you I have not switched to some ‘reverse order’ collation.

SHOW CREATE TABLE theTable;

CREATE TABLE `thetable` (
  `id` int(11),
  `data` enum('showLast','middle','first')
)

Ah. `data` is an enum. ORDER BY on an enum orders by the enum index, not the string. You can order by CONCAT(data) or by CAST(data AS char). Are there better ways?

A bit of a quiz to see if you’re paying attention:

First, we’ll set up a couple tables with some auto increment fields:

CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));

Next, we’ll insert a row to one table:

INSERT INTO t1 VALUES (NULL);

Then, we’ll see what happens when we insert two rows using LAST_INSERT_ID():

INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);

What happens here? Did the insert go ok, or did we get an error? What happens if we now issue

SELECT * FROM t2;

No errors, because we defined the auto_increment field for t2 not as a primary or unique key, but just with index. Remember that LAST_INSERT_ID() only picks up the last value you inserted using NULL, and not explicitly defined values. So we get two copies of the value ‘1’ in our id field.

+----+-------+
| id | refT1 |
+----+-------+
|  1 |     1 | 
|  1 |     2 | 
+----+-------+

Straight quotes can be used like backticks around aliases IN SOME CASES (as of 5.0.15, at least). Observe:

CREATE TABLE myTable (id int, someData int);
INSERT INTO myTable VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2);

These three queries produce identical output:

SELECT * FROM myTable GROUP BY id;
SELECT * FROM myTable GROUP BY `id`;
SELECT * FROM myTable GROUP BY 'id';
(more…)

Here are a couple table definitions:

CREATE TABLE `users1` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);

CREATE TABLE `users2` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);

After inserting some data, we get the following row counts:

SELECT COUNT(*) FROM users1;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+

SELECT COUNT(*) FROM users2;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+


Now, for this query:

SELECT * FROM users1 UNION SELECT * FROM users2;

How many rows will be in the result set? (Choose the best answer)

a) Exactly 27
b) At least 12
c) At least 15
d) Between 1 and 27
e) Zero or more

d) Between 1 and 27. Default behavior for UNION is DISTINCT. If all 27 rows in the two tables are identical, there will be only 1 row in the result. If all are unique, there will be 27 rows.

CREATE TABLE myTable (
 id INT,
 string varchar(100) DEFAULT NULL
);
INSERT INTO myTable (id, string) VALUES (1, "First"), (2, NULL);
UPDATE myTable SET string = CONCAT(id, " has the value ", string);
SELECT * FROM myTable;

What is the output?

+------+-----------------------+
| id   | string                |
+------+-----------------------+
|    1 | 1 has the value First |
|    2 | NULL                  |
+------+-----------------------+

What would result from the following?

CREATE TABLE my_table (
   id INT
);
INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4);
SELECT * FROM my_table AS m WHERE my_table.id > 3;

Answer:

ERROR 1109 (42S02): Unknown table 'my_table' in where clause.


(because it uses an alias for my_table, we should have said “WHERE m.id > 3"