SQLite - remove duplicate column in a left outer join

sql, sqlite

Solution

As your join columns have the same name, you can use the `using` operator to define the join. In that case the "duplicate" columns will not be part of the result set

SELECT * 
FROM AQ_ADRESSES
  LEFT OUTER JOIN AQ_CP_ADRESSES using (IdAdr)
  LEFT OUTER JOIN AQ_ODONYMES using (Seqodo)
  LEFT OUTER JOIN AQ_REFERENTIEL  using (NoSqNoCivq)
  LEFT OUTER JOIN AQ_MUNICIPALITES using (CodeMun)
  LEFT OUTER JOIN AQ_ARRONDISSEMENTS using (CodeArr)

Tested in SQLite 3.8, don't know if this works the same in earlier versions. But this behavior is required by the SQL standard.

But in general `select *` is considered harmful in code that is used in production.

Problem

I am doing a `left outer join` over 6 tables, but I dont want the query to keep the duplicated columns. In SQLite the duplicate column are renamed with underscore and added into the view. Is it possible to remove them in the same query? ``` SELECT * FROM AQ_ADRESSES LEFT OUTER JOIN AQ_CP_ADRESSES ON AQ_ADRESSES.IdAdr = AQ_CP_ADRESSES.IdAdr LEFT OUTER JOIN AQ_ODONYMES ON AQ_ADRESSES.Seqodo = AQ_ODONYMES.Seqodo LEFT OUTER JOIN AQ_REFERENTIEL ON AQ_ADRESSES.NoSqNoCivq = AQ_REFERENTIEL.NoSqNoCivq LEFT OUTER JOIN AQ_MUNICIPALITES ON AQ_ADRESSES.CodeMun = AQ_MUNICIPALITES.CodeMun LEFT OUTER JOIN AQ_ARRONDISSEMENTS ON AQ_ADRESSES.CodeArr = AQ_ARRONDISSEMENTS.CodeArr ```

Original source