Paradime | BETWEEN / IN / LIKE

SQL Keywords

BETWEEN / IN / LIKE

Feb 23, 2026

min read

Category: Comparison Operators

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

Comparison operators for filtering data. BETWEEN checks if a value is within a range (inclusive), IN checks if a value matches any in a list, and LIKE performs pattern matching on strings. Essential for WHERE clause filtering and conditional logic.

Syntax

BETWEEN:

column BETWEEN value1 AND value2
-- Equivalent to: column >= value1 AND column <= value2

column BETWEEN value1 AND value2
-- Equivalent to: column >= value1 AND column <= value2

column BETWEEN value1 AND value2
-- Equivalent to: column >= value1 AND column <= value2

IN:

column IN (value1, value2, value3, ...)
-- Or with subquery:
column IN (SELECT column FROM table)

column IN (value1, value2, value3, ...)
-- Or with subquery:
column IN (SELECT column FROM table)

column IN (value1, value2, value3, ...)
-- Or with subquery:
column IN (SELECT column FROM table)

LIKE:

column LIKE pattern
-- Wildcards:
-- % = any sequence of characters
-- _ = any single character

column LIKE pattern
-- Wildcards:
-- % = any sequence of characters
-- _ = any single character

column LIKE pattern
-- Wildcards:
-- % = any sequence of characters
-- _ = any single character

Platform-Specific Notes

All Platforms:

Standard SQL operators
Work identically across platforms
Can be combined with NOT
Case-sensitive by default (use ILIKE for case-insensitive in Snowflake)

Snowflake:

ILIKE for case-insensitive pattern matching
RLIKE for regex matching

BigQuery:

Case-insensitive by default for some collations
Use REGEXP_CONTAINS for regex

Databricks:

Standard SQL LIKE
RLIKE for regex patterns

Example 1: BETWEEN - Numeric and Date Ranges

All Platforms:

-- Numeric range
SELECT 
    product_name,
    price,
    category
FROM products
WHERE price BETWEEN 50 AND 150
ORDER BY price;

-- Date range
SELECT 
    order_id,
    customer_name,
    order_date,
    total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY order_date;

-- NOT BETWEEN
SELECT 
    employee_name,
    salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 100000

-- Numeric range
SELECT 
    product_name,
    price,
    category
FROM products
WHERE price BETWEEN 50 AND 150
ORDER BY price;

-- Date range
SELECT 
    order_id,
    customer_name,
    order_date,
    total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY order_date;

-- NOT BETWEEN
SELECT 
    employee_name,
    salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 100000

-- Numeric range
SELECT 
    product_name,
    price,
    category
FROM products
WHERE price BETWEEN 50 AND 150
ORDER BY price;

-- Date range
SELECT 
    order_id,
    customer_name,
    order_date,
    total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY order_date;

-- NOT BETWEEN
SELECT 
    employee_name,
    salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 100000

Sample Data (products table):

product_id	product_name	price	category
1	Mouse	29.99	Electronics
2	Keyboard	89.99	Electronics
3	Monitor	299.99	Electronics
4	Desk	199.99	Furniture
5	Chair	149.99	Furniture

Result (price BETWEEN 50 AND 150):

product_name	price	category
Keyboard	89.99	Electronics
Chair	149.99	Furniture

Example 2: IN - List of Values

All Platforms:

-- IN with literal values
SELECT 
    customer_name,
    country,
    status
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico')
  AND status IN ('Active', 'Premium');

-- IN with subquery
SELECT 
    product_name,
    price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE order_date >= '2024-01-01'
);

-- NOT IN
SELECT 
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2024-01-01'
)

-- IN with literal values
SELECT 
    customer_name,
    country,
    status
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico')
  AND status IN ('Active', 'Premium');

-- IN with subquery
SELECT 
    product_name,
    price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE order_date >= '2024-01-01'
);

-- NOT IN
SELECT 
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2024-01-01'
)

-- IN with literal values
SELECT 
    customer_name,
    country,
    status
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico')
  AND status IN ('Active', 'Premium');

-- IN with subquery
SELECT 
    product_name,
    price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE order_date >= '2024-01-01'
);

-- NOT IN
SELECT 
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2024-01-01'
)

Sample Data (customers table):

customer_id	customer_name	country	status
1	John Smith	USA	Active
2	Maria Garcia	Spain	Active
3	Bob Johnson	Canada	Premium
4	Sarah Lee	USA	Inactive
5	Carlos Ruiz	Mexico	Active

Result (country IN ('USA', 'Canada', 'Mexico') AND status IN ('Active', 'Premium')):

customer_name	country	status
John Smith	USA	Active
Bob Johnson	Canada	Premium
Carlos Ruiz	Mexico	Active

Example 3: LIKE - Pattern Matching

All Platforms:

-- Starts with pattern
SELECT email
FROM customers
WHERE email LIKE 'john%';

-- Ends with pattern
SELECT email
FROM customers
WHERE email LIKE '%@gmail.com';

-- Contains pattern
SELECT product_name
FROM products
WHERE product_name LIKE '%wireless%';

