Magento : Add custom column from custom table in magento admin grid

Use columns() method in collection as
 $collection->getSelect()->columns(new Zend_Db_Expr("[expression or any myql sub query] as custom_column"));

For example:

Consider Bd_Test module and override _prepareCollection() method in admin grid

protected function _prepareCollection()
                {
                 $collection = Mage::getModel('test/test')->getCollection();
     
                  $collection->getSelect()->columns(new Zend_Db_Expr("if((SELECT count(*) FROM table_name as  table_alias  WHERE table_alias.column > main_alias. column ) , 'NO', 'YES') as custom_column"));
               // echo $collection->getSelect();
                                $this->setCollection($collection);
                                return parent::_prepareCollection();
                }