Tag Archives: sql

Subqueries in MySQL

Posted by on December 11, 2010 No comments

MySQL has an interesting feature called subquery. It lets you to use SELECT statement within another query.

Imagine you have two tables named countries and capitals you want select capital names and country names without join statement.

The tables are as follow:

mysql> SELECT * FROM countries;
+------+------------+
| code | name       |
+------+------------+
|    1 | Azerbaijan |
|    2 | Japan      |
|    3 | Germany    |
|    4 | USA        |
|    6 | Turkey     |
+------+------------+
 
mysql> SELECT * FROM capitals;
+---------+------------+
| country | capital    |
+---------+------------+
|       1 | Baku       |
|       2 | Tokyo      |
|       3 | Berlin     |
|       4 | Washington |
|       6 | Ankara     |
+---------+------------+

Now we need show capitals and country names according to country code.  You can do it with using JOIN or VIEW etc. And also you can simply use subquery like this:

mysql> SELECT capital,(SELECT name FROM countries WHERE code=country) AS country FROM capitals;
+------------+------------+
| capital    | country    |
+------------+------------+
| Baku       | Azerbaijan |
| Tokyo      | Japan      |
| Berlin     | Germany    |
| Washington | USA        |
| Ankara     | Turkey     |
+------------+------------+

This method is not too good for performance point-view, but, you can use it in some situations.

Reference:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Regular expressions in SQL queries

Posted by on September 14, 2009 No comments

Regular expressions are useful for parsing string, matching patterns, validating etc. If we talk about strings so, I think it’s good idea using it in SQL queries.

Let we have table users:

mysql> SELECT * FROM users;
+----+-----------+----------+----------------------+
| id | first     | last     | email                |
+----+-----------+----------+----------------------+
|  1 | John      | SMITH    | jsmith@example.az    |
|  2 | Bill      | Gates    | bgates@example.ru    |
|  3 | Steve     | Jobs     | sjobs@example.de     |
|  4 | Sylvester | Stallone | stallone@example.com |
|  5 | Chuck     | Norris   | cnorris@example.net  |
|  6 | Bruce     | Lee      | blee@example1.net    |
+----+-----------+----------+----------------------+
6 rows in set (0.00 sec)

Now we want to get users whose email ends with three-letter TLD (.com,.net,.org etc). We can do this as follows:

mysql> SELECT * FROM users WHERE email LIKE '%.___';
+----+-----------+----------+----------------------+
| id | first     | last     | email                |
+----+-----------+----------+----------------------+
|  4 | Sylvester | Stallone | stallone@example.com |
|  5 | Chuck     | Norris   | cnorris@example.net  |
|  6 | Bruce     | Lee      | blee@example1.net    |
+----+-----------+----------+----------------------+
3 rows in set (0.00 sec)

Well.

What can we do, if we need get users whose email ends with digit dot three-letter TLD(blee@example1.net)? ooops.

I’ll not lie, I don’t know how to do it with ANSI SQL and I’ll use RDBMS specific functions. This example works for MySQL:

mysql> SELECT * FROM users WHERE email REGEXP '^.+[0-9]\.[a-z]{3}$';
+----+-------+------+-------------------+
| id | first | last | email             |
+----+-------+------+-------------------+
|  6 | Bruce | Lee  | blee@example1.net |
+----+-------+------+-------------------+
1 row in set (0.00 sec)

For Oracle it will as follow:

SELECT * FROM users WHERE REGEXP_LIKE(email,'^.+[0-9]\.[a-z]{3}$')

Thanks for my co-worker Mahir for the idea ;)

Reference:
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
http://dev.mysql.com/doc/refman/5.1/en/regexp.html