SELECT "NOT IN", INNER JOIN and COUNT in SQL Query-CodePudding

I am trying to select which co-ordinates from OA table are NOT found in the CUSTOMER address table.

SELECT DISTINCT
       OA.CO_ORDS
FROM    
        CUSTOMER
INNER JOIN  
        OA ON customer.address=oa.co_ords
ORDER BY ID ASC;

Returns the co-ordinates which ARE in the customer table. How do I return those that are not in the customer table?

Am I also able to COUNT how many of customers are is in each co-ordinate (The co-ords are not specific and not accurate, this is purely for query testing only)

SELECT DISTINCT
           OA.CO_ORDS
    FROM    
            CUSTOMER
    INNER JOIN  
            OA ON customer.address=oa.co_ords
    ORDER BY ID ASC;

CodePudding user response：

We can use NOT EXISTS to find those co-ordinates which don't appear in the customer table:

SELECT co_ords
FROM oa
WHERE 
  NOT EXISTS 
    (SELECT 1 FROM customers 
      WHERE address = oa.co_ords)
ORDER BY id;

In order to count how many customers belong to a certain co-ordinate, we can use COUNT with GROUP BY, something like this:

SELECT c.address, COUNT(*)
FROM customers c
JOIN oa 
  ON c.address = oa.co_ords
GROUP BY c.address;

It could be better to count a specific column instead of *.

It could also be better to use an IN clause instead of JOIN the tables:

SELECT c.address, COUNT(*)
FROM customers c
WHERE address IN 
  (SELECT co_ords FROM oa)
GROUP BY c.address;

Such details depend on your exact table structure, you should please try this out or provide more details.

CodePudding user response：

You could also do:

SELECT co_ords
FROM   oa
MINUS
SELECT address
FROM   customers;

which can sometimes be faster than doing an anti-join. Note that MINUS does a distinct on the resultset.