How to select different records between two tables

Question

How to select different records between two tables

I tried to run the query with the query below; but it did not generate records;

SELECT
    c1.cod_coletor,
    c2.cod_coletor 
FROM item_inventarios c1
INNER JOIN funcionarios c2
ON c2.cod_coletor = c1.cod_coletor
WHERE c1.cod_coletor != c2.cod_coletor
and  c1.id_inventario='85'

I left my query available at this link http://sqlfiddle.com/#! 9/83a9dd/1

Answer 1

I solved with the following code

select * from item_inventarios
where cod_coletor not in (select cod_coletor
from funcionarios) and `id_inventario`='85'

Answer 2

It is not possible to connect the two tables by a column and then put them as different in the WHERE, for this one must have another column to make the link, ai yes could filter in the WHERE the different cod_collector in the two tables.

Place an ID in either of the two and connect this ID to the other table.

Ex: Put a column ID in the table and in the other table put a column Id_employees, in select connect the two tables through this ID and then use WHERE to remove what you do not want, filtering by another column.

Or else you could get the record that is not in one of the tables through the query:

SELECT cod_coletor FROM item_inventarios 
WHERE cod_coletor not in (SELECT funcionarios.cod_coletor FROM funcionarios) 
AND id_inventario='85'

Answer 3

Starting from your original SELECT, because I did not understand the considerations made later, try:

SELECT
    c1.cod_coletor,
    c2.cod_coletor 
FROM item_inventarios c1
FULL OUTER JOIN funcionarios c2
ON c1.cod_coletor != c2.cod_coletor
WHERE c1.id_inventario='85'