SQL for Beginners : Operators

An SQL operator is a special word or character used to perform tasks like complex comparisons, to basic arithmetic operations. SQL operators are primarily used within the WHERE clause of an SQL statement. This is the part of the statement that is used to filter data by a specific condition or conditions. There are six types of SQL operators that we are going to cover: Arithmetic, Bitwise, Comparison, Compound, Logical and String. In this chapter we will covering Logical and Comparison's operators.

Logical Operators

• And — is used in a where and joins clauses, to specify that the select, update and delete statement only will retrieve the values where the left and right side of the condition are true.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  WHERE Country = 'Japan' AND City = 'Tokyo'

UPDATE [WorldCities].[dbo].[WorldCities] SET City = 'JAPAN'  WHERE Country = 'Japan' AND City = 'Tokyo' 

 DELETE FROM [WorldCities].[dbo].[WorldCities] WHERE Country = 'Japan' AND City = 'Tokyo'

Select result using 'AND'

• Or— is used in a where and joins clauses, to specify that the select, update and delete statement only will retrieve or affect all the values in the left part OR the values of the right part.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Country = 'Japan' OR City = 'Tokyo' 

UPDATE [WorldCities].[dbo].[WorldCities] SET City = 'JAPAN'  WHERE Country = 'Japan' OR City = 'Tokyo' 

 DELETE FROM [WorldCities].[dbo].[WorldCities] WHERE Country = 'Japan' OR City = 'Tokyo' 

Select result using 'OR'

• Between— is used in a where and joins clauses, to specify that the select, update and delete statement only will retrieve or affect all the values within the intervals.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE [Population] BETWEEN 2000000 and 2500000

UPDATE [WorldCities].[dbo].[WorldCities] SET City = 'JAPAN'  WHERE [Population] BETWEEN 2000000 and 2500000

DELETE [WorldCities].[dbo].[WorldCities]  WHERE [Population] BETWEEN 2000000 and 2500000

Select result using 'BETWEEN'

• In— Used in a clause to retrieve or affect all the values within the IN.

You can specify more than one value.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  WHERE City IN ('Osaka', 'Tokyo')

UPDATE [WorldCities].[dbo].[WorldCities] SET Country = 'JAPAN' WHERE City IN ('Osaka', 'Tokyo')

DELETE [WorldCities].[dbo].[WorldCities]  WHERE City IN ('Osaka', 'Tokyo')

Select result using 'IN'

• Not In— Used in the where and joins clauses, to retrieve or affect all the values that are not within the IN. You can specify more than one value.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  WHERE Country NOT IN ('Japan', 'India')

UPDATE [WorldCities].[dbo].[WorldCities] SET Country = Country  WHERE Country 	NOT IN ('Japan', 'India')
	
DELETE [WorldCities].[dbo].[WorldCities]   WHERE Country NOT IN ('Japan', 'India')

Select result using 'NOT IN'

• Like— Used in a where and joins clauses, to retrieve or affect all the values to to search for a specified pattern in a column. When you write the ‘%’ after a word or a letter you want that your statement shows you or affect all the rows that start with ‘MEX’ and end with whatever another word or letter in this case.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  		WHERE Country LIKE '%MEX'

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  WHERE Country LIKE '%MEX%'   *This result is the same result of 'MEX%'*

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  WHERE Country LIKE 'MEX%'

If you write the ‘%Mex’ the result will be all the countries that end with 'MEX' If you write the ‘%Mex%’ the result will be all the countries that contain 'MEX'

If you write the ‘Mex%’ the result will be all the countries that begin with 'MEX'

Select result using 'LIKE'

• Union/Union All – is used to retrieve all the information that show two or more queries. To use an UNION you have to retrieve the same number of columns and each query. If you need to write an order clause, you have to write it at the end of the last Select statement. The main difference between Union and Union all is, UNION retrieve all the distinct values between all the selects whilst and UNION ALL retrieve all the values including duplicate values.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].		     [WorldCities]  WHERE Country = 'Dominican Republic'
UNION
SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities]  WHERE Country = 'Colombia'

Select result using 'UNION'

• Except– is used to retrieve all the values that are in one select statement result but not in the other select statement result. To use an EXCEPT you have to retrieve the same number of columns and each query. If you need to write an order clause, you have to write it at the end of the last Select statement.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] 
EXCEPT 
SELECT  City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population = 5000000

Select result using 'EXCEPT'

• Exist– is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] w1
WHERE EXISTS  
(SELECT  City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] w2 
WHERE Country ='IRAQ' AND w1.Country = w2.Country)

Select result using 'WHERE EXISTS'

• Comparison Operators (>, <, <=, >=, =, (<> or !=))

These operators are used to retrieve information comparing with numeric values either greater than, less than, less or equal than, greater or equal than, equal and different than.

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] 		WHERE Population > 2000000	

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population >= 2000000	

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population < 2000000	

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population <= 2000000	
	
SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population = 2000000		

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population <> 2000000

SELECT City, Country ,[Population] FROM [WorldCities].[dbo].[WorldCities] WHERE Population != 2000000

Select result using '>,>=,<,<=,=,<>' operators.

Set of data used:

WorldCities.txt

Download TXT • 11KB