Stock Return as Zero due to unsold At the moment I'm bit stuck and need advice, I have looked up the 'ifnull' but nothing I can find 'ifnull' along with join tables. 

SELECT Ifnull((SELECT tbl_sku_units_order.sku         AS 'sold_sku', 
                  tbl_list_sku.sku                AS 'list_sku', 
                  tbl_sku_units_order.units_ordered, 
                  COALESCE(Sum(units_ordered), 0) AS 'Today_Sold' 
           FROM   tbl_list_sku 
                  INNER JOIN tbl_sku_units_order 
                          ON tbl_sku_units_order.sku = tbl_list_sku.sku 
           GROUP  BY tbl_list_sku.sku 
           HAVING units_ordered IS NOT NULL 
                  AND Length(units_ordered) &gt; 0 
           ORDER  BY units_ordered DESC), '0'); 


so I have tried different function, at the moment I am using this

SELECT 
    tbl_sku_units_order.sku AS 'sold_sku',
    tbl_list_sku.sku AS 'list_sku',
    tbl_sku_units_order.units_ordered,
    COALESCE(SUM(units_ordered), 0) AS 'Today_Sold'
FROM
    tbl_list_sku
        INNER JOIN
    tbl_sku_units_order ON tbl_sku_units_order.sku = tbl_list_sku.sku
GROUP BY tbl_list_sku.sku
HAVING units_ordered IS NOT NULL
    AND LENGTH(units_ordered) &gt; 0
ORDER BY units_ordered DESC


in the end, I got the result what they sold


but we have 100 SKU are unsold, but they are not displayed at all, as I would like to see the 100 unsold SKU displayed as '0'  as two different table on it. 
Maybe I am missing something?
I would make the following changes, to solve the issue and fix other minor problems with the query:

Solving the issue:


use LEFT instead of INNER join, in order to get results for skus that don't have orders.
remove the HAVING clause as it would essentially cancel the previous change.
use COALESCE() or IFNULL() to get 0 instead of NULL for these skus.


GROUP BY issue:


use SUM() or MIN() or some other aggregate for all columns in the SELECT that are not in GROUP BY.  

If you use IFNULL(tbl_sku_units_order.units_ordered, '0') AS 'unit_order' for example, when the group by is GROUP BY tbl_list_sku.sku, you may have many rows - with different units_ordered values - in the orders table. Which one should be shown?  

Now MySQL allows that (in versions before 5.7) but the result is indeterminate (any of those values may be shown). The bug/feature has been fixed in version 5.7 but it's good to use correct code, first to get sane results and second so it won't break when you upgrade.


Easier to read code:


use aliases for table names
use back-quotes or double-quotes for column aliases, or just not put spaces in names/aliases so everything can be typed without quotes.


The query:

SELECT 
    ls.sku                                AS list_sku, 
    COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku, 
    COALESCE( SUM(suo.units_ordered), 0 ) AS today_sold 
FROM   tbl_list_sku AS ls
       LEFT JOIN tbl_sku_units_order AS suo 
              ON suo.sku = ls.sku 
GROUP  BY ls.sku 
ORDER  BY today_sold DESC ;