Meritshot Tutorials

  1. Home
  2. »
  3. SQL Operators
SQL

SQL Tutorial

SQL Operators

SQL operators are symbols or keywords used to perform operations on data in SQL queries. They help in manipulating and evaluating data, making it possible to filter, sort, and combine results in meaningful ways. SQL operators are essential for building complex queries and conditions.

Types of SQL Operators

  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators
  4. Bitwise Operators
  5. String Operators

Example:

Arithmetic Operators

Arithmetic operators perform basic mathematical operations.

  • Addition (+): Adds two values.

SELECT 10 + 5 AS AdditionResult;  — Output: 15

  • Subtraction (-): Subtracts one value from another.

SELECT 10 – 5 AS SubtractionResult;  — Output: 5

  • Multiplication (*): Multiplies two values.

SELECT 10 * 5 AS MultiplicationResult;  — Output: 50

  • Division (/): Divides one value by another.

SELECT 10 / 5 AS DivisionResult;  — Output: 2

  • Modulus (%): Returns the remainder of a division.

SELECT 10 % 3 AS ModulusResult;  — Output: 1

Comparison Operators

Comparison operators are used to compare two values.

  • Equal to (=): Checks if two values are equal.

SELECT * FROM employees WHERE salary = 50000;

  • Not equal to (<> or !=): Checks if two values are not equal.

SELECT * FROM employees WHERE salary <> 50000;

  • Greater than (>): Checks if one value is greater than another.

SELECT * FROM employees WHERE salary > 50000;

  • Less than (<): Checks if one value is less than another.

SELECT * FROM employees WHERE salary < 50000;

  • Greater than or equal to (>=): Checks if one value is greater than or equal to another.

SELECT * FROM employees WHERE salary >= 50000;

  • Less than or equal to (<=): Checks if one value is less than or equal to another.

SELECT * FROM employees WHERE salary <= 50000;

3. Logical Operators

Logical operators are used to combine multiple conditions.

  • AND: Returns true if all conditions are true.

SELECT * FROM employees WHERE salary > 50000 AND department_id = 1;

  • OR: Returns true if at least one of the conditions is true.

SELECT * FROM employees WHERE salary > 50000 OR department_id = 1;

  • NOT: Reverses the logical state of a condition.

SELECT * FROM employees WHERE NOT (salary > 50000);

4. Bitwise Operators

Bitwise operators perform operations on binary numbers.

  • Bitwise AND (&): Performs a bitwise AND operation.

SELECT 5 & 3 AS BitwiseAndResult;  — Output: 1

  • Bitwise OR (|): Performs a bitwise OR operation.

SELECT 5 | 3 AS BitwiseOrResult;  — Output: 7

  • Bitwise XOR (^): Performs a bitwise XOR operation.

SELECT 5 ^ 3 AS BitwiseXorResult;  — Output: 6

  • Bitwise NOT (~): Performs a bitwise NOT operation.

SELECT ~5 AS BitwiseNotResult;  — Output: -6

5. String Operators

String operators are used to concatenate or manipulate string values.

  • Concatenation (+ or || depending on the SQL dialect): Combines two strings.

SELECT CONCAT(first_name, ‘ ‘, last_name) AS FullName

FROM employees;

  • LIKE: Searches for a specified pattern in a column.

SELECT * FROM employees WHERE first_name LIKE ‘Raj%’;

  • BETWEEN: Checks if a value is within a specified range.

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

  • IN: Checks if a value matches any value in a list.

SELECT * FROM employees WHERE department_id IN (1, 2, 3);

Tips to Remember

  1. Understand Operator Precedence: Operators have a specific order of precedence which affects how expressions are evaluated. Arithmetic operators generally have higher precedence than comparison operators, which in turn have higher precedence than logical operators.
  2. Use Parentheses for Clarity: To ensure the correct order of operations and improve readability, use parentheses to group conditions.
  3. Be Aware of Data Types: Ensure that the data types of operands are compatible when using operators to avoid errors or unexpected results.
  4. Test Conditions Thoroughly: Test logical and comparison conditions to ensure they return the expected results before using them in production queries.

Frequently Asked Questions

Q1: Can you combine multiple logical operators in a single query?
A1: Yes, you can combine multiple logical operators using parentheses to ensure the correct order of evaluation.

Example:

SELECT * FROM employees

WHERE (salary > 50000 AND department_id = 1)

   OR (salary < 30000 AND department_id = 2);

Q2: How do bitwise operators work with non-integer types?
A2: Bitwise operators are typically used with integer types. Using them with non-integer types can result in errors or unexpected behavior.

Q3: Can string operators be used with non-string data types?
A3: String operators generally work with string data types. Ensure data is appropriately cast to strings if needed.

Example:

SELECT CONCAT(employee_id, ‘: ‘, first_name) AS EmployeeInfo

FROM employees;

Q4: What is the difference between AND and OR operators?
A4: AND requires all conditions to be true, whereas OR requires at least one condition to be true.

Q5: Can SQL operators be used in ORDER BY clauses?
A5: Yes, SQL operators can be used in ORDER BY clauses to sort data based on calculated values or conditions.

Example:

SELECT employee_id, salary

FROM employees

ORDER BY salary + 5000 DESC;

❮ Previous
Next ❯
ads website