OdenData

MySQL Derived tables and where clauses

by Mark Nielsen
Copyright July 2023

Using derived tables and joins can be a problem. Mainly, you can't put limit inside a join if the 2nd table is a derived table (made from a query itself). Instead you can put it in the where clause.

---

Sample Queries

create database if not exists temp;
use temp;
-- 2nd highest no item cost
-- items with greater than avg of its type

drop table if exists cost_items;
CREATE TABLE cost_items (
  id INT PRIMARY KEY,
  amount int,
  item varchar(255)
);

insert into cost_items values (1,1,"pencil"), (2,10,"pencil"), (3,20,"pencil"),
(4,2,"toaster"), (5,20,"toaster"), (6,30,"toaster"),
(7,3,"box"), (8,30,"box"), (9,40,"box");

-- 2nd highest cost for an item
select c1.id, c1.item, c1.amount
   from cost_items as c1
    where c1.id = (
      select c2.id from cost_items as c2 where c1.item = c2.item order by c2.id limit 1,1 )
;

-- items with greater than avg cost of its type

select c1.id, c1.item, c1.amount
   from cost_items as c1
    where c1.amount > (
        select avg(amount) from cost_items as c2 where c1.item=c2.item
         );

Expected output :

+----+---------+--------+
| id | item    | amount |
+----+---------+--------+
|  2 | pencil  |     10 |
|  5 | toaster |     20 |
|  8 | box     |     30 |
+----+---------+--------+
3 rows in set (0.00 sec)

+----+---------+--------+
| id | item    | amount |
+----+---------+--------+
|  3 | pencil  |     20 |
|  5 | toaster |     20 |
|  6 | toaster |     30 |
|  8 | box     |     30 |
|  9 | box     |     40 |
+----+---------+--------+
5 rows in set (0.00 sec)

---