Left Join with + equivalent in POSTGRES

Question

In the oracle I can give a left Join with using (+) ex:

select 
from tabela1 A,tabela2 B
where A.id = B.id(+);

In the above example was made a LEFT JOIN because I want to return all the data from table A that does not satisfy the JOIN condition .

As I do in POSTGRES?

Answer 1

Postgresql does not have (+) as an OUTER JOIN operator, which is something outside the ANSI SQL standard. Oracle implemented support for the ANSI standard of OUTER Joins in version 9i, and even recommends not to use the old syntax, as it suffers from a number of limitations:

• You cannot specify the operator (+) in a query block that also contains the Join syntax in the FROM clause.
• The (+) operator can appear only in the WHERE clause or, in the context of the left correlation (by specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or display.
• If A and B are joined by multiple joining conditions, you must use the (+) operator under all these conditions. If you do not, Oracle Database will return only the lines resulting from a simple merge, but without a warning or error to warn you that you do not have the results of an external merge.
• The (+) operator does not produce an OUTER JOIN if you specify a table in the external query and the other table in an internal query.

• You cannot use the (+) operator to externally join a table to itself, although the auto-associations are valid. For example, the following statement is not valid:

  -- A seguinte declaração não é válida:
  SELECT employee_id, manager_id 
  FROM employees
  WHERE employees.manager_id (+) = employees.employee_id;
  

  However, the automatic union below is valid:

  SELECT e1.employee_id, e1.manager_id, e2.employee_id
  FROM employees e1, employees e2
  WHERE e1.manager_id (+) = e2.employee_id
  ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;
  

• The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression may contain one or more columns marked with the operator (+).

• A WHERE condition containing the operator (+) cannot be combined with another condition using the logical operator OR.
• A WHERE condition cannot use the IN comparison condition to compare a column marked with the operator (+) with an expression.

Basically Postgresql does not implement conditions that break the SQL standard, and Oracle only has this implementation because it is older than the standard, which exists to offer several advantages, among them the portability of your code between different Dbms =).

Answer 2

In Postgresql, to return all data from table A that does not satisfy the condition of join with table B, the select can be done like this:

select A.* 
from tabela1 A 
left join tabela2 B on A.id = B.id 
where 
B.id is null;