HomeMySQL Page 8 - Using Subqueries In MySQL (part 2)
A New Broom - MySQL
In this concluding segment of our MySQL subquery tutorial, find out how to do more with subqueries, including check for matching values with the IN operator, check for valid result sets with the EXISTS operator, derive new "virtual" tables for use in the FROM clause of outer queries, and UPDATE and DELETE records selectively.
Now for something a little off the beaten track. Let's suppose I'm on a "let's-clean-up-the-database" binge, and I need to check if the various tables are in sync with each other. For example, let's see if, for every customer, there exists at least one branch office in the "branches" table. The quickest way to do this is with a left join and an eyeball check for NULL values.
mysql> SELECT clients.cid, clients.cname, branches.bid, branches.bdesc
mysql> FROM
clients LEFT JOIN branches USING (cid);
+-----+-----------------------------+------+--------------------------------+
| cid | cname | bid | bdesc |
+-----+-----------------------------+------+--------------------------------+
| 101 | JV Real Estate | 1011 | Corporate HQ |
| 101 | JV Real Estate | 1012 | Accounting Department |
| 101 | JV Real Estate | 1013 | Customer Grievances Department |
| 102 | ABC Talent Agency | NULL | NULL |
| 103 | DMW Trading | 1031 | N Region HO |
| 103 | DMW Trading | 1032 | NE Region HO |
| 103 | DMW Trading | 1033 | NW Region HO |
| 104 | Rabbit Foods Inc | 1041 | Branch Office (East) |
| 104 | Rabbit Foods Inc | 1042 | Branch Office (West) |
| 110 | Sharp Eyes Detective Agency | 1101 | Head Office |
+-----+-----------------------------+------+--------------------------------+
10 rows in set (0.00 sec)
Hmmm. It's fairly obvious that we have a record for the customer "ABC Talent Agency", but no corresponding record for one or more branch offices. Since I've decided this is a major offense, I can nuke the client record with a fast DELETE using the customer ID,
mysql> DELETE FROM clients WHERE cid = 102;
Query OK, 1 row affected (0.01 sec)
or I could combine the two steps above into one using the following subquery:
mysql> DELETE FROM clients WHERE cid = (SELECT clients.cid FROM clients
LEFT JOIN branches USING (cid) WHERE bid IS NULL);
ERROR 1093: You can't specify target table 'clients' for update in FROM clause
Interesting. Do you know why?
It's actually pretty simple (and even makes sense when you think about it). MySQL won't let you delete or update a table's data if you're simultaneously reading that same data with a subquery, as doing so opens up a window where your subquery might reference rows which have already been deleted or altered. Therefore, the table named in an outer DELETE or UPDATE statement cannot appear in the FROM clause of an inner subquery (which is what MySQL more tersely said in its error message above).
Thus, as per the SQL standard, the query above is actually illegal, and should be rewritten using an EXISTS test as follows:
mysql> DELETE FROM clients WHERE NOT EXISTS (SELECT * FROM branches
mysql> WHERE
branches.cid = clients.cid);
Query OK, 1 row affected (0.11 sec)
A quick glance at the "clients" table will reveal that the offending entry has now been removed.
