What are the different SQL Comparison Operators?

In previous lessons, we have discussed many of the different SQL comparison operators that are available. These operators give you plenty of options to filter data in the WHERE clause of your SELECT statement. Having discussed many of them, I want there to be one consolidated place for you to get information on each of the SQL comparison operators.


The table below contains the SQL comparison operator symbol and a description of what it does:

SQL Comparison Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!= Not equal to
!< Not less than
!> Not greater than

The table above lists all of the SQL comparison operators that you can use in your WHERE clause. You may notice that the IN, BETWEEN, and LIKE operators are not included in this table. Those are technically considered logical operators and have slightly different properties. Accordingly, this is why the IN, BETWEEN, and LIKE operators each have lessons dedicated to them.

What is the IN operator?

The IN operator is a logical operator that can be used in the WHERE clause to filter based on a list of values. For example, using only the information learned in the previous lessons, if we wanted to return all rows in the Person.Person table where the FirstName is either “Mark”, “Michael”, or “Steven”:

SELECT *
FROM Person.Person
WHERE FirstName = 'Mark' OR FirstName = 'Michael' OR FirstName = 'Steven'

We are forced to repeat the OR operator along with the column name, the comparison operator and then the filtering value. However, the IN operator allows us to simplify the code:

SELECT *
FROM Person.Person
WHERE FirstName IN ('Mark', 'Michael', 'Steven')

Using the IN operator gives us the opportunity to separate the values we wish to filter on by commas enclosed by parentheses. This drastically reduces the amount of code we need to write and improves readability.

The previous examples have used text values as the filtering values. The IN operator can be used with dates as well as numeric examples. To find all rows in the Production.Product table where the ListPrice value is either $742.35, $333.42, $539.99, or $1003.91:

SELECT *
FROM Production.Product
WHERE ListPrice IN (742.35, 333.42, 539.99, 1003.91)

Similarly, we can also use the IN operator with dates. To find all rows from the Sales.SalesOrderHeader table where the OrderDate column value is either April 16, 2005, January 8, 2006 or August 20, 2006:

SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate IN ('4/16/2005', '1/8/2006', '8/20/2006')

Practice Problems

Given the examples from this lesson, try and complete the following practice problems:

  1. Find all rows in the Sales.SalesOrderHeader table where the OrderDate value is either May 6, 2006, July 18, 2006, or August 3, 2006.
  2. Find all rows in the HumanResources.Employee table where the JobTitle value is either Senior Tool Designer, Design Engineer, or Research and Development Manager.
  3. Return all rows from the Person.StateProvince table where the CountryRegionCode is either US, CA, DE.

How I do use the SQL WHERE clause for NULL values?

