In SQL Server there are lots of SQL operators which are widely used in SQL query to retrieve data. Some important SQL operators are given as follows:
Equal Operator (=):
The EQUAL (=) operator displays a record if condition is true.
Example: Using Equal Operator
select * from STUDENT_DETAIL where id = 102
Desired Output:
102 VARUN SINGH
AND Operator (AND):
The AND operator displays a record if both the first condition and the second condition is true.
Example: Using AND Operator
--- select data field from STUDENT_DETAIL table where both two condition are true
select * from STUDENT_DETAIL where id = 101 and name = 'arun singh'
Desired Output:
101 ARUN SINGH
OR Operator (OR):
The OR operator displays a record if either the first condition or the second condition is true.
Example: Using OR Operator
--- select data field from STUDENT_DETAIL table where one condition are true in twos
select * from STUDENT_DETAIL where id = 102 or id = 105
Desired Output:
102 VARUN SINGH
Combining AND &OR:
Example: Using AND & OR both
--- select data field from STUDENT_DETAIL table where both condition are true
select * from STUDENT_DETAIL where id = 102 and(name ='arun singh' or name = 'varun singh')
Desired Output:
102 VARUN SINGH
Not Equal (<>) Operator:
Not Equal (<>) operator displays a records when condition not equal to provide condition.
Example: Using Not Equal (<>) operator
--- select data field from STUDENT_DETAIL table where condition are true
select * from STUDENT_DETAIL where id <> 102
Desired Output:
101 ARUN SINGH
103 TARUN SINGH
SQL ‘Like’ Operator:
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. With ‘Like’ operator we must be use SQL wildcards for search data in specified pattern. There are lots of SQL wildcards which are widely used in SQL Database.
Example: Using ‘Like’ operator
-- Retrieve data from database table where specified pattern match in table data field(use like with ‘%’ wildcards)
select * from STUDENT_DETAIL where name like 'ar%'
-- Retrieve data from database table where specified pattern match in table data field(use with [charlist] wildcards)
select * from STUDENT_DETAIL where name like '[av]%'
-- Retrieve data from database table where specified pattern match in table data field(use with '_' wildcards)
select * from STUDENT_DETAIL where name like '_r%'
SQL IN Operator:
The IN operator allows you to specify multiple values in a WHERE clause and retrieve that data field value where condition match.
Example: Using IN operator
-- Select data from database table where specified jmultiple values)
select * from STUDENT_DETAIL where id in(101,102,103,104)
Desired Output:
101 ARUN SINGH
102 kajal
103 TARUN SINGH
Anonymous User
20-Jul-2019Thanks for sharing.