-- Specific position (_)
SELECT phone_number
FROM contacts
WHERE phone_number LIKE '(555) ___-____';

-- Multiple patterns
SELECT product_name
FROM products
WHERE product_name LIKE '%laptop%'
   OR product_name LIKE '%computer%'
   OR product_name LIKE '%notebook%'

-- Starts with pattern
SELECT email
FROM customers
WHERE email LIKE 'john%';

-- Ends with pattern
SELECT email
FROM customers
WHERE email LIKE '%@gmail.com';

-- Contains pattern
SELECT product_name
FROM products
WHERE product_name LIKE '%wireless%';

-- Specific position (_)
SELECT phone_number
FROM contacts
WHERE phone_number LIKE '(555) ___-____';

-- Multiple patterns
SELECT product_name
FROM products
WHERE product_name LIKE '%laptop%'
   OR product_name LIKE '%computer%'
   OR product_name LIKE '%notebook%'

-- Starts with pattern
SELECT email
FROM customers
WHERE email LIKE 'john%';

-- Ends with pattern
SELECT email
FROM customers
WHERE email LIKE '%@gmail.com';

-- Contains pattern
SELECT product_name
FROM products
WHERE product_name LIKE '%wireless%';

-- Specific position (_)
SELECT phone_number
FROM contacts
WHERE phone_number LIKE '(555) ___-____';

-- Multiple patterns
SELECT product_name
FROM products
WHERE product_name LIKE '%laptop%'
   OR product_name LIKE '%computer%'
   OR product_name LIKE '%notebook%'

Sample Data (customers table):

customer_id	customer_name	email
1	John Smith	john.smith@gmail.com
2	Johnny Doe	johnny@company.com
3	Maria Garcia	maria@gmail.com
4	Sarah Johnson	sarah@outlook.com

Result (email LIKE 'john%'):

email
john.smith@gmail.com
johnny@company.com

Result (email LIKE '%@gmail.com'):

email
john.smith@gmail.com
maria@gmail.com

Example 4: Case-Insensitive Matching

Snowflake (ILIKE):

SELECT product_name
FROM products
WHERE product_name ILIKE '%laptop%';  -- Matches: laptop, LAPTOP, Laptop

SELECT product_name
FROM products
WHERE product_name ILIKE '%laptop%';  -- Matches: laptop, LAPTOP, Laptop

SELECT product_name
FROM products
WHERE product_name ILIKE '%laptop%';  -- Matches: laptop, LAPTOP, Laptop

BigQuery / Databricks:

SELECT product_name
FROM products
WHERE LOWER(product_name) LIKE '%laptop%';  -- Convert to lowercase first

SELECT product_name
FROM products
WHERE LOWER(product_name) LIKE '%laptop%';  -- Convert to lowercase first

SELECT product_name
FROM products
WHERE LOWER(product_name) LIKE '%laptop%';  -- Convert to lowercase first

Example 5: Combining Operators

All Platforms:

SELECT 
    order_id,
    customer_name,
    order_date,
    total,
    status
FROM orders
WHERE 
    -- Date range
    order_date BETWEEN '2024-01-01' AND '2024-12-31'
    -- Amount range
    AND total BETWEEN 100 AND 1000
    -- Specific statuses
    AND status IN ('Shipped', 'Delivered')
    -- Customer name pattern
    AND customer_name LIKE 'J%'
ORDER BY order_date DESC

SELECT 
    order_id,
    customer_name,
    order_date,
    total,
    status
FROM orders
WHERE 
    -- Date range
    order_date BETWEEN '2024-01-01' AND '2024-12-31'
    -- Amount range
    AND total BETWEEN 100 AND 1000
    -- Specific statuses
    AND status IN ('Shipped', 'Delivered')
    -- Customer name pattern
    AND customer_name LIKE 'J%'
ORDER BY order_date DESC

SELECT 
    order_id,
    customer_name,
    order_date,
    total,
    status
FROM orders
WHERE 
    -- Date range
    order_date BETWEEN '2024-01-01' AND '2024-12-31'
    -- Amount range
    AND total BETWEEN 100 AND 1000
    -- Specific statuses
    AND status IN ('Shipped', 'Delivered')
    -- Customer name pattern
    AND customer_name LIKE 'J%'
ORDER BY order_date DESC

Sample Data:

order_id	customer_name	order_date	total	status
101	John Smith	2024-01-15	250.00	Shipped
102	Maria Garcia	2024-01-20	450.00	Delivered
103	James Brown	2024-02-10	150.00	Delivered
104	Jane Doe	2024-02-15	50.00	Shipped
105	Jake Wilson	2024-03-01	2000.00	Pending

Result:

order_id	customer_name	order_date	total	status
103	James Brown	2024-02-10	150.00	Delivered
101	John Smith	2024-01-15	250.00	Shipped

Example 6: LIKE Escape Characters

All Platforms:

-- Match literal % or _ using ESCAPE
SELECT description
FROM products
WHERE description LIKE '%\%%' ESCAPE '\\'  -- Find strings containing %
   OR description LIKE '%\_%' ESCAPE '\\';  -- Find strings containing _

-- Example: find products with "50% off" in description
SELECT product_name, description
FROM products
WHERE description LIKE '%50\% off%' ESCAPE '\\'

-- Match literal % or _ using ESCAPE
SELECT description
FROM products
WHERE description LIKE '%\%%' ESCAPE '\\'  -- Find strings containing %
   OR description LIKE '%\_%' ESCAPE '\\';  -- Find strings containing _

-- Example: find products with "50% off" in description
SELECT product_name, description
FROM products
WHERE description LIKE '%50\% off%' ESCAPE '\\'

-- Match literal % or _ using ESCAPE
SELECT description
FROM products
WHERE description LIKE '%\%%' ESCAPE '\\'  -- Find strings containing %
   OR description LIKE '%\_%' ESCAPE '\\';  -- Find strings containing _

-- Example: find products with "50% off" in description
SELECT product_name, description
FROM products
WHERE description LIKE '%50\% off%' ESCAPE '\\'

BETWEEN vs Comparison Operators

Expression	Equivalent	Notes
`x BETWEEN 10 AND 20`	`x >= 10 AND x <= 20`	Inclusive of both bounds
`x NOT BETWEEN 10 AND 20`	`x < 10 OR x > 20`	Exclusive of bounds

IN vs OR

Expression	Equivalent	Performance
`status IN ('A', 'B', 'C')`	`status = 'A' OR status = 'B' OR status = 'C'`	IN is cleaner and often faster
`id IN (SELECT...)`	Subquery join	Can use indexes

LIKE Pattern Examples

Pattern	Matches	Example
`'A%'`	Starts with A	Apple, Ant
`'%z'`	Ends with z	Quiz, Jazz
`'%or%'`	Contains 'or'	Word, Store
`'_at'`	3 chars ending in 'at'	Cat, Bat, Hat
`'[0-9]%'`	Starts with digit	1st, 5pm
`'%20__'`	Ends with 20XX	2023, 2024

NULL Handling

Important: NULL values require special handling

-- BETWEEN does not match NULL
SELECT * FROM table WHERE column BETWEEN 1 AND 10;  -- Excludes NULL

-- IN does not match NULL
SELECT * FROM table WHERE column IN (1, 2, NULL);  -- Excludes NULL

-- NOT IN with NULL can return unexpected results
SELECT * FROM table WHERE column NOT IN (1, 2, NULL);  -- Returns no rows!

-- Use IS NULL explicitly
SELECT * FROM table 
WHERE column BETWEEN 1 AND 10 OR column IS NULL

-- BETWEEN does not match NULL
SELECT * FROM table WHERE column BETWEEN 1 AND 10;  -- Excludes NULL

-- IN does not match NULL
SELECT * FROM table WHERE column IN (1, 2, NULL);  -- Excludes NULL

-- NOT IN with NULL can return unexpected results
SELECT * FROM table WHERE column NOT IN (1, 2, NULL);  -- Returns no rows!

-- Use IS NULL explicitly
SELECT * FROM table 
WHERE column BETWEEN 1 AND 10 OR column IS NULL

-- BETWEEN does not match NULL
SELECT * FROM table WHERE column BETWEEN 1 AND 10;  -- Excludes NULL

-- IN does not match NULL
SELECT * FROM table WHERE column IN (1, 2, NULL);  -- Excludes NULL

-- NOT IN with NULL can return unexpected results
SELECT * FROM table WHERE column NOT IN (1, 2, NULL);  -- Returns no rows!

-- Use IS NULL explicitly
SELECT * FROM table 
WHERE column BETWEEN 1 AND 10 OR column IS NULL

Performance Considerations

BETWEEN:

Fast: Uses indexes efficiently
Tip: Index columns used in BETWEEN

IN:

Small lists (<100 values): Very fast
Large lists: Consider temp table + JOIN
Subqueries: Optimized by query planner
Avoid: NOT IN with NULLs

LIKE:

Leading wildcard ('%abc'): Cannot use index (slow)
Trailing wildcard ('abc%'): Can use index (fast)
Consider: Full-text search for complex patterns

Common Use Cases

BETWEEN:

Date ranges: Monthly/quarterly reports
Price ranges: Product filtering
Age ranges: Demographics
Score ranges: Grade boundaries

IN:

Category filtering: Multiple selections
Whitelist/blacklist: Allowed/blocked values
Subquery results: Dynamic filtering
Multi-value parameters: User selections

LIKE:

Search functionality: Name/description search
Email validation: Domain checks
Code matching: Product SKUs
Partial matching: Auto-complete

Best Practices

Use BETWEEN for ranges: Cleaner than >= AND <=
Prefer IN over multiple ORs: More readable
Avoid leading wildcards in LIKE: Performance impact
Handle NULLs explicitly: Especially with NOT IN
Use ILIKE sparingly: Case-insensitive search is slower
Consider indexes: For frequently filtered columns
Validate input: Prevent SQL injection with LIKE
Test with NULLs: Verify behavior with NULL values

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources