JOIN - Beginner’s Question

Asked

Viewed 60 times

-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

Tabela Empresa

Tabela Funcionário

Full Outer Join - Explicação gráfica

Figure 1: Company Table Figure 2: Employee Table Figure 3: Graphical explanation of outer join

sql postgresql
  • 1

    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.

1 answer

1

I would discourage you from using full function Outer Join, it is the rarest and often boring to use, there are some cases where it is used. Usually in exception or ETL reports or other very peculiar situations where both sides have data that you are trying to match. The best alternative is to use an INNER JOIN, a LEFT JOIN (with the right side IS NULL) and a RIGHT JOIN (with the left side IS NULL) and make a UNION - sometimes this approach is better because you can customize each junction individually more obviously ( and add a derived column to indicate which side is found or is found in both and which one will "win").

  • Okay, for what I intended to do, it would fit. But I will use it as a measure. Thank you.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.