SQL Operators: Syntax, Usage, and Examples
SQL operators let you filter, compare, and manipulate data in your queries. You use them to perform arithmetic calculations, logical evaluations, and pattern matching, making them essential for writing efficient SQL statements.
How to Use SQL Operators
SQL operators work within queries to define relationships between values. You typically use them in SELECT, WHERE, and JOIN clauses to filter or modify results. Here’s the basic syntax:
sql
SELECT column_name
FROM table_name
WHERE column_name OPERATOR value;
Parentheses help you control the order of operations, ensuring your expressions evaluate correctly.
Types of SQL Operators
Arithmetic Operators
Arithmetic operators help you perform mathematical calculations on numerical values.
-
Use
+to add values:sqlSELECT 10 + 5; -- Returns 15 -
Use `` to subtract:
sqlSELECT 20 - 8; -- Returns 12 -
Use `` to multiply:
sqlSELECT 7 * 3; -- Returns 21 -
Use
/to divide:sqlSELECT 10 / 2; -- Returns 5 -
Use
%orMODto return the remainder of a division:sqlSELECT 10 % 3; -- Returns 1
Comparison Operators
Comparison operators help you filter data based on conditions.
-
Use
=to check equality:sqlSELECT * FROM employees WHERE department = 'IT'; -
Use
!=or<>to check inequality:sqlSELECT * FROM orders WHERE status != 'Shipped'; -
Use
>and<to compare values:sqlSELECT * FROM products WHERE price > 100; SELECT * FROM students WHERE age < 18; -
Use
BETWEENto find values in a range:sqlSELECT * FROM sales WHERE total BETWEEN 500 AND 1000; -
Use
INto match multiple values:sqlSELECT * FROM customers WHERE country IN ('USA', 'UK', 'Canada'); -
Use
LIKEfor pattern matching with wildcards (%for multiple characters,_for a single character):sqlSELECT * FROM users WHERE name LIKE 'A%'; -- Finds names starting with A
Logical Operators
Logical operators help you combine conditions in your queries.
-
Use
ANDto match multiple conditions:sqlSELECT * FROM employees WHERE department = 'HR' AND salary > 50000; -
Use
ORto match at least one condition:sqlSELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles'; -
Use
NOTto exclude results:sqlSELECT * FROM orders WHERE NOT status = 'Canceled';
Join Operators
Join operators allow you to combine data from multiple tables.
-
Use
INNER JOINto return only matching records:sqlSELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.dept_id = departments.id; -
Use
LEFT JOINto return all records from the left table and matching records from the right table:sqlSELECT customers.name, orders.order_date FROM customers LEFT JOIN orders ON customers.id = orders.customer_id; -
Use
RIGHT JOINto return all records from the right table and matching records from the left table:sqlSELECT orders.order_id, customers.name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id; -
Use
FULL JOINto return all records from both tables:sqlSELECT students.name, grades.grade FROM students FULL JOIN grades ON students.id = grades.student_id;
Special Operators
Some operators handle specific conditions in SQL queries.
-
Use
CASEto categorize data:sqlSELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS age_group FROM users; -
Use
IS NULLto check for missing values:sqlSELECT * FROM employees WHERE email IS NULL; -
Use
EXISTSto check if a subquery returns any results:sqlSELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customers.id = orders.customer_id);
When to Use SQL Operators
Filtering Data
Use comparison operators to retrieve only relevant records.
sql
SELECT * FROM customers WHERE country = 'USA';
Performing Calculations
Arithmetic operators let you manipulate numerical data directly in your queries.
sql
SELECT price, price * 1.1 AS new_price FROM products;
Evaluating Conditions
Logical operators help you combine multiple conditions in a WHERE clause.
sql
SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
Handling NULL Values
You can use IS NULL to find records with missing values.
sql
SELECT * FROM users WHERE phone_number IS NULL;
Joining Tables
Join operators allow you to retrieve data from multiple related tables.
sql
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;
Examples of SQL Operators
Finding High-Paying Jobs
Retrieve employees earning above a specific salary using >:
sql
SELECT name, salary FROM employees WHERE salary > 75000;
Checking Customer Locations
List customers from certain countries using IN:
sql
SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'UK');
Calculating Discounted Prices
Display product prices before and after applying a discount using *:
sql
SELECT name, price, price * 0.9 AS discounted_price FROM products;
Filtering Data by Date
Find orders placed within a specific period using BETWEEN:
sql
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
Categorizing Users by Age
Use CASE to classify users based on their age:
sql
SELECT name,
CASE
WHEN age < 18 THEN 'Underage'
WHEN age BETWEEN 18 AND 25 THEN 'Young Adult'
ELSE 'Adult'
END AS age_category
FROM users;
Learn More About SQL Operators
Controlling Execution Order
SQL processes expressions in a specific order. Use parentheses to ensure the correct sequence of operations.
sql
SELECT * FROM employees WHERE (department = 'HR' OR department = 'IT') AND salary > 60000;
Using the Modulo Operator
The modulo (% or MOD) operator helps you find values that meet specific conditions, such as even or odd numbers.
sql
SELECT employee_id, salary FROM employees WHERE salary % 2 = 0;
Searching with LIKE
The LIKE operator helps you find data based on patterns.
sql
SELECT * FROM customers WHERE name LIKE 'J%'; -- Names starting with J
SQL operators help you refine queries, perform calculations, and merge data efficiently. Mastering these tools will make your SQL skills stronger and your queries more effective.
Looking to dive deeper into SQL operators and other essential SQL concepts? Check out our SQL course.