Delivery geolocations query I have the below MySQL query that does exactly what I need, but I am sure there are better queries that do the same thing.

If you see something very ugly or bad and have any suggestions, I will be very glad to hear them.

SELECT c.`ID_COURIER`, c.`NAME`,
    c.`NICKNAME`, c.`AREA_CODE`, 
    c.`PHONE_NUMBER`, d.`ID_DELIVERY`, 
    d.`DESCRIPTION`, d.`START_DATE`, 
    d.`END_DATE`, d.`CANCELED`, 
    r.`LAT`, r.`LNG`, 
    (SELECT COUNT(DISTINCT `ID_ORDER`) FROM `ORDERS` WHERE `ID_DELIVERY` = d.`ID_DELIVERY` AND `DELIVERY_DATE` IS NULL AND `CANCELED` = false) as REMAINING_ORDERS 
FROM `COURIER` as c 
    LEFT JOIN `DELIVERY` as d USING (`ID_COURIER`) 
    LEFT JOIN `GEOLOCATIONS` as r ON r.`ID_DELIVERY` = d.`ID_DELIVERY` 
WHERE c.`ID_COMPANY` = ? AND c.`DISABLED` = false
    AND (
        CASE WHEN (SELECT MAX(`START_DATE`) 
                       FROM `DELIVERY` 
                       WHERE `ID_COURIER` = c.`ID_COURIER`) 
                 IS NULL THEN d.`START_DATE` IS NULL 
                 ELSE d.`START_DATE` = (SELECT MAX(`START_DATE`) 
                                        FROM `DELIVERY` 
                                        WHERE `ID_COURIER` = c.`ID_COURIER`) 
            END )
    AND (
        CASE WHEN (SELECT MAX(`ID_GEOLOCATION`) 
                       FROM `GEOLOCATIONS` 
                       WHERE `ID_DELIVERY` = d.`ID_DELIVERY`) 
                     IS NULL THEN r.`ID_GEOLOCATION` IS NULL
                   ELSE r.`ID_GEOLOCATION` = (SELECT MAX(`ID_GEOLOCATION`) 
                                               FROM `GEOLOCATIONS` 
                                               WHERE `ID_DELIVERY` = d.`ID_DELIVERY`)
            END)
GROUP BY c.`ID_COURIER`
ORDER BY d.`START_DATE` DESC
Top to bottom, here we go.


COUNT(DISTINCT ID_ORDER) 

This DISTINCT aggregation seems superfluous, if ID_ORDER is a true identity column then there should not be duplicates. This will improve performance.
LEFT JOIN DELIVERY AS d USING (ID_COURIER)

I'm not a big fan of the USING keyword as I find it ambiguous. Agreed it's a useful shorthand but to the next person going back to read your code they would have to go and check that both tables have a ID_COURIER column. Plus if one of the columns got renamed at any point it would be more difficult to find the bug.

My suggestion:

LEFT JOIN `DELIVERY` AS d ON c.ID_DELIVERY = d.ID_DELIVERY

WHERE c.ID_COMPANY = ?

This doesn't seem to have any purpose I'm sure you could take it out with no difference. 
CASE WHEN (SELECT MAX(START_DATE) and the rest of the statement. 

This I find very unusual and not optimal for performance. I'm not sure I completely understand what you are trying to do but I'll give it my best shot. You may want to instead build this into your select. Here is what I suggest for the complete script. I made some edits to the layout to make it easier to read. If I misunderstood the intention of your CASE statements let me know and I can revise.  




SELECT     
    c.ID_COURIER,     
    c.NAME,
    c.NICKNAME, 
    c.AREA_CODE, 
    c.PHONE_NUMBER, 
-- This will return NULL if ID_GEOLOCATION is NULL
    (SELECT MAX(ID_GEOLOCATION) FROM GEOLOCATIONS) AS 'ID_GEOLOCATION',
    d.DESCRIPTION,     
-- This will return NULL if START_DATE is NULL
    (SELECT MAX(START_DATE) FROM ORDERS) AS 'START_DATE',
    d.END_DATE,
    d.CANCELED,
    r.LAT, 
    r.LNG, 
-- Subquery JOIN moved to FROM and WHERE statements
    COUNT(ID_ORDER) AS 'REMAINING ORDERS'   
FROM COURIER AS c 
    LEFT JOIN DELIVERY AS d ON c.ID_DELIVERY = d.ID_DELIVERY
    LEFT JOIN GEOLOCATIONS AS r ON r.ID_DELIVERY = d.ID_DELIVERY 
    LEFT JOIN ORDERS AS o ON d.ID_DELIVERY = o.ID_DELIVERY
WHERE c.DISABLED = false
AND o.DELIVERY_DATE IS NULL AND o.CANCELED = false
GROUP BY c.ID_COURIER
ORDER BY d.START_DATE DESC;