---
tags:
  - mysql
---
Optimizing a query with subselects into a "flat" query with just additional joins. This query finds rows that are missing for instances.

```
select *
from instance_system_metadata ism1
where ism1.instance_uuid in (
   select i.uuid
   from instances i
   left join instance_system_metadata ism
     on ism.instance_uuid = i.uuid and ism.key = "image_os_admin_user"
   where i.deleted_at is null and ism.id is null
)
and ism1.key = "image_hw_qemu_guest_agent";
```

Can be rewritten more nicely with an additional join
```
select i.uuid
from instances i
left join instance_system_metadata ism_agent
  on ism_agent.instance_uuid = i.uuid
  and ism_agent.key = "image_hw_qemu_guest_agent"
left join instance_system_metadata ism_user
  on ism_user.instance_uuid = i.uuid
  and ism_user.key = "image_os_admin_user"
where i.deleted is null
  and ism_user.key is null;
```