sqlsqlitealias

alias multiple columns


I have a table in sqlite and now I want to alias multiple columns, how can I do it?

For example, my columns are Name, Family, City and now I want to check a condition on them

SELECT (Name,Family,City) AS Personal From tblPersonal
WHERE Personal!=NULL

Solution

  • Updated answer:

    Well if you want to check if all columns are NOT NULL the easy solution would be to concating/adding then, with the + operator:

    SELECT Personal From 
        (SELECT  (Name + Family + City) AS Personal From tblPersonal) AS SubQueryPersonal
    WHERE Personal IS NOT NULL
    

    Since concating/adding NULL with the + operator results in NULL.

    Just for completeness, if you want to use the columns you would have to add them, in both queries.

    For example:

    SELECT Personal, Name, Family, City From 
        (SELECT  (Name + Family + City) AS Personal, Name, Family, City From tblPersonal) AS SubQueryPersonal
    WHERE Personal IS NOT NULL
    

    Update 2:

    I made a small online Fiddle, where this can be seen in action, and I also added the suggestion of using the || operator from @JonasMetzler comment, so that it can be compared.
    But since this new field, is only used for checking if all columns are NOT NULL it should not matter, if you use + or ||.

    While doing this, I found out, in SQLite it is possible to use Alias Names in the WHERE clause. It is non-SQL-Standard, but it works in SQLite, and like this you don't need a subquery. (Link to SO Post for details)
    So this would be possible (in SQLite):
    SELECT Name + Family + City AS Personal FROM tblPersonal WHERE Personal IS NOT NULL;

    Old Answer is incorrect:

    Even if I don't understand the why, theoretically you could build something similar (depending on your usecase you would need a different query)

    But basically, you can create a subquery that use COALESCE (link to the documenation) achieve something similar:

    SELECT Personal From 
        (SELECT COALESCE (Name,Family,City) AS Personal From tblPersonal) AS SubQueryPersonal
    WHERE Personal IS NOT NULL
    

    Just to explain: COALESCE returns the first non-Null Argument, so it should have the desired effect.