1

I have recently faced a query which has been written longtime ago for an Informix database. This query seems a bit strange and nonsense to me.

I know This query returns all the rows from the city table with the rows that match in the ocw table. If no record for a city appears in the ocw table, the returned code column for that city has a NULL value.

I understand also that UNION removes duplicates, whereas UNION ALL does not.

Is my understanding about outer and union all correct?

Can anyone explain what they try to achieve with this query and is there a better way to do this?

SELECT * FROM city as c, OUTER ocw o
WHERE c.mutual = o.code
INTO temp city_ocw;

SELECT
      name   ,
      year   ,
      mutual ,
      0 animalId 
FROM
      city_ocw
WHERE
      code IS NULL
GROUP BY
      1, 2, 3 , 4

UNION ALL

SELECT
      name   ,
      year   ,
      mutual ,
      animalId 
FROM
      city_ocw
WHERE
     NOT code IS NULL
GROUP BY
      1, 2, 3 , 4 
INTO TEMP city_ocw_final ;
itro
  • 7,006
  • 27
  • 78
  • 121
  • 4
    You can't group by 5 columns, if your result set has only 4 columns... – The Impaler Jun 10 '20 at 14:42
  • 2
    It is hard to tell from your info which columns come from which base table. I don't get how it could be linked on code but have nulls? Unless Informix does something weird with that case. Generally nulls don't link to nulls, so the second half of the union would never return data. My best guess is they are trying (and failing?) to return 0 animalIds when code is null. – Jon Wilson Jun 10 '20 at 15:04
  • @TheImpaler You are right, thanks for mentioning it, for simplicity, I just delete extra columns. @JonWilson I did correct the naming, it is mutual in place of code and also the ` ocw table` is a one column table namely `code`. – itro Jun 10 '20 at 15:11

1 Answers1

2

@TheImpaler is right that grouping by 5 columns when your result set only has 4 columns doesn't make much sense, but I'll ignore that.

As I see it, your understanding of OUTER and UNION ALL is correct. The goal appears to be to generate a stacked result set with 2 versions of city joined to ocw, 1 with an actual animalId, and 1 with animalId = 0.

I'm not familiar with OUTER being used by itself (I always use it with LEFT/RIGHT/FULL), but would assume the default to be LEFT OUTER.

If no record for a city appears in the ocw table, the returned code column for that city has a NULL value.

That would be true, but the line WHERE c.mutual = o.code will make that unimportant. You could rewrite the join as LEFT JOIN ocw o ON c.mutual = o.code

The GROUP BY may have been done in the past for some aggregate column that no longer exists... perhaps that's column 5?

I think it could be redone as: 

SELECT name,
    year,
    mutual,
    0 as animalId
FROM city c
    LEFT JOIN ocw o ON c.mutual = o.code
UNION --don't need the all since animalId ensures rows are different
SELECT name,
    year,
    mutual,
    animalId
FROM city c
    LEFT JOIN ocw o ON c.mutual = o.code
kangaroo_pope
  • 49
  • 1
  • 9
  • 1
    The OUTER is an old Informix-specific notation separate from the standard SQL LEFT OUTER JOIN etc. The usage here is equivalent to an LOJ, as you deduced. – Jonathan Leffler Jun 11 '20 at 04:20
  • 1
    Your query doesn't quite produce the same answer as the original. The first term should only generate a `0` for `animalID` when there is no matching row in `ocw`. Yours produces a `0` entry for each row in `city`. The second term should only produce rows where there is a matching row in `ocw`. Yours produces row where there is no matching entry. – Jonathan Leffler Jun 11 '20 at 04:34
  • True, but looking at it further, there shouldn't ever be a place where `animalId = 0`. The `OUTER` above is basically changed to an `INNER` with the `WHERE` clause, so (unless I'm missing something), the query could actually just be `SELECT name, year, mutual, animalId FROM city c LEFT JOIN ocw o on c.mutual = o.code`. Maybe the 0 as `animalId` was placed as an error check? But it seems strange to short circuit your error check with the `WHERE` that makes it impossible. – kangaroo_pope Jun 11 '20 at 12:54