The following bug has been logged on the website:
Bug reference: 9896
Logged by: SQLpro
Email address: sqlpro@sqlspot.com
PostgreSQL version: 9.3.4
Operating system: Windows
Description:
According to ISO standard SQL there is no limitation in predicat ON for JOIN
operator.
But PostGreSQL is unable to join tables in the FULL OUTER mode when
predicate ON is not sargable.
here is an example :
CREATE TABLE T_CLIENT_CLI --> customers
(CLI_ID INT PRIMARY KEY,
CLI_SIREN CHAR(9) NOT NULL UNIQUE,
CLI_ENSEIGNE VARCHAR(16) NOT NULL);
CREATE TABLE T_PROSPECT_PSP --> sale perspectives
(PSP_ID INT PRIMARY KEY,
PSP_SIREN CHAR(9) NOT NULL UNIQUE,
PSP_ENSEIGNE VARCHAR(16) NOT NULL);
We want to join on SIREN (national French firm number) or, in the case of
mistake in this number, on the name (enseigne).
Some datas :
INSERT INTO T_CLIENT_CLI VALUES
(1, '123456789', 'IBM'),
(2, '111111111', 'Microsoft'),
(3, '999555111', 'SAP');
INSERT INTO T_PROSPECT_PSP VALUES
(101, '123456789', 'IBM'),
(102, '555555555', 'Microsoft'),
(103, '777777777', 'Amazon'),
(104, '444444444', 'Google');
This query answer exactly to the question :
SELECT *
FROM T_CLIENT_CLI AS C
FULL OUTER JOIN T_PROSPECT_PSP AS P
ON C.CLI_SIREN = P.PSP_SIREN
OR C.CLI_ENSEIGNE = P.PSP_ENSEIGNE;
The result would be :
CLI_ID CLI_SIREN CLI_ENSEIGNE PSP_ID PSP_SIREN PSP_ENSEIGNE
----------- --------- ---------------- ----------- ---------
----------------
1 123456789 IBM 101 123456789 IBM
2 111111111 Microsoft 102 555555555 Microsoft
3 999555111 SAP NULL NULL NULL
NULL NULL NULL 103 777777777 Amazon
NULL NULL NULL 104 444444444 Google
But PG throw an exception :
ERREUR: FULL JOIN is only supported with merge-joinable or hash-joinable
join conditions
Ãtat SQL :0A000
This is the case in version 9.1.2 too.
It seems that is the case in any version of PG...
By comparizon, this query guive the exact answer in :
- MS SQL Server since version 2000 to 2014 (2005, 2008/2008R2, 2012).
- Oracle 11G
Thanks