[amalashkevich]

Actually Pony can transform subqueries into JOINs in most of the cases. But when it translates the 'in' operator of a generator expression, it produces a subquery with 'IN', because otherwise the programmer can be confused by the fact that the resulted SQL looks too different from the Python code. Pony allows you to use the 'JOIN' hint in order to make it to use JOINs instead of subquiries. In the example below Pony produces a subquery when it translates the `in` section from the generator:

    >>> from pony.orm.examples.estore import *
    >>> select(c for c in Customer if 'iPad' in c.orders.items.product.name)[:]

    SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
    FROM "Customer" "c"
    WHERE 'iPad' IN (
        SELECT "product-1"."name"
        FROM "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
        WHERE "c"."id" = "order-1"."customer"
          AND "order-1"."id" = "orderitem-1"."order"
          AND "orderitem-1"."product" = "product-1"."id"
        )

But you can tell Pony to use JOIN instead of a subquery by wrapping the 'in' section into a 'JOIN' hint:

    >>> select(c for c in Customer if JOIN('iPad' in c.orders.items.product.name))[:]
	
    SELECT DISTINCT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
    FROM "Customer" "c", "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
    WHERE "product-1"."name" = 'iPad'
      AND "c"."id" = "order-1"."customer"
      AND "order-1"."id" = "orderitem-1"."order"
      AND "orderitem-1"."product" = "product-1"."id"