Left join with + equivalent in POSTGRES

Question

Left join with + equivalent in POSTGRES

Navigation

#1 by (5 votes)
#2 by (-1 votes)

2

oracle I can give a left join with using (+) ex:

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

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

How do I do the same on POSTGRES?

oracle postgresql

asked by anonymous 03.04.2018 / 16:34

2 answers

-1

In PostgreSQL, to return all data from table A that does not satisfy the condition of join with table B, 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;

03.04.2018 / 20:30

Code review: Server with threads to handle multiple clients SQL Select highest value item

score 5 · Accepted Answer

PostgreSQL does not have (+) as operator of OUTER JOIN, which is something non-standard ANSI SQL. Oracle has implemented support for ANSI OUTER JOINs in version 9i , and including recommends that you do not use old syntax because it suffers from a number of limitations:

You can not 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 leftmost correlation (when specifying the TABLE clause) in the FROM clause, and can only be applied to a column of a table or view. >
If A and B are joined by various join conditions, you must use the (+) operator in all these conditions. If you do not do this, Oracle Database will return only the rows resulting from a simple join, but without a warning or error to warn you that you do not have the results of an outer join.
The (+) operator does not produce an OUTER JOIN if you specify a table in the outer query and the other table in an internal query.

You can not use the (+) operator to externally join a table to itself, although self-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 following auto merge 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 that contains the (+) operator can not be combined with another condition using the OR logical operator.
A WHERE condition can not 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 default, which exists to offer several advantages, among them the portability of your code between different DBMSs =) .