Integrity check in Oracle-CodePudding

I'm new to Oracle. I don't know how to write a SELECT statement that lists vendors for which there is an invoice and also lists vendors for which there is no invoice. The list should be in an order that clumps all the vendors without invoices together.

CodePudding user response：

Vendors with invoices (clump them together? Clump how, exactly?):

select v.vendor_id, v.vendor_name
from vendors v
where exists (select null
              from invoices
              where i.vendor_id = v.vendor_id
             );

Vendors without invoices:

select v.vendor_id, v.vendor_name
from vendors v
where not exists (select null
                  from invoices
                  where i.vendor_id = v.vendor_id
                 );

CodePudding user response：

You can do it in one query using LEFT OUTER JOIN and using the COUNT aggregation function to determine if there are invoices or not:

SELECT v.vendor_id,
       CASE COUNT(i.invoice_id)
       WHEN 0
       THEN 'No Invoices'
       ELSE 'Has Invoices'
       END AS invoices
FROM   vendors v
       LEFT OUTER JOIN invoices i
       ON (v.vendor_id = i.vendor_id)
GROUP BY
       v.vendor_id
ORDER BY
       invoices,
       vendor_id;