SQL: Using COUNT(*) Instead of EXISTS

Is it possible to use COUNT in place of EXISTS?

I have following query:

SELECT * 
FROM Goals G
WHERE EXISTS (SELECT NULL FROM tfv_home_last6(G.Date, G.Home) WHERE GameNumber <= 6 AND     
HomeGoals >= 3)

Instead of returning the row if at least one row exists in the subquery, I'd like to specify a number of rows that need to be returned in the subquery, something like

SELECT * 
FROM Goals G
WHERE ROWCOUNT(*) >= 2 (SELECT NULL FROM tfv_home_last6(G.Date, G.Home) WHERE GameNumber <= 6 AND     
HomeGoals >= 3)

I'm not sure how to go about it?

I'm using SQL Server 2012.

Answers


You can do the subquery pretty much just like you describe:

SELECT * 
FROM Goals G
WHERE (SELECT count(*)
       FROM tfv_home_last6(G.Date, G.Home)
       WHERE GameNumber <= 6 AND HomeGoals >= 3
      ) > 0;

However, this requires calculating the entire count. The exists form is more efficient, because it stops at the first matching record.

In SQL Server 2012, you could also use `cross apply:

SELECT * 
FROM Goals G cross apply
     (select count(*) as cnt
      FROM tfv_home_last6(G.Date, G.Home)
      WHERE GameNumber <= 6 AND HomeGoals >= 3
     ) a
WHERE a.cnt > 0;

I do not know which would have better performance, the correlated subquery in the where clause or the cross apply version.


Need Your Help

Height of a red black tree

data-structures binary-search-tree red-black-tree

I'm almost done implementing a red black tree but I'm stuck with height. (Not black height). Can anyone give me a hint or the concept on how to implement height? I know the formula but its not much...

Recursively counting character occurences in a string

java string recursion count character

Im making a program to count the number of times a character is found in a string. This is what my method looks like:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.