SELECT EXISTS (subselect with LIMIT 1)

Posts   
 
    
rlucassen
User
Posts: 22
Joined: 25-Oct-2013
# Posted on: 29-Sep-2016 13:07:18   

Hi,

is it possible to do a

SELECT EXISTS (subselect LIMIT 1)

to test if a certain predicate returns at least one row without fetching the data.

i'm using it this way now but this returns a record without using it.


collection.GetMulti(filter, 1, null, relations, null, null, 1, 1);
return collection.Count > 0;

it's not a big waste of performance but maybe there is a cleaner/better way to do this

rlucassen
User
Posts: 22
Joined: 25-Oct-2013
# Posted on: 29-Sep-2016 13:22:09   

rlucassen wrote:

Hi,

is it possible to do a

SELECT EXISTS (subselect LIMIT 1)

to test if a certain predicate returns at least one row without fetching the data.

i'm using it this way now but this returns a record without using it.


collection.GetMulti(filter, 1, null, relations, null, null, 1, 1);
return collection.Count > 0;

it's not a big waste of performance but maybe there is a cleaner/better way to do this

Mysql doensn't even need The LIMIT 1 if it finds 1 record it aborts the query'and returns 1

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Sep-2016 13:20:11   

Options: 1- You can exclude as many fields as you want and only return PKs and FKs.

2- Use GetScalar() e.g.


int count = (int)adapter.GetScalar(CustomerFields.CustomerId, null, AggregateFunction.Count, (CustomerFields.Country == "UK"));

Which generates:

Query: SELECT TOP(@p2) COUNT([NORTHWND].[dbo].[Customers].[CustomerID]) AS [CustomerId] FROM [NORTHWND].[dbo].[Customers]   WHERE ( [NORTHWND].[dbo].[Customers].[Country] = @p3)
    Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p3 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "UK".

3- use QuerySpec or Linq .Any() method [Recommended]

e.g. (Linq)

var linqMetaData = new LinqMetaData(adapter);

var q = (from c in linqMetaData.Customer
         where c.Country == "UK"
         select c
        ).Any();

Which generates:

    Query: SELECT TOP(@p3) CASE WHEN  EXISTS (SELECT [LPA_L2].[Address] FROM (SELECT [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Country], [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[Fax], [LPLA_1].[Phone], [LPLA_1].[PostalCode], [LPLA_1].[Region] FROM [NORTHWND].[dbo].[Customers]  [LPLA_1]   WHERE ( ( [LPLA_1].[Country] = @p1))) [LPA_L2]) THEN 1 ELSE 0 END AS [LPFA_1] FROM [NORTHWND].[dbo].[Customers]  [LPLA_1]  
    Parameter: @p1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "UK".
    Parameter: @p3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

e.g. (QuerySpec)

var qf = new QueryFactory();

// Scalar query which fetches a boolean using Any(). 
var q = qf.Create().Select(qf.Customer.Where(CustomerFields.CustomerId.Equal("CHOPS")).Any());
var exists = adapter.FetchScalar<bool>(q);

Which generates:

    Query: SELECT TOP(@p7) CASE WHEN CASE WHEN  EXISTS (SELECT [LPA_L1].[Address] FROM (SELECT [NORTHWND].[dbo].[Customers].[Address], [NORTHWND].[dbo].[Customers].[City], [NORTHWND].[dbo].[Customers].[CompanyName], [NORTHWND].[dbo].[Customers].[ContactName], [NORTHWND].[dbo].[Customers].[ContactTitle], [NORTHWND].[dbo].[Customers].[Country], [NORTHWND].[dbo].[Customers].[CustomerID] AS [CustomerId], [NORTHWND].[dbo].[Customers].[Fax], [NORTHWND].[dbo].[Customers].[Phone], [NORTHWND].[dbo].[Customers].[PostalCode], [NORTHWND].[dbo].[Customers].[Region] FROM [NORTHWND].[dbo].[Customers]   WHERE ( ( [NORTHWND].[dbo].[Customers].[CustomerID] = @p1))) [LPA_L1]) THEN 1 ELSE 0 END=1 THEN @p3 ELSE @p5 END AS [LLBLV_1] FROM [NORTHWND].[dbo].[Customers]  
    Parameter: @p1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "CHOPS".
    Parameter: @p3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
    Parameter: @p5 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.
    Parameter: @p7 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.