Checking column exists with If else condition in sql

Question

I'm trying to check weather thw column exists or not

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME='businness_id' > 0 ) THEN 
 PRINT 'test'

Whats wrong with above sql? getting error as

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (Select COUNT(*) From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'tb_consu' at line 1

New to SQL. Thanks for any help,

Version of MySql is 5.X

Answer 1

You can fix the query by moving the closing paren:

IF (SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME = 'businness_id'
   ) > 0 THEN 
 PRINT 'test'

A better way to write the condition is using exists:

IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME = 'businness_id'
          ) THEN 

Answer 2

SQL command is not a Boolean function. Therefore you need to check the value.

Like this:

IF (SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME='businness_id') >= 0 
THEN PRINT 'test'

Answer 3

If you try to use this as part of a stored procedure, you might take a look at the if statement in the mysql documentation

if you do this as part of a normal query, you can write it like

SELECT IF (COUNT(*) > 0, 'TEST, 'TEST FAILED') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME='businness_id'

Answer 4

i would suggest using CASE statement in MYSQL

SELECT CASE 
    WHEN COLUMN_NAME = 'businness_id' then 'TEST'
    END
    from FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'tb_consumer'

Case statements are faster on smaller databases