inner join, right join ? Count rows

Question

How can I discover which orders has no details rows ?

Table Orders: orderId (int), customerName, deliveryDate (date), price (decimal)

Table Details: detailId (int), orderId (int), sku (varchar), quantity (int)

I tried (with no success) :

SELECT
    COUNT(Details.detailId) AS Tot,
    Orders.orderId
FROM Details
INNER JOIN Orders
    ON Details.orderId = Orders.orderId
GROUP BY Details.orderId
HAVING Tot = 0

Gordon Linoff · Answer 1 · 2016-09-26T12:26:18.970

4

How about not in or not exists?

select o.*
from orders o
where not exists (select 1
                  from details d
                  where d.orderId = o.orderId
                 );

If you don't want all columns, then select the ones you want in the outer select.

edited Sep 26 '16 at 12:26

answered Sep 26 '16 at 12:17

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Sep 26 '16 at 12:19

0

select it with a outer join an check if orderId is null... like this

SELECT
    o.*
FROM orders o
LEFT OUTER JOIN details d
    ON d.orderId = o.orderId
WHERE d.orderId IS NULL

answered Sep 26 '16 at 12:19

silly

7,789
2
24
37

score 0 · Accepted Answer · edited May 23 '17 at 12:33

Try using a LEFT OUTER JOIN join. See also How to retrieve non-matching results in mysql .

INNER JOINS select the rows from two tables that match. Everything else will be discarded and is not available in the result set that you can query with HAVING

LEFT OUTER JOINS select a row for every entry in the left table (Orders). Always generate a row for the right table (Details). If no matching row can be found the values will be NULL. We can use this to our advantage, try:

SELECT Orders.orderId, Details.orderId
FROM Orders
LEFT OUTER JOIN Details              -- generate a row for every row in a
                                     -- make null if not matching
ON Details.orderId = Orders.orderId  -- match condition
WHERE Details.orderId IS NULL

inner join, right join ? Count rows

3 Answers3