What is Predicate Logic and how it is applied to SQL Server?

We have been working with databases and SQL for long time but some of the important theories and concepts are purposely ignored assuming that they are not crucial and can complete the implementation without any issue. But understanding them and applying to implementations make our solutions perfect and less error prone because operations implemented flow on correct way producing the right result. Do you know that terms like SET Theory, Predicate Logic are not much known among engineers including database engineers?

Let's try to understand Predicate Logic in simple form. It is a mathematical basis for the relational database model and as per the theory, it is an expression or property that is either true or false. It is sometime considered as a Boolean Expression too.

Although the theory describes two possible outcomes (true or false), unknown values are not uncommon in database world, hence it needs to be considered. This extends the outcomes of Predicate Logic to three: True, False or Unknown.

How can we apply this to a database? Definitely for comparison, simply as an expression that allows us to evaluate it to true, false or unknown. Remember, unknown is null in database model.

Predicates do not stand alone when implementing with SQL Server databases. It is always a part of the query and following are the possible roles it can play;

• Filtering data using WHERE or HAVING clauses
• Handling the flow with conditional logic using IF or CASE expressions
• Joining tables using ON filter
• Enforcing entity, domain and referential integrity (Example: CHECK, FOREIGN KEY constraints)

Here is an example;

USE AdventureWorks2014;
GO

SELECT *
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2013
 AND (SubTotal > CASE MONTH(OrderDate)
      WHEN 2 THEN 2500
      ELSE 3000
      END);