Relational Operators in SQL Server

In SQL Server, relational operators are used to compare values and establish relationships between data stored in tables. These operators allow us to perform logical comparisons to filter data based on specific conditions.

Understanding relational operators is fundamental for querying and manipulating data effectively in SQL Server. In this article, we’ll explore relational operators in SQL Server by covering concepts, examples in detail.

Introduction to Relational Operators

Relational operators in SQL Server are used to compare values and determine the relationship between them. These operators evaluate conditions and return a boolean (true or false) result based on whether the specified condition is met. Common relational operators include:

• Equal to (=): This operator checks if two values are the same. Generally, it compares values for equality.
• Not equal to (<> or !=): The not equal to operator checks if two values are different. It returns true if the values are not equal.
• Greater than (>): This operator checks if the left value is greater than the right value.
• Less than (<): Similar to the greater than operator, but it checks if the left operand is less than the right operand.
• Greater than or equal to (>=): This operator checks if the left operand is greater than or equal to the right operand.
• Less than or equal to (<=): Similar to the greater than or equal to operator, but it checks if the left operand is less than or equal to the right operand.

Examples of Relational Operators in SQL Server

Let’s set up an Environment:

To understand Relational Operators in SQL Server we need a table on which we will perform various operations and queries. Here we will consider a table called Persons which contains data as shown below:

CREATE TABLE Persons (
    EmpID INT,
    Name NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2),
    Age INT
);

INSERT INTO Persons(EmpID, Name, Department, Salary, Age)
VALUES
    (1, 'John Doe', 'Sales', 60000.00, 35),
    (2, 'Jane Smith', 'Marketing', 45000.00, 28),
    (3, 'Robert Johnson', 'HR', 55000.00, 42),
    (4, 'Lisa Brown', 'Sales', 70000.00, 45),
    (5, 'Michael Davis', 'IT', 65000.00, 30);

PersonsID	Name	Department	Salary	Age
1	John Doe	Sales	60000	35
2	Jane Smith	Marketing	45000	28
3	Robert Johnson	HR	55000	42
4	Lisa Brown	Sales	70000	45
5	Michael Davis	IT	65000	30

Example 1: Equal to (=)

Select Persons whose department is ‘Sales’

-- Select Persons whose department is 'Sales'
SELECT * FROM Persons
WHERE Department = 'Sales';

PersonsID	Name	Department	Salary	Age
1	John Doe	Sales	60000	35
4	Lisa Brown	Sales	70000	45

Example 2: Not equal to (<>)

Select employees whose department is not ‘Sales’

-- Select Persons whose Salary is not equal to 100
SELECT * FROM Persons
WHERE Salary<> 45000;

PersonsID	Name	Department	Salary	Age
1	John Doe	Sales	60000	35
3	Robert Johnson	HR	55000	42
4	Lisa Brown	Sales	70000	45
5	Michael Davis	IT	65000	30

Example 3: Greater than (>)

Select Persons whose Age is greater than 35

-- Select Person with a Age  greater than 35
SELECT * FROM Persons
WHERE Age> 35;

PersonsID	Name	Department	Salary	Age
3	Robert Johnson	HR	55000	42
4	Lisa Brown	Sales	70000	45

Example 4: Less than (<)

Select Persons whose age is less than 35

-- Select Persons whose age is less than 30
SELECT * FROM Persons
WHERE Age < 35;

PersonsID	Name	Department	Salary	Age
2	Jane Smith	Marketing	45000	28
5	Michael Davis	IT	65000	30

Example 5: Greater than or equal to (>=)

Select Persons whose salary is greater than or equal to 60000

-- Select Persons whose salary is greater than or equal to 60000
SELECT * FROM Persons
WHERE salary >= 60000;

PersonsID	Name	Department	Salary	Age
1	John Doe	Sales	60000	35
4	Lisa Brown	Sales	70000	45
5	Michael Davis	IT	65000	30

Example 6: Less than or equal to (<=)

Select Persons whose Salary is less than or equal to 60000

-- Select products with a Salary less than or equal to 60000
SELECT * FROM Persons
WHERE Salary <= 60000;

PersonsID	Name	Department	Salary	Age
1	John Doe	Sales	60000	35
2	Jane Smith	Marketing	45000	28
3	Robert Johnson	HR	55000	42

Example 7: Combining Relational Operators with Logical Operators

Relational operators can also be combined with logical operators (AND, OR, NOT) to create complex conditions in SQL queries:

-- Select products with a price between 50 and 100
SELECT * FROM Persons
WHERE PersonsID >= 2 AND PersonsID <= 4;

PersonsID	Name	Department	Salary	Age
2	Jane Smith	Marketing	45000	28
3	Robert Johnson	HR	55000	42
4	Lisa Brown	Sales	70000	45

Conclusion

Relational operators play a important role in SQL Server for comparing values and filtering data based on specific conditions. By understanding the relational operators such as equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to, users can perform logical comparisons and establish relationships between data in tables. In this article, we explored relational operators in SQL Server and provided examples of their usage in SQL queries, and demonstrated the output of these queries.