db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: Derby 10.5.3: subquery performance using "IN"
Date Tue, 10 Nov 2009 16:09:19 GMT
> --------------------------------------------
> --  This is the real query (with subquery)
> --------------------------------------------
> 
> SELECT em.end_item_meter_id
> FROM   end_item_meter em
> WHERE  em.end_Item_meter_id IN
>     (
>     SELECT
>         mr.end_item_meter_id
>     FROM
>         Meter_Reading mr
>             INNER JOIN End_Item_Event eie
>                 ON eie.end_Item_Event_Id = mr.end_Item_event_Id
>             INNER JOIN End_Item_Inventory eii
>                 ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id
>     WHERE
>         eii.End_Item_Inventory_Id = 6061799
>     );

Is it possible to rewrite this as a top-level join, without using a subquery?

Something along the lines of:

  SELECT em.end_item_meter_id
  FROM   end_item_meter em
      inner join  Meter_Reading mr
               on em.end_Item_meter_id = mr.end_item_meter_id
      INNER JOIN End_Item_Event eie
               ON eie.end_Item_Event_Id = mr.end_Item_event_Id
      INNER JOIN End_Item_Inventory eii
               ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id
      WHERE
          eii.End_Item_Inventory_Id = 6061799

Does that query produce the right results for you?

thanks,

bryan


Mime
View raw message