NULL is something you might see in the database quite a bit. NULL means there is no value. This is not a blank value – like a blank space – but truly nothing. This is a subtle but important difference. Those with some programming background will have some experience working with NULL values. (Ever get that irritating NULL value does not reference an object error when coding in C# or Java?)

It is possible to filter rows in the SQL WHERE clause by whether or not a NULL value appears. For example, to find all rows in the Person.Person table where the MiddleName value is not NULL:

SELECT *
FROM Person.Person
WHERE MiddleName IS NOT NULL

Notice how the filtering condition is the phrase “IS NOT NULL”. It will never be “<> NULL” which is the standard not equal to operator. NULLs require either “IS NULL” or “IS NOT NULL” which makes them unique to other data types.

To find all rows in the Sales.SalesOrderHeader table where the MiddleName column is NULL:

SELECT *
FROM Person.Person
WHERE MiddleName IS NULL

You can think of “IS NULL” as “equal to NULL” in some sense, but be sure to use the proper operator in your SQL WHERE clause. Just like we have done in other WHERE clause lessons, we can use the “AND” and “OR” operators to combine multiple filtering conditions. To find all rows in the Person.Person table where the MiddleName column has a NULL value or the Title column contains a non-NULL value:

SELECT *
FROM Person.Person
WHERE MiddleName IS NULL OR Title IS NOT NULL


Practice Problems

Complete the questions below to reinforce some of the concepts learned in this lesson about filtering NULL values using the SQL WHERE clause.

  1. Find all rows in the Sales.SalesOrderHeader table where the SalesPersonID value is NULL.
  2. Find all rows in the Production.Product table where the Color column is NULL
  3. Find all rows in the Production.Product table where the Color column is not NULL and the Size column is NULL.

How do I use the SQL WHERE clause with dates?

Using the WHERE clause with dates is very similar to filtering text. Date values must be enclosed with the single apostrophe. To find all rows that have an OrderDate equal to July 15, 2005 in the Sales.SalesOrderHeader table, we use the query:

SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate = '7/15/2005'

We can use different comparison operators with date values as well. To find all orders that took place after July 15, 2005 we can use the query:

SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate > '7/15/2005'

While the previous examples have used the slash symbol to separate the month, day, and year, you can also use the hyphen to separate the values:

SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate > '7-15-2005'

Just like with the other examples of the SQL WHERE clause, we can combine multiple filtering conditions using the “AND” or “OR” operator. To find all rows where the OrderDate was either July 15, 2005 or July 16, 2005 then we can execute the query:

SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate = '7/15/2005' OR OrderDate = '7/16/2005'

There are many date functions that can be used in a WHERE clause that give you a little more flexibility with your queries. An upcoming post will discuss these various date functions. As we get to later lessons, we will apply some of those to give you dynamic options.


Practice Problems

Given the examples completed in this lesson, try and complete some of the SQL WHERE clause practice problems below!

  1. Find all orders that took place before August 1, 2006 using the OrderDate column and the Sales.SalesorderHeader table.
  2. Find all rows in the HumanResources.Employee table with a BirthDate after September 12, 1970
  3. Find all orders in the Sales.SalesOrderHeader table with an OrderDate value that was after January 3, 2006 and before July 8, 2006.

Everyone should learn SQL.

While that seems like an incredibly bold statement, learning SQL can be one of the greatest career decisions you make. Between the potential salary, no longer relying on others to give you information, and being able to ask ANY question about your business, learning SQL enables you to do so much more than you could have done previously.

What is SQL?

SQL (Structured Query Language) is the primary language responsible for managing data and data structures contained within a relational database management system (RDBMS). Put simply, SQL is the language you use to interact with a database. There are four basic operations that SQL can perform: INSERTs, SELECTs, UPDATEs, and DELETEs (these are sometimes referred to as CRUD operations – create, read, update, delete).

What are some database concepts?

A database, in simplest terms, is an organized collection of data. A database is comprised of many tables, and a table stores rows of data in a structured format defined by the table’s columns. This represents the basic hierarchy of a database. When writing SQL queries, you are interacting with rows of data stored in tables contained within a database.

Ok, so the brief SQL introduction is done. Let’s get to the good stuff.

Why should you learn SQL?

1) Because you can earn really good money.

This point shouldn’t be too hard to sell; most people would love to be able to make a little more hard-earned cash. If you learn SQL, this is a great way to boost your income. The average SQL Developer salary, according to Indeed.com, was $92,000! SQL DBAs (Database Administrators – the people who make sure the databases are running properly and performing as best they can) have an average salary of $97,000! Considering that the United States median household income is around $52,000, an income nearly twice that of the median household income signifies a pretty good living.

2) Because SQL is one of the most sought-after skills by hiring employers.

Employers are seeking out those individuals who know SQL. It’s one thing to be able to earn a high salary (see point #1), but employers know the value that someone skilled in SQL brings to their company and want to employ these individuals. If you are looking to change employers, learning SQL makes you a highly sought after prospect.

Learn SQL Course

Interested in learning SQL? Click the image to check out our top-rated online course!

3) Because you can get an answer to any question you ask.

Think of all the questions you ask about your data on a regular basis. What were our sales last year? What is our average customer satisfaction rating? At what rate have we reduced expenses since last summer? These are all questions that can be answered if you learn SQL. Once you identify the database that stores the data you seek to explore, SQL empowers you to answer your own questions. You no longer need to rely on overly simplified, pre-built reports or emailing co-workers to track down data. SQL enables you to become a more self-sufficient employee.

4) Because you no longer have to deal with Excel crashing.

Ever crashed Excel because you had a ton of rows in a spreadsheet? Relational databases are designed to store millions and millions of rows of data (even billions of rows). SQL allows you to perform operations on this vast amount of data without having to worry about crashing a program not designed for that amount of information. Microsoft Excel is a great tool; it just isn’t meant to perform operations on tens of millions of rows at once. Relational databases are designed for those larger operations, and SQL is the language that allows you complete them.

5) Because you won’t ever have to ask yourself, “How did I make that report again?”.

SQL queries can be easily saved and re-used at any point in time. SQL code can also be edited with comments, so you can include clear descriptions directly in your query. When you work with Excel, you might be stuck doing a long multi-step process. First, you might have to export data from some standard report, then sort it, then add headers, then filter out certain values, and on, and on, and on, and on…

