What operators exist in SQL?

In SQL, there are several categories of operators:

• Comparison operators: Used for comparing values in expressions.

  • = (Equal)
  • <> or != (Not equal)
  • > (Greater than)
  • < (Less than)
  • >= (Greater than or equal to)
  • <= (Less than or equal to)
  • BETWEEN (Within a range)
  • LIKE (Pattern matching)
  • IN (Value in list)
  • IS NULL (Null value)
  • ANY (Any value from subquery)
  • ALL (All values from subquery)
  • EXISTS (Subquery returns at least one row)

• Logical operators: Used to combine conditions.

  • AND (Logical AND)
  • OR (Logical OR)
  • NOT (Logical NOT)

• Arithmetic operators: Used for mathematical operations.

  • + (Addition)
  • - (Subtraction)
  • * (Multiplication)
  • / (Division)
  • % (Modulo - depends on DBMS)

• Bitwise operators: (Used for bit manipulation, not supported equally across all DBMS)

  • & (Bitwise AND)
  • | (Bitwise OR)
  • ^ (Bitwise XOR)
  • ~ (Bitwise NOT)
  • << (Bitwise shift left)
  • >> (Bitwise shift right)

• String concatenation operators:

  • || (String concatenation - depends on DBMS)

• Special operators:

  • UNION (Combine results of two or more SELECT statements)
  • UNION ALL (Combine results including duplicates)
  • INTERSECT (Returns rows present in all SELECT statements)
  • EXCEPT or MINUS (Returns rows from the first SELECT not present in the second)

Example of using WHERE with comparison and logical operators:

-- Select clients older than 30 and from the city "Moscow"
SELECT *
FROM Customers
WHERE Age > 30 AND City = 'Moscow';

Example of using LIKE and BETWEEN:

-- Select products with names starting with "A"
SELECT *
FROM Products
WHERE ProductName LIKE 'A%';

-- Select orders within a specific period
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';