Cardinality Estimator Series II: Learning Predicates

Predicate: Predicates are the expression which evaluates to TRUE or FALSE or UNKNOWN. They comes in two form

·         Filter Predicate :

·         Join Predicate: 

Filter Predicate: The expression that comes in WHERE or HAVING clause.

Example 1.

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

So here the Filter predicates are StateProvinceID =79 and  City = 'Bothell'

Example 2.

select StateProvinceID, Count(*) as 'MoreThanOneCity'

from Person.Address

group by StateProvinceID

having count(*) > 1

Here the filter predicate is HAVING with a condition of count(*) > 1.

Join Predicate: The join condition that comes in FROM Clause of select query are considered as JOIN Predicate.

Example:

select be.BusinessEntityID,a.AddressLine1 from Person.BusinessEntity be

inner join Person.BusinessEntityAddress bea on be.BusinessEntityID = bea.BusinessEntityID

inner join Person.Address a on bea.AddressID = a.AddressID

Here the Joining condition are considered as Join predicate.

i.e. be.BusinessEntityID = bea.BusinessEntityID

bea.AddressID = a.AddressID

Code like your code shut others mouth!

Post Reference: Vikram Aristocratic Elfin Share