-1
I have a question about full outer join
, I researched and still do not understand. The last figure below explains, graphically, the operation of full outer join
, that returns only the data not coincident in the two tables (company table, in the first figure, and working table, in the second figure).
Note that keys 123, 1230000 occur both in the company table (first table, with only two records), and in the employee table (second table, with 6 records).
Already keys 12300 and 123000 occur only in the employee table. Since they do not occur in the other table, the query I do with the command below should return only the lines with keys 12300 and 123000. However, they return only two lines of null
? What might be going on? The command I am using is just below:
SELECT pessoaID, ultimoNome, cidade
FROM empresa e
FULL OUTER JOIN funcionario f
ON e.pessoaID = f.personID
WHERE e.pessoaID is NULL
OR f.personID is NULL
Figure 1: Company Table
Figure 2: Employee Table
Figure 3: Graphical explanation of outer join
FULL OUTER JOIN gives as a result all the rows of the tables that meet the join criterion, plus one row for each row of the left table that does not meet the join criteria (with Nulls in the fields referring to the right table) and one more row for each row of the right table that does not meet the join criteria (with Nulls in the fields referring to the left table). That is all rows that meet the criteria of INNER JOIN, plus all rows of tables for which there is no match, with Nulls in the fields of the table without match.
– anonimo