Select Columnvalue If the Column Exists Otherwise Null

Select columnValue if the column exists otherwise null

You cannot do this with a simple SQL statement. A SQL query will not compile unless all table and column references in the table exist.

You can do this with dynamic SQL if the "subquery" is a table reference or a view.

In dynamic SQL, you would do something like:

declare @sql nvarchar(max) = '
SELECT uniqueId, columnTwo, '+
    (case when exists (select *
                       from INFORMATION_SCHEMA.COLUMNS 
                       where tablename = @TableName and
                             columnname = 'ColumnThree' -- and schema name too, if you like
                      )
          then 'ColumnThree'
          else 'NULL as ColumnThree'
     end) + '
FROM (select * from '+@SourceName+' s
';

exec sp_executesql @sql;

For an actual subquery, you could approximate the same thing by checking to see if the subquery returned something with that column name. One method for this is to run the query: select top 0 * into #temp from (<subquery>) s and then check the columns in #temp.

EDIT:

I don't usually update such old questions, but based on the comment below. If you have a unique identifier for each row in the "subquery", you can run the following:

select t.. . .,  -- everything but columnthree
       (select column3   -- not qualified!
        from t t2
        where t2.pk = t.pk
       ) as column3
from t cross join
     (values (NULL)) v(columnthree);

The subquery will pick up column3 from the outer query if it doesn't exist. However, this depends critically on having a unique identifier for each row. The question is explicitly about a subquery, and there is no reason to expect that the rows are easily uniquely identified.

Select column value if column exists in that table else create that column and set it's value to null in BigQuery

I assume in the following that you have a source table (the one with potentially "missing" columns) and an existing target table (with the desired schema).

In order to get the information of the columns of these tables, you just need to look into the INFORMATION_SCHEMA.COLUMNS table.
The solution below uses dynamic SQL, to 1) generate the desired SQL, 2) run it.

DECLARE column_selection STRING;

SET column_selection = (
    WITH column_table AS (
        SELECT
          source.column_name AS source_colum,
          tgt.column_name AS target_column
        FROM
          (SELECT
              column_name
            FROM `<yourproject>.<target_dataset>.INFORMATION_SCHEMA.COLUMNS`
            WHERE table_name='<target_table>') tgt
        LEFT JOIN
          (SELECT column_name
            FROM `<yourproject>.<source_dataset>.INFORMATION_SCHEMA.COLUMNS`
            WHERE table_name='<source_table>') source
        ON source.column_name = tgt.column_name
    )

    SELECT STRING_AGG(coalesce(source_column,
        CONCAT("NULL AS `",target_column, "`")), ", \n") AS col_selection
  FROM
    column_table
)

EXECUTE IMMEDIATE
  FORMAT("SELECT %s FROM `<yourproject>.<source_dataset>.<source_table>`", column_selection) ;

Explanation of the steps

1. Build a column_table for the columns we want to query:

   a. first column containing the columns of the target table,
   b. second one containing the corresponding source columns if they exist, or NULL if they don't

2. Once we have this table, we can build the desired SELECT statement: the name of the column is it's in the source table, or if it's NOT present, we want to have in our query " NULL AS `column_name_in_target` "

This is expressed in the
coalesce(source_column, CONCAT("NULL AS ``",target_column, "\``"))

We aggregate all these statement with STRING_AGG into the desired column selection.

3. Final step: putting together the rest of the query ( "SELECT" + <column_selection_string> + "FROM <your_source_table>" + ...), and we can EXECUTE IMMEDIATE it.

SQL Server How to SELECT a column only if it exists in the table

You can write as:

SELECT CASE WHEN EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' 
    and COLUMN_NAME='columnName'
) 
THEN
(
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
    and COLUMN_NAME='columnName'
)
ELSE
NULL 
END
AS columnName

DEMO

Edit:
If you are looking to select top 10 values from a table's column if that column exists then you need to write a dynamic query as:

SELECT @columnVariable =     
CASE WHEN EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' 
    and COLUMN_NAME='columnName'
) 
THEN
(
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' 
    and COLUMN_NAME='columnName'
)
ELSE
NULL 
END

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT TOP 10 ' + @columnVariable+ '
       FROM test.tableName ';      

EXECUTE sp_executesql @SQLString

DEMO2

Select column name that may or may not exist in table, and get null value rather than error

If performance does not matter, try Vertica Flex Tables:

DROP TABLE IF EXISTS allcols;                            
DROP TABLE IF EXISTS allbut1;
DROP TABLE IF EXISTS allbut4;

CREATE FLEX TABLE allcols();
INSERT INTO allcols(col1,col2,col3,col4)
          SELECT  1,  2,  3,  4
UNION ALL SELECT 11, 12, 13, 14
;
      
CREATE FLEX TABLE allbut1();
INSERT INTO allbut1(col2,col3,col4)
          SELECT  22, 23, 24
UNION ALL SELECT  32, 33, 34
;
      
CREATE FLEX TABLE allbut4();
INSERT INTO allbut4(col1,col2,col3)
          SELECT  21, 22, 23
UNION ALL SELECT  31, 32, 33
;
COMMIT;
\pset null (null)
          SELECT col1,col2,col3,col4 FROM allcols
UNION ALL SELECT col1,col2,col3,col4 FROM allbut1
UNION ALL SELECT col1,col2,col3,col4 FROM allbut4
;
-- out   col1  | col2 | col3 |  col4  
-- out --------+------+------+--------
-- out  1      | 2    | 3    | 4
-- out  11     | 12   | 13   | 14
-- out  (null) | 22   | 23   | 24
-- out  (null) | 32   | 33   | 34
-- out  21     | 22   | 23   | (null)
-- out  31     | 32   | 33   | (null)

How to select column 1 value if exists else column 2 value in PostgresQL?

Use COALESCE(). From the manual 9.18.2. COALESCE:

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.

For example:

select coalesce(column_1, column_2) as column_3 from t

How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Is it possible to test the existence of a column, then choose an action based on if the column exists?

The issue is that the script needs to be compiled . . . and you are missing the columns in the compilation. I might suggest:

IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.COLUMNS c
           WHERE TABLE_SCHEMA = 'dbo' AND
                 TABLE_NAME = 'users' AND
                 COLUMN_NAME = '<column A>'
          ) -- Check for existence of <column A> column
BEGIN
-- Some earlier versions do not have this column
    EXEC('
UPDATE Users
    SET Password = ''XXXX'', <column A> = 0, <column B> = NULL
    WHERE Login = ''User''
'); -- Reset password for later versions
END;
ELSE
BEGIN
    UPDATE Users
        SET Password = 'XXXX'
        WHERE Login = 'User' -- Reset password for earlier versions

    PRINT 'The Password for Login "User" has been reset to XXXX.'
END;

Both versions have Password, so the ELSE should compile fine.

---

Related Topics