Constraining Queries with Conditions

When querying a database table, specifying constraints and conditions allows you to filter the result set to contain only relevant rows that meet defined criteria. This improves query performance, reduces network traffic and enables more useful analysis of the data.

In SQL, the WHERE clause is used to apply filters that constrain results to rows fulfilling your conditional logic.

Basic Syntax and Comparison Operators

The basic syntax for a constrained SELECT query is:

SELECT column1, column2,...
FROM mytable
WHERE condition1 AND/OR condition2

Numeric conditions leverage comparison operators like:

• = : Equals
• != or <> : Not equals
• < : Less than
• > : Greater than
• <= : Less than or equal to
• >= : Greater than or equal to

For example:

SELECT name, price
FROM products
WHERE price <= 100

Retrieves rows for products priced 100 or under. Multiple AND/OR conditions can be chained to further filter:

SELECT name, stock
FROM products
WHERE price < 50 AND stock > 10

Now only products under $50 with over 10 units in stock are returned.

Advanced Conditions

SQL offers additional operators for more advanced conditional logic:

BETWEEN provides numeric range filtering:

SELECT name, qty
FROM sales
WHERE qty BETWEEN 10 AND 50

NOT BETWEEN filters excluding a range.

IN and NOT IN check if values match any element in a set:

SELECT name
FROM products
WHERE category IN ('Appliances', 'Electronics')

For text, LIKE and NOT LIKE enable partial string matching using wildcards:

SELECT title
FROM books
WHERE title LIKE '%Cook%'

The % matches any # characters. _ matches a single character.

Joining Multiple Conditions

You can chain numerous AND/OR operators to conjure complex conditional logic:

SELECT *
FROM data
WHERE
  name LIKE 'A%' AND
  fruit IN ('Apple', 'Apricot') OR
  qty > 10 AND
  purchase_date BETWEEN '2020-01-01' AND '2020-12-31'

This returns rows for names starting with A that have Apple/Apricot for fruit OR have over 10 quantity AND were purchased last year.

Carefully constraining result sets by leveraging comparison operators and condition chaining helps optimize SQL query performance and analytical value.

Table of constraints