With SQL code, you simply write the code once, save it, re-open it, and re-run it if you ever need to produce a report twice. Think of the hours – even DAYS – that you could get back each month by not having to manually produce reports that could easily be automated.

Closing Remarks

Sure, I’m admittedly biased when I say “everyone should learn SQL”, but the facts don’t lie. SQL is an incredibly important and valuable skill employers desire. So much of our business is digital now. Digital means data; data means databases, and, to access those databases, you need SQL. Try reading a business journal and NOT see something about business intelligence or analytics. As organizations seek to do more with their data, they will need more individuals with the skills to access and analyze that data. SQL is the skill that enables you to do just that.

Take a look at our blog to find some free lessons to learn SQL!

How do I use the SQL WHERE clause with numbers?

In the last section we talked about how to use the SQL WHERE clause to filter rows of data based on text criteria. Now, we will look at how to filter rows of data based on numbers. Remember, we are using the AdventureWorks sample database that Microsoft provides for all examples in our SQL lesson. Please navigate to our introduction to SQL post that will provide information about how to get your environment set up.

The process is nearly identical in the WHERE clause; the only difference is that we will not need the single apostrophes around our filtering criteria. For example, to find all rows with a TotalDue value in Sales.SalesOrderHeader greater than 10,000 we would type:

SELECT *
FROM Sales.SalesOrderHeader
WHERE TotalDue > 10000

We do not place any commas in the numbers to specify a thousands separator. The number is not enclosed by single apostrophes like we are required to do with text values. All of the comparison operators discussed in the last section can be used when filtering numbers in the WHERE clause. For example, to find all rows in Production.Product with a SafetyStockLevel value of 1000:

SELECT *
FROM Production.Product
WHERE SafetyStockLevel = 1000

We can also use the “AND” and “OR” operators to use multiple filtering conditions in the same WHERE clause. To find all rows in Production.Product with a ListPrice greater than $2,000 and less than $3,000, we can use the query:

SELECT *
FROM Production.Product
WHERE ListPrice > 2000 AND ListPrice < 3000

The BETWEEN operator can complete the previous query with a little less typing involved. We will show you how to use the BETWEEN operator and others in a later post!

Practice Problems

Using some of the techniques learned in this lesson, try to complete the practice problems below.

  1. Find the rows in Production.Product with a ListPrice less than $250. Return only the Name and ListPrice columns.
  2. Find all rows in Sales.SalesOrderHeader where the TotalDue column is at least $15,000. Return only the SalesOrderNumber and TotalDue columns.
  3. Find all rows in HumanResources.Employee with a value greater than 50 in the VacationHours column.

How do I use the SQL WHERE clause with text?

The SQL WHERE clause allows you to filter rows based on a criteria you define. This lesson will focus specifically on filtering rows based on text values. Were we to find all rows in the Person.Person table where the FirstName contains the word “Michael”, we would type:

SELECT *
FROM Person.Person
WHERE FirstName = 'Michael'

Notice the equals comparison operator after the WHERE and before the filtering text in the WHERE clause. Every WHERE clause must include one of several possible comparison operators. See our lesson on SQL Comparison Operators that will be coming up soon for the complete list of operators you can use.

We can use the operators “AND” and “OR” to add more conditions to our WHERE clause. To find all rows from the Person.Person table with a FirstName value equal to “Michael” and a LastName value equal to “Allen”, we can type:

SELECT *
FROM Person.Person
WHERE FirstName = 'Michael' AND LastName = 'Allen'

We could use the “OR” operator to find all rows of Person.Person with a FirstName equal to “Michael” or equal to “James”:

SELECT *
FROM Person.Person
WHERE FirstName = 'Michael' OR FirstName = 'James'

We can find all rows of Production.Product where the Name column starts with a letter less than “K” with the query:

SELECT *
FROM Production.Product
WHERE Name < 'K'


Practice Problems

There are several other ways to filter rows of data based on text values in the SQL WHERE clause. Later sections will discuss other comparison operators and ways to filter text data. Complete the practice problems below to gain a bit more experience with the techniques learned in this lesson:

  1. Find all rows in Person.Person where the FirstName value equals “Steve”.
  2. Find all rows in Production.Product where the Color column is “Red”.
  3. Find all rows in Production.Product where the Color column is either “Red” or “Black”.

What is SELECT DISTINCT and how do I use it?

The previous lessons have discussed the basics of the SQL SELECT statement. There is an option that can be added to the SELECT statement to return a distinct list. For example, if I wanted to find the distinct list of all values in the Color column of Production.Product, I would execute the query:

SELECT DISTINCT Color
FROM Production.Product

