SQL Server - use count function with intersect
SELECT COUNT(*) FROM( SELECT id FROM table1 where col1 like '%abcd%' intersect SELECT id from table2 where col2 like '%efgh%') IWhere I is an alias for the 'derived' table. It doesn't do anything important in this instance, but needs to be there for SQL to recognize the syntax - otherwise, you'll get an "Incorrect syntax near ')'" error.