Sign in Subscribe
2 min read

SQL - Operators and Examples

SQL operators are symbols or keywords that perform operations on one or more expressions or values in a SQL statement. They are used in conditions, calculations, and comparisons within queries. Here are some common SQL operators and query examples:

1. Arithmetic Operators:

  • + (Addition): Adds two values
    SELECT salary + bonus AS total_income FROM employees;

    - -We add salary and bonus columns to create the resulting column as total_income
  • - (Subtraction): Subtracts the right operand from the left operand
    SELECT price - discount AS discounted_price FROM products;

    - - We subtract price and discount columns to create the resulting column as discounted_price

  • * (Multiplication): Multiplies two values
    SELECT quantity * price AS total_cost FROM order_details;

  • / (Division): Divides the left operand by the right operand
    SELECT revenue / num_customers AS avg_revenue_per_customer FROM sales_summary;

  • % (Modulus): Returns the remainder of the division of the left operand by the right operandSELECT employee_id, salary % 1000 AS remainder FROM employees;

2. Comparison Operators:

  • =, !=, <>, <, >, <=, >=: These operators are used to compare values
    SELECT product_name FROM products WHERE price > 100;
  • BETWEEN ... AND ...: Checks if a value is within a range
    SELECT order_date FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
  • IN: Checks if a value is within a set of values
    SELECT product_name FROM products WHERE category_id IN (1, 2, 3);
  • LIKE: Compares a value to a pattern using wildcard characters
    SELECT customer_name FROM customers WHERE customer_name LIKE 'A%';

3. Logical Operators:

  • AND: Returns true if both conditions are true
    SELECT product_name FROM products WHERE price > 50 AND stock_quantity > 0;
  • OR: Returns true if at least one of the conditions is true
    SELECT order_id FROM orders WHERE order_status = 'Shipped' OR order_date < '2023-01-01';
  • NOT: Negates a condition, making true conditions false and vice versa
    SELECT product_name FROM products WHERE NOT discontinued;
  • IS NULL: Tests if a value is NULL
    SELECT product_name FROM products WHERE supplier_id IS NULL;
  • IS NOT NULL: Tests if a value is not NULL
    SELECT employee_name FROM employees WHERE hire_date IS NOT NULL;
  • It is important to note in tables and columns NULL values are not the same as EMPTY values. For example, an empty string as "" is not considered NULL
  • But also depending on how your database engine interprets Boolean values, 0 or 1s can be interpreted as NULL/Not NULL

5. Concatenation Operator:

  • || (Double Pipe): Concatenates two strings
    SELECT first_name || ' ' || last_name AS full_name FROM employees;

6. Aggregate Operators:

Aggregate operators are used to find a single value by calculating the data from a column.

When we use aggregate operators, it's important to understand which data types and columns we are dealing with. These functions won't work on any column or we might be querying for irrelevant data. It is up to the SQL engineer to understand what they are querying for.

  • COUNT(): Counts the number of rows
    SELECT COUNT(*) AS num_orders FROM orders;

    - The COUNT() function gets the total number of records for a column. The COUNT(*) aggregates ALL records from a table. So the above query will provide one row showing the number of records in the orders table
  • SUM(): Calculates the sum of values in a column
    SELECT SUM(quantity) AS total_quantity FROM order_details;
  • AVG(): Calculates the average of values in a column
    SELECT AVG(price) AS avg_price FROM products;
  • MIN(): Retrieves the minimum value in a column
    SELECT MIN(order_date) AS earliest_order FROM orders;
  • MAX(): Retrieves the maximum value in a column
    SELECT MAX(salary) AS highest_salary FROM employees;

The use cases for each of these queries and operators will depend on your business requirements or business logic based on what type of data you are trying retrieve to build certain insights or get a clearer picture from your existing data.

Understanding and effectively using these operators is crucial for constructing SQL queries that retrieve, filter, and manipulate data in relational databases.

Published by:

Tahsin