SQL Predicates: Practical Guide

SQL predicates are the building blocks of effective data filtering and manipulation in databases.

They allow you to specify conditions that determine which rows are affected by your SQL statements.

In this article, we’ll dive deep into SQL predicates, exploring their various forms and applications through practical examples.

Understanding SQL Predicates

At its core, a SQL predicate is a condition that evaluates to TRUE, FALSE, or UNKNOWN. These predicates are used in WHERE clauses, JOIN conditions, and HAVING clauses to filter data based on specific criteria.

Let’s start with a simple example:

SELECT * FROM employees WHERE salary > 50000;

In this query, salary > 50000 is the predicate. It filters the result set to include only employees with a salary greater than 50,000.

Types of SQL Predicates

SQL supports various types of predicates. Let’s explore each type with examples.

Comparison Predicates

Comparison predicates use operators like =, <>, <, >, <=, and >= to compare values.

SELECT product_name, price 
FROM products 
WHERE price <= 100;

This query returns all products priced at or below 100.

BETWEEN Predicate

The BETWEEN predicate checks if a value falls within a specified range.

SELECT order_id, order_date 
FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

This query fetches all orders placed in the year 2023.

IN Predicate

The IN predicate checks if a value matches any value in a list or subquery.

SELECT employee_name, department 
FROM employees 
WHERE department IN ('Sales', 'Marketing', 'IT');

This query retrieves employees working in Sales, Marketing, or IT departments.

LIKE Predicate

The LIKE predicate is used for pattern matching in string comparisons.

SELECT customer_name, email 
FROM customers 
WHERE email LIKE '%@gmail.com';

This query finds all customers with Gmail addresses.

NULL Predicate

The NULL predicate checks for NULL values.

SELECT product_name, description 
FROM products 
WHERE description IS NULL;

This query lists all products that don’t have a description.

Combining Predicates

You can combine multiple predicates using logical operators AND, OR, and NOT to create more complex conditions.

SELECT employee_name, salary, hire_date 
FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 60000 
  AND hire_date >= '2020-01-01';

This query finds high-earning sales and marketing employees hired since 2020.

Subquery Predicates

Subqueries can be used as predicates, allowing for more dynamic and complex filtering.

SELECT product_name, price 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

This query returns products priced above the average price.

Exists Predicate

The EXISTS predicate checks for the existence of rows that satisfy a subquery.

SELECT customer_name 
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id 
    AND o.order_date >= '2023-01-01'
);

This query finds customers who have placed orders in 2023.

Build a Web Application using only SQL

Building an entire web app using only SQL is not only possible but can be incredibly efficient with the right tools. With Five, a rapid application development environment, you can create a fully responsive SQL app that runs on top of a MySQL database without needing any additional programming languages or frameworks.

The step-by-step tutorial available here will guide you through creating:

• A MySQL-based database for your web app.
• A form-based user interface that dynamically interacts with the data.
• Interactive charts and comprehensive PDF reports directly from SQL queries.
• A secure login system, turning your SQL queries into a multiuser app.

Using Five, the development process is simplified. The platform automatically generates forms, charts, reports, and other front-end elements based on your SQL schema, meaning you can build an entire application with just SQL and Five’s intuitive visual tools.

By the end the tutorial available here “How To Build a SQL App“, you will have a fully functional web app that uses SQL to manage data, generate reports, and provide a dynamic user experience — all without writing any additional code outside SQL.

Get started with free access to Five’s development environment and begin building your SQL-powered web app today!

Build Your SQL Web App In 3 Steps
Start Developing Today

Real-World Applications of SQL Predicates

Let’s explore some practical scenarios where SQL predicates shine.

E-commerce Order Analysis

Imagine you’re analyzing order data for an e-commerce platform. You want to find high-value orders from repeat customers.

SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total_amount > 1000
  AND EXISTS (
    SELECT 1 
    FROM orders prev
    WHERE prev.customer_id = o.customer_id
      AND prev.order_date < o.order_date
  )
ORDER BY o.total_amount DESC;

This query finds orders over $1000 from customers who have made previous purchases.

Employee Performance Evaluation

In an HR scenario, you might want to identify top-performing employees for a bonus program.

SELECT e.employee_name, e.department, p.performance_score
FROM employees e
JOIN performance_reviews p ON e.employee_id = p.employee_id
WHERE p.review_year = 2023
  AND p.performance_score > (
    SELECT AVG(performance_score) + 1
    FROM performance_reviews
    WHERE review_year = 2023
  )
  AND e.hire_date <= '2022-12-31'
ORDER BY p.performance_score DESC;

This query finds employees who have been with the company for at least a year and have performance scores more than one point above the average.

Inventory Management

For a retail business, identifying slow-moving inventory is crucial. Here’s how you might use predicates to find such items:

SELECT p.product_id, p.product_name, p.stock_quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE p.stock_quantity > 50
  AND (
    oi.order_id IS NULL
    OR p.product_id NOT IN (
      SELECT DISTINCT product_id
      FROM order_items
      WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    )
  )
ORDER BY p.stock_quantity DESC;

This query identifies products with more than 50 units in stock that haven’t been ordered in the last 90 days.

Advanced Predicate Techniques

As you become more comfortable with basic predicates, you can explore more advanced techniques to make your queries even more effective.

Window Functions with Predicates

Window functions can be combined with predicates to perform complex analyses.

SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM 
    employees
WHERE 
    salary > (
        SELECT AVG(salary) * 1.2 
        FROM employees e2 
        WHERE e2.department = employees.department
    )
ORDER BY 
    department, salary DESC;

This query finds employees who earn more than 120% of their department’s average salary.

Dynamic Predicates

Sometimes, you need to build predicates dynamically based on user input or application logic. While the exact implementation depends on your programming environment, here’s a conceptual example:

-- Assuming @min_price and @max_price are user-provided parameters
SELECT product_name, price
FROM products
WHERE 1=1
  AND (@min_price IS NULL OR price >= @min_price)
  AND (@max_price IS NULL OR price <= @max_price)
  AND category IN (SELECT value FROM STRING_SPLIT(@categories, ','))
ORDER BY price;

This query allows for flexible price range filtering and multiple category selection.

Optimizing Predicates for Performance

While predicates are essential for data filtering, they can impact query performance if not used carefully. Here are some tips to optimize your predicate usage:

1. Use appropriate indexes: Ensure that columns used in predicates are properly indexed.
2. Avoid functions in predicates: Using functions on indexed columns can prevent the use of indexes. Instead of:

   WHERE YEAR(order_date) = 2023

Use:

   WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'

3. Be mindful of data types: Comparing different data types can lead to implicit conversions and poor performance.
4. Use EXISTS instead of IN for large subqueries: EXISTS can be more efficient for large result sets.
5. Consider using JOIN instead of correlated subqueries: In many cases, JOINs can be more efficient.

Conclusion

SQL predicates are essential tools for precise data retrieval and manipulation. As you’ve seen throughout this article, they offer a wide range of possibilities for filtering and analyzing your data. Here are some key takeaways and practical next steps:

1. Start simple: Begin with basic comparison predicates in your WHERE clauses. Practice using operators like =, <>, <, >, <=, and >= to filter your data effectively.
2. Combine predicates: Use AND, OR, and NOT to create more complex conditions. This allows you to narrow down your results with greater precision.
3. Explore different predicate types: Experiment with BETWEEN, IN, LIKE, and NULL predicates. Each has its strengths in different scenarios.