Show difference between two tables in mysql using join

Question

I have two tables in my database: projects e invoices

Table invoices

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
);


INSERT INTO `invoices` (`id`, `name`) VALUES
(1, 'Invoice 1'),
(2, 'Invoice 2'),
(3, 'Invoice 3');

Table projects

CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Fazendo dump de dados para tabela `projects`
--

INSERT INTO `projects` (`id`, `invoice_id`, `name`) VALUES
(1, 1, 'Project name');

Expected result: show rows

(2, 'Invoice 2'),
(3, 'Invoice 3')

Answer 1

If you want invoices where no project has that invoice, then one method uses not exists:

select i.*
from invoices i
where not exists (select 1
                  from projects p
                  where p.invoice_id = i.id
                 );