Common predicates and logical operators

In the process of writing SQL queries, there are several frequently used predicates and logical operators, including in, between, and like. Logical operators include or and. Next we will summarize them separately.

In

In is generally used to determine whether a value is equal to at least one of a group of elements.For example, the following SQL query returns an order with an order ID equal to 10248, 10249, or 10250.

-- Set the database context to use tsqlfundamentals2008; goselect orderid, empid, orderdate from sales. orderswhere orderid in (10248, 10249, 10250 );

Query results:

NOTE: If orderid is of the string type, the Order ID in brackets must be enclosed in quotation marks.

Between

Between is generally used to determine whether a value is within the specified range, including two specified boundary values.For example, the following query returns all orders with the order ID between 10300 and 10310.

The SQL code is as follows:

-- Set the database context use tsqlfundamentals2008; goselect orderid, empid, orderdate from sales. orderswhere orderid between 10300 and 10310

Query results:

Note: Between can also be used for the date and time range.

Like

Like is generally used to determine whether a string value matches the specified pattern.For example, the following query returns all employees whose last names start with 'D.

SQL query code:

-- Set the database context use tsqlfundamentals2008; goselect empid, firstname, lastname from HR. employeeswhere lastname like n 'd % ';

Query results:

The following is a summary of logical operators. logical operators are mainly used to combine multiple logical expressions.

Or

OR operator.For example, the following query returns results after January 1, January 1, 2008.OrAll orders processed by staff ID 1, 2.

SQL query code:

-- Set the database context use tsqlfundamentals2008; goselect orderid, empid, orderdate from sales. orderswhere orderdate> = '123' or empid in (20080101)

Query results:

And

And represents and. For example, the following query returns results after January 1, January 1, 2008.AndAll orders processed by staff ID 1, 2.

SQL query code:

-- Set the database context use tsqlfundamentals2008; goselect orderid, empid, orderdate from sales. orderswhere orderdate> = '2016' and empid in (20080101)

Query results: