Select Inner Join of what is not in the other table

Question

I have two tables, Anuidades and Pagamentos.
Of these annuities it is necessary to select the data of Anuidades which have not yet been paid.

Table Anuidades

Table Pagamentos

To return the annuities data that had been paid I used the following query:

SELECT * 
FROM anuidades
INNER JOIN pagamentos ON anuidades.Id = pagamentos.idAnuidade
WHERE pagamentos.idMinistro = 1

and he returned correctly:

But when I try to select the annuities that have no record in the payments table I cannot.

Answer 1

Try it this way:

SELECT anuidades.* 
FROM anuidades
LEFT JOIN pagamentos ON anuidades.Id = pagamentos.idAnuidade
WHERE pagamentos.idMinistro = 1
AND pagamentos.idAnuidade Is null

Answer 2

There is more than one way to make this select, it follows some forms:

LEFT OUTER JOIN

SELECT * FROM ANUIDADES
LEFT JOIN PAGAMENTOS 
    ON ANUIDADES.Id = PAGAMENTOS.idAnuidade
WHERE PAGAMENTOS.idAnuidade IS NULL

NOT EXISTS

SELECT * FROM ANUIDADES
WHERE NOT EXISTS (
            SELECT NULL FROM PAGAMENTOS
                WHERE   PAGAMENTOS.IDMINISTRO = 1
                AND     PAGAMENTOS.IDANUIDADE = ANUIDADES.ID)

NOT IN

SELECT * FROM ANUIDADES
WHERE ANUIDADES.ID NOT IN (
                        SELECT IDANUIDADE FROM PAGAMENTOS
                        WHERE IDMINISTRO = 1)

Note: LEFT JOIN / IS NULL and NOT EXISTS are semantically equivalent, while NOT IN is not.