BETWEEN CONDITION

This SQL tutorial explains how to use the SQL BETWEEN condition with syntax and examples.

DESCRIPTION

The SQL BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL BETWEEN Condition is:

expression BETWEEN value1 AND value2;

Parameters or Arguments

expression

A column or calculation.

value1 and value2

These values create an inclusive range that expression is compared to.

NOTE

• The SQL BETWEEN Condition will return the records where expression is within the range of value1 and value2(inclusive).

EXAMPLE - WITH NUMERIC

Let's look at some BETWEEN condition examples using numeric values. The following numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;

This SQL BETWEEN example would return all rows where the supplier_id is between 5000 and 5010 (inclusive). It is equivalent to the following SQL SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;

EXAMPLE - WITH DATE

Next, let's look at how you would use the BETWEEN condition with Dates. The following date example uses the SQL BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2003/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2003/12/31', 'yyyy/mm/dd');

This SQL BETWEEN condition example would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive). It would be equivalent to the following SQL SELECT statement:

SELECT *
FROM orders
WHERE order_date >= TO_DATE('2003/01/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2003/12/31','yyyy/mm/dd');

EXAMPLE - USING NOT OPERATOR

The SQL BETWEEN condition can also be combined with the SQL NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.

For example:

SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;

This SQL BETWEEN condition example would return all rows where the supplier_id was NOT between 5000 and 5500, inclusive. It would be equivalent to the following SQL SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;

IS NOT NULL CONDITION

This SQL tutorial explains how to use the SQL IS NOT NULL condition with syntax and examples.

DESCRIPTION

The SQL IS NOT NULL Condition is used to test for a NOT NULL value in SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL IS NOT NULL Condition is:

expression IS NOT NULL

Parameters or Arguments

expression

The expression to test for a NOT NULL value.

NOTE

• If expression is not a NULL value, the condition evaluates to TRUE.
• If expression is a NULL value, the condition evaluates to FALSE.

EXAMPLE - WITH SELECT STATEMENT

Let's look at an example of how to use SQL IS NOT NULL in a SQL SELECT statement:

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This SQL IS NOT NULL example will return all records from the customers table where the customer_name does not contain a NULL value.

EXAMPLE - WITH INSERT STATEMENT

Next, let's look at an example of how to use SQL IS NOT NULL in a SQL INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE account_no IS NOT NULL;

This SQL IS NOT NULL example will insert records into the suppliers table where the account_no does not contain a NULL value in the customers table.

EXAMPLE - WITH UPDATE STATEMENT

Next, let's look at an example of how to use SQL IS NOT NULL in a SQL UPDATE statement:

UPDATE suppliers
SET supplier_name = 'Apple'
WHERE supplier_name IS NOT NULL;

This SQL IS NOT NULL example will update records in the suppliers table where the supplier_name does not contain a NULL value.

EXAMPLE - WITH DELETE STATEMENT

Next, let's look at an example of how to use SQL IS NOT NULL in a SQL DELETE statement:

DELETE FROM customers
WHERE status IS NOT NULL;

This SQL IS NOT NULL example will delete all records from the customers table where the status does not contain a NULL value.

IS NULL CONDITION

This SQL tutorial explains how to use the SQL IS NULL condition with syntax and examples.

DESCRIPTION

The SQL IS NULL condition is used to test for a NULL value in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL IS NULL condition is:

expression IS NULL

Parameters or Arguments

expression

The expression to test for a NULL value.

NOTE

• If expression is a NULL value, the condition evaluates to TRUE.
• If expression is not a NULL value, the condition evaluates to FALSE.

EXAMPLE - WITH SELECT STATEMENT

Let's look at an example of how to use IS NULL in a SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_name IS NULL;

This SQL IS NULL example will return all records from the suppliers table where the supplier_name contains a NULL value.

EXAMPLE - WITH INSERT STATEMENT

Next, let's look at an example of how to use SQL IS NULL in an INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city IS NULL;

This SQL IS NULL example will insert records into the suppliers table where the city contains a NULL value.

EXAMPLE - WITH UPDATE STATEMENT

Next, let's look at an example of how to use SQL IS NULL in an UPDATE statement:

UPDATE suppliers
SET supplier_name = 'Apple'
WHERE supplier_name IS NULL;

This SQL IS NULL example will update records in the suppliers table where the supplier_name contains a NULL value.

EXAMPLE - WITH DELETE STATEMENT

Next, let's look at an example of how to use SQL IS NULL in a DELETE statement:

DELETE FROM suppliers
WHERE supplier_name IS NULL;

This SQL IS NULL example will delete all records from the suppliers table where the supplier_name contains a NULL value

NOT CONDITION

This SQL tutorial explains how to use the SQL NOT condition with syntax and examples.

DESCRIPTION