A quick reminder: all of our practice problems and examples use the AdventureWorks sample database provided by Microsoft. For some help getting this set up, take a look at our introduction to SQL post!

Instead of returning all 504 rows of the Production.Product table’s color column, this query returns only the ten unique color values. Quickly being able to find the distinct values of a certain column can be very helpful. If we wanted to find the distinct list of job titles for employees in the AdventureWorks company, we could use the query:

SELECT DISTINCT JobTitle
FROM HumanResources.Employee

It is possible to create a SELECT DISTINCT statement on more than one column. To find the list of distinct combinations of FirstName and LastName values from the Person.Person table, use the query:

SELECT DISTINCT FirstName, LastName
FROM Person.Person

Using the lessons learned in a previous section about column aliases, we can write a SELECT DISTINCT SQL statement and use column aliases at the same time:

SELECT DISTINCT FirstName AS “First Name”, LastName AS “Last Name”
FROM Person.Person

Practice Problems

Complete the following practice problems to gain more experience using SELECT DISTINCT in your SQL queries.

  1. Find the distinct list of values in the Size column from Production.Product.
  2. Find the distinct MaritalStatus column values in the HumanResources.Employee table.
  3. Find the distinct list of CardType values from the Sales.CreditCard table.

What is a column alias?

A column alias is a name given to a column that changes how it appears in the results. For example, to return the Name column from the Production.Product table but have the column’s name appear as “Product Name” in the results:

SELECT Name AS [Product Name]
FROM Production.Product

If you look in the results section, you will see “Product Name” listed as the column name instead of just “Name”. There are a few reasons to use a column alias (we will talk about some of them in more advanced lessons), but one reason is to just make the columns easier to read. Many times, we will write a query and then bring the results into an Excel file to give to someone. Since most columns in a table do not have spaces or they use underscores in their place, column aliases give us a chance to clean them.

To return the top 100 SalesOrderNumbers from the Sales.SalesOrderHeader table but give it the column alias “Order Number”, we type:

SELECT TOP 100 SalesOrderNumber AS [Order Number]
FROM Sales.SalesOrderHeader

You can create a column alias without spaces if you choose. However, if spaces exist in the alias, you must enclose the alias in either double quotes (“) or square brackets.

Practice Problems

Complete the practice problems below to get more experience using column aliases.

  1. Return the top 100 rows from the Production.Product table. Select only the Name column and assign it the column alias “Standard Product Name”.
  2. Using the Person.Person table, return the FirstName and LastName columns. Assign the alias “First Name” and “Last Name” to the columns respectively.

Follow the link to review the basic SQL SELECT statement if you having trouble with some of the practice problems. As always, leave a comment if you have any questions!

A basic SQL SELECT statement allows users to return rows and columns of data from a database table. In the previous section, we focused solely on returning literal values and results of basic mathematical expressions. Now, we will be retrieving data that is stored in the tables. SELECT statements that only retrieve data with no filtering, grouping, or sorting have the standard form:

SELECT [Column 1], [Column 2], …, [Column N]
FROM [Schema Name].[Table Name]

Every SELECT statement includes specifying one or more columns and a table from which to retrieve the columns. Each table is contained within a database schema. Unless the schema is the default “dbo” schema, you must specify the schema name with the table name.

Remember that all of our examples will use the AdventureWorks sample database provided by Microsoft. Take a look at our introduction to SQL post to learn how to get started.

To return the Name and ProductNumber columns from the Production.Product table:

SELECT Name, ProductNumber
FROM Production.Product

Notice that the column names are separated by a comma. Your results contain two columns for each row of data – one for the Name column and another for the ProductNumber column.

It is possible to return all columns and all rows from a table using the asterisk (*) symbol. To return all rows and all columns from the Production.Product table, execute the query:

SELECT *
FROM Production.Product

With larger tables, it is not a good practice to return every row of data. You can limit the number of rows returned by your SQL SELECT statement with the “TOP” expression. To return only the top one hundred rows from the Production.Product table:

SELECT TOP 100 *
FROM Production.Product

Instead of specifying a number of rows to limit, you can limit by a percent of the total number of rows in a table. For example, to limit our query to return only the top ten percent of rows from the Production.Product table:

SELECT TOP 10 PERCENT *
FROM Production.Product

Given some of you will work on large databases, it is a good practice to use the TOP expression. You run the risk of sapping much of the server’s resources by not limiting the number of rows retrieved.


This gives you the basic information you need to complete a SQL SELECT statement. You should now be able to retrieve data from a table. You can either return all columns or specify a select few. You also have the ability to limit the number of rows returned to the results.