Convert subqueries to JOINs : Join Select « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Join Select

Convert subqueries to JOINs

    
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>

   
    
    
    
  

Related examples in the same category