The SQL NOT Condition (also known as the SQL NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL NOT Condition is:

NOT condition

Parameters or Arguments

condition

This is the condition to negate. The opposite of the condition be must be met for the record to be included in the result set.

EXAMPLE - COMBINE WITH IN CONDITION

The SQL NOT condition can be combined with the IN Condition.

For example:

SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft' );

This SQL NOT example would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

EXAMPLE - COMBINE WITH IS NULL CONDITION

The SQL NOT condition can also be combined with the IS NULL Condition.

For example,

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This SQL NOT example would return all records from the customers table where the customer_name does not contain a NULL value.

EXAMPLE - COMBINE WITH LIKE CONDITION

The SQL NOT condition can also be combined with the LIKE Condition.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name NOT LIKE 'T%';

By placing the SQL NOT Operator in front of the SQL LIKE condition, you are able to retrieve all suppliers whose supplier_namedoes not start with 'T'.

EXAMPLE - COMBINE WITH BETWEEN CONDITION

The SQL NOT condition can also be combined with the BETWEEN Condition. Here is an example of how you would combine the NOT Operator with the BETWEEN Condition.

For example:

SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;

This SQL NOT example would return all rows where the supplier_id was NOT between 5000 and 5500, inclusive. It would be equivalent to the following SQL SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;

EXAMPLE - COMBINE WITH EXISTS CONDITION

The SQL NOT condition can also be combined with the EXISTS Condition.

For example,

SELECT *
FROM suppliers
WHERE NOT EXISTS (SELECT * 
                  FROM orders
                  WHERE suppliers.supplier_id = orders.supplier_id);

This SQL NOT example would return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

IN CONDITION

This SQL tutorial explains how to use the SQL IN condition with syntax and examples.

DESCRIPTION

The SQL IN condition is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL IN condition is:

expression IN (value1, value2, .... value_n);

Parameters or Arguments

expression

This is a value to test.

value1, value2 ..., alue_n

These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true. This is a quick method to test if any one of the values matches expression.

NOTE

• The SQL IN condition will return the records where expression is value1, value2..., or value_n.
• The SQL IN condition is also called the SQL IN operator.

EXAMPLE - WITH CHARACTER

Let's look at an IN condition example using character values.

The following is a SQL SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM suppliers
WHERE supplier_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

This SQL IN condition example would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the SQL IN condition makes the statement easier to read and more efficient.

EXAMPLE - WITH NUMERIC

Next, let's look at an IN condition example using numeric values.

For example:

SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);

This SQL IN condition example would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

EXAMPLE - USING NOT OPERATOR

Finally, let's look at an IN condition example using the NOT operator.

For example:

SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');

This SQL IN condition example would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

LIKE CONDITION

 

This SQL tutorial explains how to use the SQL LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises.

DESCRIPTION

The SQL LIKE condition allows you to use wildcards to perform pattern matching. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL LIKE Condition is:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression

A character expression such as a column or field.

pattern

A character expression that contains pattern matching. The wildcards that you can choose from are:

Wildcard	Explanation
%	Allows you to match any string of any length (including zero length)
_	Allows you to match on a single character

ESCAPE 'escape_character'

Optional. It allows you to pattern match on literal instances of a wildcard character such as % or _.

TIP: If you are pattern matching with char datatypes, remember that chars are padded with spaces at the end to fill the length of the field. This may give you unexpected results when you use the LIKE condition to pattern match at the end of a string.

DDL/DML FOR EXAMPLES

If 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 - USING % WILDCARD (PERCENT SIGN WILDCARD)

Let's explain how the % wildcard works in the SQL LIKE condition. Remember that the % wildcard matches any string of any length (including zero length).

In this first example, we want to find all of the records in the customers table where the customer's last_name begins with 'J'.

We have a table called customers with the following data in this example:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       8000  Anderson   Paige
       9000  Johnson    Derek       www.techonthenet.com

Enter the following SQL statement:

SELECT *
FROM customers
WHERE last_name LIKE 'J%';

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       9000  Johnson    Derek       www.techonthenet.com

In this example, there are 2 records in the customers table where the last_name starts with 'J'.

Using Multiple % Wildcards

You can also using the % wildcard multiple times with the LIKE condition.

Using the same customers table with the following data:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       8000  Anderson   Paige
       9000  Johnson    Derek       www.techonthenet.com

Let's try to find all last_name values from the customers table where the last_name contains the letter 'e'. Enter the following SQL statement:

SELECT last_name
FROM customers
WHERE last_name LIKE '%e%';

These are the results that you should see:

LAST_NAME
---------
Ferguson
Reynolds
Anderson

In this example, there are 3 last_name values that contain the letter 'e'.

EXAMPLE - USING _ WILDCARD (UNDERSCORE WILDCARD)

Next, let's explain how the _ wildcard (underscore wildcard) works in the LIKE condition. Remember that _ wildcard is looking for exactly one character, unlike the % wildcard.

SUPPLIER_ID  SUPPLIER_NAME  ACCT_NUMBER
-----------  -------------  -----------
        100  Microsoft      123170  
        200  RIM            398745
        300  Oracle         123175
        400  Google         693479
        500  Intel          123178
        600  Samsung        239003

