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 |
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.