How to use subquery in WHERE Clause in Oracle?

WHERE Clause in Oracle

By using conditional retrieval in Oracle SQL, you can retrieve particular rows of data from a table according to predefined criteria instead of retrieving all of the rows. In a SELECT statement, the WHERE clause is the main tool used to accomplish this. Oracle is instructed to return only rows that meet the provided criteria using the WHERE clause, which serves as a filter. It is a basic component of SQL queries that allows you to concentrate on the important data.

These criteria can be constructed using a variety of comparison operators in the WHERE clause, allowing for more complex filtering logic. You can use these operators to check values for ranges, equality, inequality, list membership, and even patterns.

Let’s examine some frequently used comparison operators and how they are used:

Equality Operator ()

If a column’s value is precisely equal to a given value, it can be checked using the equality operator (=). Finding records with exact matches is made easier using this. Note that Oracle often uses case-sensitive comparisons for character values.

Code Example:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section = 'F';

Code Output:

FEATURE         S  PAGE
--------------- - -----
Births          F     7
Classified      F     8
Obituaries      F     6
Doctor Is In    F     6

It returns every feature found in ‘Section F’.

Inequality Operators (, , )

You can use inequality operators such as !=, <>, or ^= to identify rows where the value of a column does not match a given value. Three distinct ways to say “not equal” are provided by Oracle to support various keyboard layouts.

Code Example:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Page <> 1;

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Editorials      A         12
Weather         C          2
Television      B          7
Births          F          7
Classified      F          8
Comics          C          4
Movies          B          4
Bridge          B          2
Obituaries      F          6
Doctor Is In    F          6

‘Page 1’ does not contain any features.

Relational Operators (, , , )

Values can be compared using these operators according to their magnitude: greater than, less than, greater than or equal to, less than or equal to. Although they can be used with characters, where comparison is based on alphabetical order, they are most frequently applied to numeric and date datatypes.

Code Example:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Page > 4;

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Editorials      A         12
Television      B          7
Births          F          7
Classified      F          8
Obituaries      F          6
Doctor Is In    F          6

With this query, every feature on a page number higher than four gets selected.

BETWEEN Operator

The beginning and finishing values of a range search are specified by the BETWEEN operator. It simplifies conditions that would otherwise require AND with two relational operators (e.g., value >= A AND value <= B).

Code Example:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Page BETWEEN 7 AND 10;

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Television      B          7
Births          F          7
Classified      F          8

Features on pages 7 through 10 inclusive are retrieved in this sample.

Operator

To check if the value in a column is one of the values in a given list, use the IN operator. Either a subquery or an explicit supply can produce this list. The NOT IN operator, on the other hand, determines whether a value is present in the list.

Code Example:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section IN ('A', 'B', 'F');

Code Output:

FEATURE         S  PAGE
--------------- - -----
National News   A     1
Editorials      A    12
Television      B     7
Modern Life     B     1
Movies          B     4
Bridge          B     2
Births          F     7
Classified      F     8
Obituaries      F     6
Doctor Is In    F     6

‘A’, ‘B’, or ‘F’ sections’ features are returned by this query.

Operator (with wildcards )

Character data can have patterns matched using the LIKE operator. It makes use of two unique wildcard characters:

• % (percent sign): Any letter, including zero, can be represented by the percent sign (%).
• _ (underscore): The symbol _ (underscore) denotes a single character.

Code Example:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Feature LIKE 'Mo%';

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Modern Life     B          1
Movies          B          4

Features that start with the letters “Mo” are found in this example. Features with ‘I’ in the third place may be represented by Feature LIKE '_ _ I%', whereas features with two ‘o’s could be represented by Feature LIKE '%o%o%'.

/ Operators

To precisely check for null values (missing or undefined data), these operators are employed. Because a null value is not equal to anything, not even another null, ordinary equality (=) or inequality (!=) operators do not function meaningfully with NULL values, hence it is imperative to utilise IS NULL or IS NOT NULL.

Code Example: Let’s consider the COMFORT table, which can contain NULL values for Precipitation.

SELECT City, SampleDate, Precipitation
FROM COMFORT
WHERE Precipitation IS NOT NULL;

Code Output:

CITY          SAMPLEDAT PRECIPITATION
------------- --------- -------------
SAN FRANCISCO 21-MAR-03            .5
SAN FRANCISCO 22-JUN-03            .1
SAN FRANCISCO 23-SEP-03            .1
SAN FRANCISCO 22-DEC-03           2.3
KEENE         21-MAR-03           4.4
KEENE         22-JUN-03           1.3
KEENE         22-DEC-03           3.9

This search yields rows with known precipitation data (not null).