Academic Block

Academic Block logo
Menu
SQL OPERATORS
Learn how to use SQL operators to compare values, perform calculations, combine conditions, and filter data in database queries.

What are SQL Operators?

SQL Operators are symbols and keywords used to perform operations on data. They allow you to compare values, perform arithmetic calculations, combine multiple conditions, and filter records.

SQL operators are commonly used with statements such as SELECT, WHERE, UPDATE, and DELETE.

Types of SQL Operators

Operator Type Examples Purpose
Arithmetic + - * / % Perform calculations
Comparison = <> != > < >= <= Compare values
Logical AND OR NOT Combine conditions
Special IN BETWEEN LIKE IS NULL Perform specialized comparisons

Arithmetic Operators

Arithmetic operators are used to perform mathematical calculations on numeric values. 

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL NOT NULL,
  quantity INTEGER NOT NULL
);

INSERT INTO products (product_id, product_name, price, quantity)
VALUES
  (1, 'Notebook', 120, 5),
  (2, 'Desk Lamp', 850, 2),
  (3, 'USB Cable', 300, 4);

SELECT
  product_name,
  price,
  quantity,
  price * quantity AS total_value,
  price + 50 AS price_with_charge,
  price - 20 AS discounted_price,
  price % 100 AS remainder
FROM products;

Addition (+)

The + operator adds two numeric values. 

SELECT 125 + 75 AS result;

Subtraction (-)

The - operator subtracts one numeric value from another. 

SELECT 500 - 175 AS remaining_amount;

Multiplication (*)

The * operator multiplies two numeric values. 

SELECT 45 * 12 AS result;

Division (/)

The / operator divides one numeric value by another. 

SELECT 144 / 12 AS result;

Modulo (%)

The % operator returns the remainder after integer division. 

SELECT 29 % 6 AS remainder;

Comparison Operators

Comparison operators compare two values. They are especially useful in WHERE clauses. 

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (employee_id, employee_name, salary)
VALUES
  (1, 'Anaya', 72000),
  (2, 'Vikram', 54000),
  (3, 'Diya', 68000),
  (4, 'Karan', 61000);

-- Equal to
SELECT employee_name, salary
FROM employees
WHERE salary = 68000;

Greater Than (>)

DROP TABLE IF EXISTS scores;

CREATE TABLE scores (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL,
  score INTEGER NOT NULL
);

INSERT INTO scores (student_id, student_name, score)
VALUES
  (1, 'Ravi', 82),
  (2, 'Tara', 94),
  (3, 'Neha', 76),
  (4, 'Arman', 88);

SELECT student_name, score
FROM scores
WHERE score > 85;

Less Than (<)

DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO inventory (item_id, item_name, stock)
VALUES
  (1, 'Paper', 120),
  (2, 'Markers', 35),
  (3, 'Folders', 80),
  (4, 'Pens', 25);

SELECT item_name, stock
FROM inventory
WHERE stock < 50;

Greater Than or Equal To (>=)

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT NOT NULL,
  duration INTEGER NOT NULL
);

INSERT INTO courses (course_id, course_name, duration)
VALUES
  (1, 'SQL Basics', 15),
  (2, 'Database Design', 25),
  (3, 'Advanced SQL', 30);

SELECT course_name, duration
FROM courses
WHERE duration >= 25;

Not Equal To (!= or <>)

In SQLite, both != and <> can be used to test whether two values are different. 

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
  task_id INTEGER PRIMARY KEY,
  task_name TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO tasks (task_id, task_name, status)
VALUES
  (1, 'Design homepage', 'Completed'),
  (2, 'Write documentation', 'Pending'),
  (3, 'Test database', 'Completed'),
  (4, 'Update images', 'Pending');

SELECT task_name, status
FROM tasks
WHERE status != 'Completed';

AND Operator

The AND operator returns records only when all specified conditions are true. 

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_id, product_name, category, price)
VALUES
  (1, 'Monitor', 'Electronics', 12500),
  (2, 'Keyboard', 'Electronics', 2500),
  (3, 'Office Chair', 'Furniture', 8500),
  (4, 'Desk Lamp', 'Furniture', 1800);

SELECT product_name, price
FROM products
WHERE category = 'Electronics'
  AND price > 3000;

OR Operator

The OR operator returns records when at least one of the specified conditions is true. 

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city)
VALUES
  (1, 'Aditi', 'Delhi'),
  (2, 'Kabir', 'Pune'),
  (3, 'Mohan', 'Mumbai'),
  (4, 'Sara', 'Jaipur');

