Show
This SQL tutorial explores all of the comparison operators used in SQL to test for equality and inequality, as well as the more advanced operators. DescriptionComparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in SQL:
DDL/DML for ExamplesIf you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database! Get DDL/DML Example - Equality OperatorIn SQL, you can use the In this example, we have a table called suppliers with the following data:
Enter the following SQL statement: Try It SELECT * FROM suppliers WHERE supplier_name = 'Microsoft'; There will be 1 record selected. These are the results that you should see:
In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to Microsoft. Example - Inequality OperatorIn SQL, there are two ways to test for inequality in a query. You can use either the Let's use the same suppliers table as the previous example.
Enter the following SQL statement to test for inequality using the Try It SELECT * FROM suppliers WHERE supplier_name <> 'Microsoft'; OR enter this next SQL statement to use the Try It SELECT * FROM suppliers WHERE supplier_name != 'Microsoft'; There will be 8 records selected. These are the results you should see with either one of the SQL statements:
In the example, both SELECT statements would return all rows from the suppliers table where the supplier_name is not equal to Microsoft. Example - Greater Than OperatorYou can use the In this example, we have a table called customers with the following data:
Enter the following SQL statement: Try It SELECT * FROM customers WHERE customer_id > 6000; There will be 3 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than 6000. A customer_id equal to 6000 would not be included in the result set. Example - Greater Than or Equal OperatorIn SQL, you can use the Let's use the same customers table as the previous example.
Enter the following SQL statement: Try It SELECT * FROM customers WHERE customer_id >= 6000; There will be 4 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than or equal to 6000. In this case, the supplier_id equal to 6000 would be included in the result set. Example - Less Than OperatorYou can use the In this example, we have a table called products with the following data:
Enter the following SQL statement: Try It SELECT * FROM products WHERE product_id < 5; There will be 4 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the products table where the product_id is less than 5. A product_id equal to 5 would not be included in the result set. Example - Less Than or Equal OperatorIn SQL, you can use the Let's use the same products table as the previous example.
Enter the following SQL statement: Try It SELECT * FROM products WHERE product_id <= 5; There will be 5 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the products table where the product_id is less than or equal to 5. In this case, the product_id equal to 5 would be included in the result set. Example - Advanced OperatorsTo learn more about the advanced comparison operators, we've written tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.
Which of the following are examples of comparison operators?Comparison operators — operators that compare values and return true or false . The operators include: > , < , >= , <= , === , and !== .
Which operator is used to combine columns of character strings to other columns?Use CONCATENATE, one of the text functions, to join two or more text strings into one string.
When using the like condition to search for symbols which character can you use as the default escape option?Removes the special significance of the next character (to match a literal % or _ or \ by specifying \% or \_ or \\ ) Using the backslash ( \ ) symbol as the default escape character(when DEFAULTESCCHAR is not set) is the Informix® extension to the ANSI/ISO-standard for SQL.
Which clause would you include in a select statement to sort the rows returned by the lastname column?The ORDER BY clause can be used in the SELECT query to sort the result in ascending or descending order of one or more columns.
|