Operators in SQL

Operators

SQL operators are used in queries to perform operations on data. They can be categorized into different types:

1. Arithmetic Operators:

Used for mathematical calculations.

Operator Description Example
+ Addition SELECT 10 + 5; → 15
- Subtraction SELECT 10 - 5; → 5
* Multiplication SELECT 10 * 5; → 50
/ Division SELECT 10 / 5; → 2
% Modulus (Remainder) SELECT 10 % 3; → 1

2. Comparison Operators:

Used to compare values.

Operator Description Example:
= Equal to SELECT * FROM sales WHERE amount = 500;
!= or <> Not equal to SELECT * FROM sales WHERE amount <> 500;
> Greater than SELECT * FROM orders WHERE quantity > 10;
< Less than SELECT * FROM orders WHERE quantity < 10;
>= Greater than or equal to SELECT * FROM sales WHERE amount >= 1000; <= Less than or equal to SELECT * FROM sales WHERE amount <= 1000;

3. Logical Operators:

Used to filter records based on multiple conditions.

Operator Description Example AND Both conditions must be true SELECT * FROM sales WHERE amount > 500 AND region = 'East'; OR At least one condition must be true SELECT * FROM sales WHERE amount > 500 OR region = 'West'; NOT Negates a condition SELECT * FROM orders WHERE NOT status = 'Cancelled';

4. Bitwise Operators:

Used for bitwise operations.

Get Teju Senior Data engineer|Azuure|Databricks | AWS’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Operator Description Example & Bitwise AND SELECT 5 & 3; → 1 ` ` Bitwise OR ^ Bitwise XOR SELECT 5 ^ 3; → 6

5. Other Operators:

Operator Description Example BETWEEN Checks if value is within a range SELECT * FROM sales WHERE amount BETWEEN 100 AND 500;
IN Checks if value exists in a list SELECT * FROM sales WHERE region IN ('East', 'West');
LIKE Matches a pattern SELECT * FROM orders WHERE customer_name LIKE 'A%';
IS NULL Checks for NULL values SELECT * FROM sales WHERE discount IS NULL;
Create Table: Sales and Orders:

CREATE TABLE Sales (
    sales_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    amount DECIMAL(10,2),
    region VARCHAR(50)
);
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    product_name VARCHAR(100),
    quantity INT,
    status VARCHAR(50)
);

Applying Operators with Examples:
1. Arithmetic Operators:

SELECT sales_id, amount, amount * 1.1 AS increased_amount FROM Sales;

(Here, we increase the amount by 10%)
2. Comparison Operators:

SELECT * FROM Sales WHERE amount > 500;

(Returns sales where the amount is greater than 500)
3. Logical Operators:

SELECT * FROM Orders WHERE quantity > 10 AND status = 'Delivered';

(Returns orders where quantity is greater than 10 and status is Delivered)
4. LIKE Operator:

SELECT * FROM Orders WHERE customer_name LIKE 'A%';

(Returns orders where customer name starts with ‘A’)
5. IN Operator:

SELECT * FROM Sales WHERE region IN ('East', 'West');

(Returns sales where the region is either East or West)
6. BETWEEN Operator:

SELECT * FROM Sales WHERE amount BETWEEN 100 AND 500;

(Returns sales where the amount is between 100 and 500)

Thanks For Reading!!!!
If you found this article insightful, please click the ‘clap’ button and follow me on Medium and LinkedIn. For any questions or advice, feel free to reach out to me on LinkedIn.