Is there a where or group by for after a full outer joint to show the negative?

Question

I’m trying to query a DB sql oracle which combines 2 tables data but shows the locations which have not had any orders.

I have created a location_t with the relevant grids by Mile from the main company. I have given the grids a location ID which map over to the customer table.

I need to write a query which shows the grids that have no Customer orders.

I have begun the query with a full outer join

Select customer.location_id, location-t.grid_location From customer Full outer join location_t On customer.location_Id = location_t.location_Id

This works but of course gives all the results with a - for the ids not used.

Is there a where clause which will only show the empty IDs where there are no customers who order yet.

Examples of better ways to do this are also welcome. Thank you

Answer 1

Left join the grids to the customer and then require that only rows where customer is null are returned:

SELECT
    *
FROM
  location_t l
  left outer join customer c on c.location_Id = l.location_Id
WHERE
  c.location_id IS NULL

When you do your WHERE, specify the join column to be null, or another column that you know will never naturally contain nulls (e.g. don't use customer's middle name if some customers naturally will have no middle name and hence have nulls in that column as part of the natural data)

Answer 2

I need to write a query which shows the grids that have no Customer orders.

Assuming that you really want locations with no customers, then I think of not exists:

select l.*
from location_t l
where not exists (select 1
                  from customer 
                  where c.location_Id = l.location_Id
                 );