Mega Code Archive
Convert subqueries to JOINs
<![CDATA[
mysql>
mysql> CREATE TABLE jobs (
-> employee varchar(30)
-> ,title varchar(30)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO jobs VALUES ('Gordon Russell','Lecturer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO jobs VALUES ('Andrew Cumming','Teaching Fellow');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO jobs VALUES ('Jim Smith','Technician');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE ranks (
-> title varchar(30)
-> ,rank varchar(30)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO ranks VALUES ('Lecturer','LECT1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ranks VALUES ('Teaching Fellow','LECT2');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ranks VALUES ('Technician','TECH1');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE salary (
-> rank varchar(30)
-> ,payment DECIMAL(10,2)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO salary VALUES ('LECT1',2000.00);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO salary VALUES ('LECT2',3000.00);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO salary VALUES ('TECH1',5000.00);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO salary VALUES ('TECH2',6000.00);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from jobs;
+----------------+-----------------+
| employee | title |
+----------------+-----------------+
| Gordon Russell | Lecturer |
| Andrew Cumming | Teaching Fellow |
| Jim Smith | Technician |
+----------------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from ranks;
+-----------------+-------+
| title | rank |
+-----------------+-------+
| Lecturer | LECT1 |
| Teaching Fellow | LECT2 |
| Technician | TECH1 |
+-----------------+-------+
3 rows in set (0.00 sec)
mysql> select * from salary;
+-------+---------+
| rank | payment |
+-------+---------+
| LECT1 | 2000.00 |
| LECT2 | 3000.00 |
| TECH1 | 5000.00 |
| TECH2 | 6000.00 |
+-------+---------+
4 rows in set (0.00 sec)
mysql>
mysql> SELECT title FROM jobs WHERE employee = 'Andrew Cumming';
+-----------------+
| title |
+-----------------+
| Teaching Fellow |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT rank FROM ranks WHERE title = 'Teaching Fellow';
+-------+
| rank |
+-------+
| LECT2 |
+-------+
1 row in set (0.00 sec)
mysql> SELECT payment FROM salary WHERE rank = 'LECT2';
+---------+
| payment |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT payment FROM salary WHERE rank =
-> (SELECT rank FROM ranks WHERE title =
-> (SELECT title FROM jobs WHERE employee = 'Andrew Cumming'))
-> ;
+---------+
| payment |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT payment FROM salary,ranks,jobs
-> WHERE salary.rank = ranks.rank
-> AND ranks.title = jobs.title
-> AND jobs.employee = 'Andrew Cumming'
-> ;
+---------+
| payment |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT payment
-> FROM salary JOIN ranks ON (salary.rank = ranks.rank)
-> JOIN jobs ON (ranks.title = jobs.title)
-> WHERE jobs.employee = 'Andrew Cumming'
-> ;
+---------+
| payment |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT salary.rank FROM salary
-> WHERE rank NOT IN (SELECT rank FROM ranks)
-> ;
+-------+
| rank |
+-------+
| TECH2 |
+-------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT salary.rank
-> FROM salary LEFT OUTER JOIN ranks ON (salary.rank = ranks.rank)
-> WHERE ranks.rank IS NULL
-> ;
+-------+
| rank |
+-------+
| TECH2 |
+-------+
1 row in set (0.00 sec)
mysql>
mysql> DROP TABLE jobs;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE ranks;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE salary;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>