List of SQL Relational Operators
ALL
Retrieves all rows. ALL is the default for a SELECT statement. Can be used with operators <, >, = to collect specific rows.
Examples:
Select * from employees where customer_id >= all
Select customer_id from customers where customer_id = 50005800 or customer_id = 50005600
AND
Retrieves rows that contain all the same values in the specified data. Can be used with operators <, >, = to collect specific rows.
Example: SELECT * FROM Customers_dataflex WHERE customercountry = 'USA' AND customerstate = 'WA'
ANY
Retrieves any rows. Can be used with operators <, >, and = to collect specific rows.
Examples:
Select * from employees where customer_id <= any
Select customer_id from customers where customer_id = 50005800 or customer_id = 50005600
BETWEEN
Finds values for fieldname in the range of minimumvalue to maximumvalue, inclusive. Equivalent to fieldname >= minimumvalue and fieldname <= maximumvalue. Using the BETWEEN operator results in better overall performance than using >= or <=.
Example: SELECT productid FROM products WHERE productid BETWEEN 1 and 100
EQUAL =
Retrieves rows that contain the same values as the specified data.
Example: SELECT * FROM Customers_dataflex WHERE customername = 'name'
GREATER THAN >
Retrieves rows whose values are greater than the specified data.
Example: SELECT * FROM Customer_dataflex WHERE recordnumber > 33
GREATER THAN OR EQUAL >=
Retrieves rows whose values are greater than or equal to the specified data.
Example: SELECT * FROM Customer_dataflex WHERE recordnumber >= 10
IN
Finds values for fieldname that match the list of values inside the parenthesis. This is equivalent to using the OR operator (such as fieldname = value1 OR fieldname = value2 OR ...). Using the IN() operator results in better overall performance.
Example: SELECT productid FROM products WHERE productid IN(1,3,5,7,11)
IS NOT NULL
Retrieves rows where information is not null or does exist.
Example: Select customer_address from customers where customer_address is not null
IS NULL
Retrieves rows where information is null or does not exist.
Example: Select customer_address from customers where customer_address is null
LESS THAN <
Retrieves rows whose values are less than the specified data.
Example: SELECT * FROM Customer_dataflex WHERE recordnumber < 10
LESS THAN OR EQUAL <=
Retrieves rows whose values are less than or equal to the specified data.
Example: SELECT * FROM Customer_dataflex WHERE recordnumber <= 10
LIKE
The SQL primary command LIKE, also known as the LIKE operator, can be used for wildcard comparison against String values to filter specific data. The LIKE operator has three special characters, each with a different usage: the percent sign (%), the underscore (_), and the caret (^).
% multi-character wildcardThe use of the percent sign (%) in a LIKE clause retrieves matches of zero or more characters.
Example: If the column TEST contains "ABCDEF," the expression TEST LIKE "%F" returns TRUE, the expression TEST LIKE "%C%" returns TRUE, and the expression TEST LIKE "%G" returns FALSE.
The Microsoft Jet Engine for Microsoft Access uses an asterisk (*) as the wildcard character instead of the percent sign (%). Replace the percent sign (%) wildcard with an asterisk (*) when using Microsoft Access, or when using other DAO-compliant applications.
_ single-character wildcard The use of an underscore (_) in a LIKE clause retrieves matches of a single character.
Example: If the column TEST contains "GHIJKLM," the expression TEST LIKE "G_IJKLM" returns TRUE, and the expression TEST LIKE "A_IJKLM" returns FALSE.
^ literal character identifier The use of the caret (^) in a LIKE cause permits the use of any of the wildcard characters %, _ or ^. If the ^ character is placed in front of either of the other two wildcard characters, the characters following are treated as normal literals.
Example: If the column TEST contains "10% of the data," the expression TEST LIKE "10^% of the data" returns TRUE, the expression TEST LIKE "10%data" returns TRUE, and the expression TEST LIKE "10data" returns FALSE.
NOT
Retrieves rows that contain different values from the specified data. Can be used with operators <, >, = to collect specific rows.
Example: SELECT * FROM Customers_dataflex WHERE customerstate is NOT = 'UT'
NOT EQUAL <>
Retrieves rows that contain different values from the specified data.
Example: SELECT * FROM Customers_dataflex WHERE customername <> 'name'
OR
Retrieves rows that contain any or all the values in the specified data. Can be used with operators <, >, = to collect specific rows.
Example: SELECT * FROM Customers_dataflex WHERE customerstate = 'UT' OR customerID = 'anthb'
UNION
The Union relational operator enables you to join information from two or more tables that have the same structure. Duplicate rows are eliminated.
Example:
SELECT * FROM testtable
UNION
SELECT * FROM testtable1
UNION ALL
The Union All relational operator enables you to join information from two or more tables that have the same structure. Duplicate rows are preserved.
Example:
SELECT * FROM testtable
UNION ALL
SELECT * FROM testtable1