---
layout: default
title: Operators and Expressions
parent: SQL Features
nav_order: 6
---
# Operators and Expressions
Oxibase supports a comprehensive set of SQL operators for building expressions in SELECT, WHERE, HAVING, and other clauses.
## Comparison Operators
| `=` | Equal | `WHERE id = 5` |
| `<>` or `!=` | Not equal | `WHERE status <> 'deleted'` |
| `<` | Less than | `WHERE price < 100` |
| `<=` | Less than or equal | `WHERE age <= 65` |
| `>` | Greater than | `WHERE score > 90` |
| `>=` | Greater than or equal | `WHERE date >= '2024-01-01'` |
## Logical Operators
| `AND` | Both conditions true | `WHERE a > 1 AND b < 10` |
| `OR` | Either condition true | `WHERE status = 'active' OR status = 'pending'` |
| `NOT` | Negates a condition | `WHERE NOT deleted` |
## Arithmetic Operators
| `+` | Addition | `SELECT price + tax` |
| `-` | Subtraction | `SELECT total - discount` |
| `*` | Multiplication | `SELECT quantity * price` |
| `/` | Division | `SELECT total / count` |
| `%` | Modulo (remainder) | `SELECT id % 10` |
## Bitwise Operators
| `&` | Bitwise AND | `SELECT flags & 0x0F` |
| `\|` | Bitwise OR | `SELECT flags \| 0x10` |
| `^` | Bitwise XOR | `SELECT a ^ b` |
| `~` | Bitwise NOT | `SELECT ~flags` |
| `<<` | Left shift | `SELECT 1 << 4` (returns 16) |
| `>>` | Right shift | `SELECT 16 >> 2` (returns 4) |
## String Operators
### Concatenation
```sql
-- Using CONCAT function
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
```
### Pattern Matching
#### LIKE (Case-Sensitive)
```sql
-- % matches any sequence of characters
SELECT * FROM products WHERE name LIKE 'Apple%'; -- Starts with 'Apple'
SELECT * FROM products WHERE name LIKE '%Phone'; -- Ends with 'Phone'
SELECT * FROM products WHERE name LIKE '%Pro%'; -- Contains 'Pro'
-- _ matches any single character
SELECT * FROM products WHERE code LIKE 'A_C'; -- Matches 'ABC', 'A1C', etc.
```
#### ILIKE (Case-Insensitive)
```sql
-- Same as LIKE but ignores case
SELECT * FROM products WHERE name ILIKE 'apple%'; -- Matches 'Apple', 'APPLE', 'apple'
SELECT * FROM users WHERE email ILIKE '%@gmail.com';
```
#### GLOB (Shell-Style Patterns)
```sql
-- * matches any sequence of characters (like % in LIKE)
SELECT * FROM files WHERE name GLOB '*.txt';
-- ? matches any single character (like _ in LIKE)
SELECT * FROM files WHERE name GLOB 'file?.dat';
-- [...] matches any character in the set
SELECT * FROM files WHERE name GLOB '[abc]*';
```
#### REGEXP (Regular Expressions)
```sql
-- Full regex pattern matching
SELECT * FROM logs WHERE message REGEXP 'error|warning';
SELECT * FROM users WHERE email REGEXP '^[a-z]+@[a-z]+\.[a-z]+$';
SELECT * FROM data WHERE value REGEXP '[0-9]{3}-[0-9]{4}';
```
## Range Operators
### BETWEEN
```sql
-- Inclusive range check
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
-- Equivalent to:
SELECT * FROM products WHERE price >= 10 AND price <= 100;
-- Works with dates
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;
```
### IN
```sql
-- Check if value is in a list
SELECT * FROM products WHERE category IN ('Electronics', 'Computers', 'Phones');
-- With numbers
SELECT * FROM orders WHERE status_id IN (1, 2, 3);
-- NOT IN
SELECT * FROM products WHERE category NOT IN ('Discontinued', 'Archived');
-- With subquery
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
```
## NULL Operators
```sql
-- Check for NULL
SELECT * FROM users WHERE deleted_at IS NULL;
-- Check for NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;
-- Note: = and <> don't work with NULL
SELECT * FROM users WHERE value = NULL; -- Always returns no rows!
SELECT * FROM users WHERE value IS NULL; -- Correct way
```
## CASE Expression
```sql
-- Simple CASE
SELECT name,
CASE status
WHEN 'A' THEN 'Active'
WHEN 'I' THEN 'Inactive'
WHEN 'P' THEN 'Pending'
ELSE 'Unknown'
END AS status_name
FROM users;
-- Searched CASE
SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Executive'
WHEN salary >= 70000 THEN 'Senior'
WHEN salary >= 40000 THEN 'Mid-level'
ELSE 'Entry-level'
END AS level
FROM employees;
```
## Operator Precedence
From highest to lowest:
1. `()` - Parentheses
2. `~` - Bitwise NOT
3. `*`, `/`, `%` - Multiplication, division, modulo
4. `+`, `-` - Addition, subtraction
5. `<<`, `>>` - Bit shifts
6. `&` - Bitwise AND
7. `^` - Bitwise XOR
8. `|` - Bitwise OR
9. `=`, `<>`, `<`, `<=`, `>`, `>=` - Comparisons
10. `NOT` - Logical NOT
11. `AND` - Logical AND
12. `OR` - Logical OR
Use parentheses to control evaluation nav_order:
```sql
-- Without parentheses: AND has higher precedence than OR
SELECT * FROM products WHERE category = 'A' OR category = 'B' AND price > 100;
-- Equivalent to: category = 'A' OR (category = 'B' AND price > 100)
-- With parentheses: explicit grouping
SELECT * FROM products WHERE (category = 'A' OR category = 'B') AND price > 100;
```
## Examples
### Complex WHERE Clauses
```sql
SELECT * FROM orders
WHERE status IN ('pending', 'processing')
AND total BETWEEN 100 AND 1000
AND customer_name ILIKE '%smith%'
AND created_at >= DATE_SUB(NOW(), 30, 'day');
```
### Computed Columns
```sql
SELECT
product_name,
quantity,
unit_price,
quantity * unit_price AS line_total,
CASE WHEN quantity >= 10 THEN 0.1 ELSE 0 END AS discount_rate,
quantity * unit_price * (1 - CASE WHEN quantity >= 10 THEN 0.1 ELSE 0 END) AS final_price
FROM order_items;
```
### Bitwise Flags
```sql
-- Check if specific bit is set
SELECT * FROM permissions WHERE (flags & 0x04) = 0x04; -- Check bit 2
-- Set a bit
-- Clear a bit
UPDATE permissions SET flags = flags & ~0x04 WHERE user_id = 1;
```