Get all the products, and putting a specific order's product first

Question

I have 3 tables: products, orders and orderLines(order_id, product_id).

I have an sql query to figure out which seems nearly impossible to do in only one query.
Is there a way to have in only one query:

• All the products but showning a specific order's products first; which means that: for an order A: show product1, product2.. present in orderA's orderLines first, than the following products (not ordered) are shown next.

PS: I know it's possible to achieve this with a union of two queries, but it would be better to have it done in only one query.

Answer 1

You can put a subquery in the order by clause. In this case, an exists subquery is what you need:

select p.*
from products p
order by (exists (select 1
                  from orderlines ol
                  where p.productid = ol.productid and o.orderid = ORDERA
                 )
         ) desc;