SELECT customer_name, city
FROM customers
WHERE city = 'Delhi'
   OR city = 'Pune';

NOT Operator

The NOT operator reverses the result of a condition. 

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_name, status)
VALUES
  (1, 'Ira', 'Delivered'),
  (2, 'Dev', 'Pending'),
  (3, 'Rohan', 'Cancelled'),
  (4, 'Mira', 'Delivered');

SELECT order_id, customer_name, status
FROM orders
WHERE NOT status = 'Cancelled';

IN Operator

The IN operator checks whether a value matches one of the values in a specified list. 

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  department TEXT NOT NULL
);

INSERT INTO employees (employee_id, employee_name, department)
VALUES
  (1, 'Naina', 'Engineering'),
  (2, 'Rahul', 'Sales'),
  (3, 'Ishaan', 'Support'),
  (4, 'Pooja', 'Marketing');

SELECT employee_name, department
FROM employees
WHERE department IN ('Engineering', 'Support');

BETWEEN Operator

The BETWEEN operator checks whether a value falls within an inclusive range. 

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_id, product_name, price)
VALUES
  (1, 'Tablet Stand', 900),
  (2, 'Webcam', 2200),
  (3, 'Headphones', 3500),
  (4, 'Monitor', 12000);

SELECT product_name, price
FROM products
WHERE price BETWEEN 2000 AND 4000;

LIKE Operator

The LIKE operator performs pattern matching on text values. The % wildcard represents zero or more characters. 

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

INSERT INTO books (book_id, title)
VALUES
  (1, 'Learning SQL'),
  (2, 'Python Essentials'),
  (3, 'SQL for Beginners'),
  (4, 'Database Design');

SELECT title
FROM books
WHERE title LIKE '%SQL%';

IS NULL Operator

The IS NULL operator checks whether a column contains a NULL value. 

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
  contact_id INTEGER PRIMARY KEY,
  contact_name TEXT NOT NULL,
  phone TEXT
);

INSERT INTO contacts (contact_id, contact_name, phone)
VALUES
  (1, 'Aman', '9876543210'),
  (2, 'Riya', NULL),
  (3, 'Kunal', '9123456780'),
  (4, 'Tina', NULL);

SELECT contact_name, phone
FROM contacts
WHERE phone IS NULL;

Combining Multiple Operators

SQL operators can be combined to create more specific filtering conditions. Parentheses can be used to control the order in which conditions are evaluated. 

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT NOT NULL,
  category TEXT NOT NULL,
  fee INTEGER NOT NULL
);

INSERT INTO courses (course_id, course_name, category, fee)
VALUES
  (1, 'SQL Fundamentals', 'Database', 2500),
  (2, 'Web Design', 'Web', 1800),
  (3, 'Advanced SQL', 'Database', 4500),
  (4, 'JavaScript Basics', 'Web', 2200),
  (5, 'Data Modeling', 'Database', 3200);

SELECT course_name, category, fee
FROM courses
WHERE (category = 'Database' OR category = 'Web')
  AND fee BETWEEN 2000 AND 4000
ORDER BY fee;

Common SQL Operators

Operator Meaning Example
= Equal to price = 100
!= Not equal to price != 100
> Greater than price > 100
< Less than price < 100
>= Greater than or equal to price >= 100
<= Less than or equal to price <= 100
AND All conditions must be true age > 18 AND city = 'Delhi'
OR At least one condition must be true city = 'Delhi' OR city = 'Pune'
NOT Reverses a condition NOT status = 'Closed'
IN Matches values in a list city IN ('Delhi','Pune')
BETWEEN Checks an inclusive range price BETWEEN 100 AND 500
LIKE Matches a text pattern name LIKE 'A%'
IS NULL Checks for NULL phone IS NULL

Advantages of SQL Operators

  • Allow precise filtering of database records.
  • Make it possible to compare and calculate values.
  • Help combine multiple search conditions.
  • Support text pattern matching and range searches.
  • Make SQL queries more flexible and powerful.

Best Practices

  • Use parentheses when combining complex AND and OR conditions.
  • Use IS NULL instead of = NULL.
  • Use IN when checking against several specific values.
  • Use BETWEEN when filtering an inclusive range.
  • Use appropriate comparison operators for numeric and text values.
  • Test complex operator combinations with representative data.

SQL Operators are essential for comparing values, performing calculations, and building powerful filtering conditions. Mastering operators such as AND, OR, NOT, IN, BETWEEN, LIKE, and comparison operators will help you write more precise SQL queries.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click "Run Code" to see the result on the right.

📝 SQL Code
👁️ Preview (query result)

Click "Run Code" to see the result here.