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:

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:

  Copy Code

  SELECT 10 + 5; -- Returns 15
  

• Use `` to subtract:

  Copy Code

  SELECT 20 - 8; -- Returns 12
  

• Use `` to multiply:

  Copy Code

  SELECT 7 * 3; -- Returns 21
  

• Use / to divide:

  Copy Code

  SELECT 10 / 2; -- Returns 5
  

• Use % or MOD to return the remainder of a division:

  Copy Code

  SELECT 10 % 3; -- Returns 1
  

Comparison Operators

Comparison operators help you filter data based on conditions.

• Use = to check equality:

  Copy Code

  SELECT * FROM employees WHERE department = 'IT';
  

• Use != or <> to check inequality:

  Copy Code

  SELECT * FROM orders WHERE status != 'Shipped';
  

• Use > and < to compare values:

  Copy Code

  SELECT * FROM products WHERE price > 100;
  SELECT * FROM students WHERE age < 18;
  

• Use BETWEEN to find values in a range:

  Copy Code

  SELECT * FROM sales WHERE total BETWEEN 500 AND 1000;
  

• Use IN to match multiple values:

  Copy Code

  SELECT * FROM customers WHERE country IN ('USA', 'UK', 'Canada');
  

• Use LIKE for pattern matching with wildcards (% for multiple characters, _ for a single character):

  Copy Code

  SELECT * FROM users WHERE name LIKE 'A%'; -- Finds names starting with A
  

Logical Operators

Logical operators help you combine conditions in your queries.

• Use AND to match multiple conditions:

  Copy Code

  SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
  

• Use OR to match at least one condition:

  Copy Code

  SELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles';
  

• Use NOT to exclude results:

  Copy Code

  SELECT * FROM orders WHERE NOT status = 'Canceled';
  

Join Operators

Join operators allow you to combine data from multiple tables.

• Use INNER JOIN to return only matching records:

  Copy Code

  SELECT employees.name, departments.department_name
  FROM employees
  INNER JOIN departments ON employees.dept_id = departments.id;
  

• Use LEFT JOIN to return all records from the left table and matching records from the right table:

  Copy Code

  SELECT customers.name, orders.order_date
  FROM customers
  LEFT JOIN orders ON customers.id = orders.customer_id;
  

• Use RIGHT JOIN to return all records from the right table and matching records from the left table:

  Copy Code

  SELECT orders.order_id, customers.name
  FROM orders
  RIGHT JOIN customers ON orders.customer_id = customers.id;
  

• Use FULL JOIN to return all records from both tables:

  Copy Code

  SELECT 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 CASE to categorize data:

  Copy Code

  SELECT name,
    CASE
      WHEN age < 18 THEN 'Minor'
      ELSE 'Adult'
    END AS age_group
  FROM users;
  

• Use IS NULL to check for missing values:

  Copy Code

  SELECT * FROM employees WHERE email IS NULL;
  

• Use EXISTS to check if a subquery returns any results:

  Copy Code

  SELECT * 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.

SELECT * FROM customers WHERE country = 'USA';

Performing Calculations

Arithmetic operators let you manipulate numerical data directly in your queries.

SELECT price, price * 1.1 AS new_price FROM products;

Evaluating Conditions

Logical operators help you combine multiple conditions in a WHERE clause.

SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;

Handling NULL Values

You can use IS NULL to find records with missing values.

SELECT * FROM users WHERE phone_number IS NULL;

Joining Tables

Join operators allow you to retrieve data from multiple related tables.

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 >:

SELECT name, salary FROM employees WHERE salary > 75000;

Checking Customer Locations

List customers from certain countries using IN:

SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'UK');

Calculating Discounted Prices

Display product prices before and after applying a discount using *:

SELECT name, price, price * 0.9 AS discounted_price FROM products;

Filtering Data by Date

Find orders placed within a specific period using BETWEEN:

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:

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.

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.

SELECT employee_id, salary FROM employees WHERE salary % 2 = 0;

Searching with LIKE

The LIKE operator helps you find data based on patterns.

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.