Alternatives for a LEFT OUTER JOIN query

Question

Problem

I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS.

SQL Query

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

Table

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);

Example data

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');

score 3 · Answer 1 · answered Sep 22 '14 at 13:30

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform.

SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');

Or you could build some type of temp table (or a regular table if H2 doesn't support temp tables), then populate it in three steps. Again, this will probably still compile using an anti-semijoin or some other type of outer join, but maybe it'll function as a workaround for your bug:

--- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;

But these are not pretty solutions.

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Solution

this is not exactly what i asked for but it solves the problem of the original query to fail on H2 Database thanks ypercube your suggestion is correct

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END AS nlsGuid
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

results in a correct result.

but due (lot) better performance i process the whole table to Objects in my Application now and grab the Objects (select which one is the right one by code)

lumo · Answer 3 · 2014-09-26T05:34:33.650

i had two queries without outer join, which worked fast and correct on Microsoft SQL Server but both did not work out on H2 Database one of em because MSSQL uses + to concatenate strings and H2 uses CONCAT (which is crap because afaik this does not match the SQL specifications)

my final solution is to create one single query in my code and find the correct stuff with code.

One of the queries i tried was this one (works on Microsoft SQL Server but not on H2 due different concatenate syntax)

SELECT * FROM tSysNLS
WHERE nlsGuid = CASE
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?2) then ?4+'.'+?5+'.'+?2
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?1) then ?4+'.'+?5+'.'+?1
ELSE ?4+'.'+?5+'.'+?3
END

where ?# are parameters
?1 = 'de_AT'
?2 = 'de'
?3 = 'en'
?4 = 'Contact'
?5 = 'firstName'