Let's try to find all acct_number values from the suppliers table where acct_number starts with '12317' and has one more digit after. Enter the following SQL statement:

SELECT acct_number
FROM suppliers
WHERE acct_number LIKE '12317_';

These are the results that you should see:

ACCT_NUMBER
-----------
123170
123175
123178

In this example, there are 3 records that have an acct_number value that is 6 characters long and start with '12317'.

EXAMPLE - USING THE NOT OPERATOR

Next, let's see how you would use the NOT Operator with the LIKE condition.

Let's use our customers table again for this example:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       8000  Anderson   Paige
       9000  Johnson    Derek       www.techonthenet.com

And this time, let's look for all records in the customers table where the last_name does not start with 'J'. Enter the following SQL statement:

SELECT *
FROM customers
WHERE last_name NOT LIKE 'J%';

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       8000  Anderson   Paige

In this example, there are 4 last_name values that do not start with 'J'.

EXAMPLE - USING ESCAPE CHARACTERS

It is important to understand how to "Escape Characters" when pattern matching. You can escape % or _ and search for the literal versions instead.

Let's say you wanted to search for % as a literal in the LIKE condition. You can do this using an Escape character. In our example, we will use ! as the escape character in the LIKE condition.

NOTE: You can only define an escape character as a single character. It is best to choose a character that will not appear in your data very often such as ! or #.

In this example, we a table called test with the following data:

TEST_ID  TEST_VALUE
-------  ----------
      1  10%
      2  25%
      3  100
      4  99

We could return all records from the test table where the test_value contains the % literal. Enter the following SQL statement:

SELECT *
FROM test
WHERE test_value LIKE '%!%%' escape '!';

These are the results that you should see:

TEST_ID  TEST_VALUE
-------  ----------
      1  10%
      2  25%

This LIKE condition example identifies the ! character as an escape character. The first and last % values in the LIKE condition are treated as regular wildcards. The !% is an escaped % so it is treated as a literal % value.

You could further modify the above example and only return test_values that start with 1 and contain the % literal. Enter the following SQL statement:

SELECT *
FROM test
WHERE test_value LIKE '1%!%%' escape '!';

These are the results that you should see:

TEST_ID  TEST_VALUE
-------  ----------
      1  10%

This LIKE condition will only return one record this time. Because there is only one test_value that starts with 1 and contains the% literal.

FREQUENTLY ASKED QUESTIONS

Question: How do you incorporate the Oracle UPPER function with the SQL LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.

Answer: To answer this question, let's look at an example.

Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.

If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL SELECT statements:

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE ('TEST%');

OR

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE UPPER('test%')

These SQL SELECT statements use a combination of the Oracle UPPER function and the SQL LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test

COMBINING THE AND AND OR CONDITIONS

This SQL tutorial explains how to use the AND condition and the OR condition together in a single query with syntax and examples.

DESCRIPTION

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)

SYNTAX

The syntax for the SQL AND condition and OR condition together is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

The conditions that are evaluated to determine if the records will be selected.

NOTE

• The SQL AND & OR conditions allow you to test multiple conditions.
• Don't forget the order of operation parentheses!

EXAMPLE - WITH SELECT STATEMENT

Let's look at an example that combines the AND condition and OR condition in a SELECT query.

For example:

SELECT *
FROM suppliers
WHERE (city = 'New York' AND name = 'IBM')
OR (ranking >= 10);

This SQL SELECT example would return all suppliers that reside in New York whose name is IBM and all suppliers whose ranking is greater than or equal to 10. The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
OR (name = 'Hewlett Packard' AND city = 'Atlantic City')
OR (name = 'Gateway' AND status = 'Active' AND city = 'Burma');

This SQL SELECT statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.

EXAMPLE - WITH INSERT STATEMENT

This next example demonstrates how the SQL AND condition and SQL OR condition can be combined in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, customer_name
FROM customers
WHERE (customer_name = 'IBM' OR customer_name = 'Apple')
AND employees > 15;

This SQL AND and OR condition example would insert into the suppliers table, all account_no and customer_name records from the customers table whose customer_name is either IBM or Apple and where the employees is greater than 15.

EXAMPLE - WITH UPDATE STATEMENT

This example shows how the AND and OR conditions can be used in the UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'HP'
WHERE supplier_name = 'IBM'
OR (state = 'California' AND supplier_id = 50);

This SQL AND & OR condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was IBM or the state is California and the supplier_id is equal to 50.

EXAMPLE - WITH DELETE STATEMENT

Finally, this last AND & OR condition example demonstrates how the AND and OR condition can be used in the DELETE statement.

For example:

DELETE FROM suppliers
WHERE city = 'New York'
AND (product = 'PC computers' OR supplier_name = 'Dell');

This SQL AND and OR condition example would delete all suppliers from the suppliers table whose city was New York and either the product was PC computers or the supplier name was Dell.