The WHERE clause lets you filter rows according to some condition. In this post, we will see how we can use the oracle where clause to get more specific result set. So, let’s get started.
The columns after the SELECT clause lets you limit or control the number of columns in the result. Likewise, WHERE clause limits the number of rows the SQL query fetches.
This page contains (Skip to topic)..
Oracle where clause syntax
SELECT col1, col2, col3, ....., colN FROM table1 WHERE <conditional expressions>;
The where clause is used to add some extra rules or conditions to the rows. Only those rows appear in the result set which satisfies the conditional expressions in the where clause.
The where clause is also applicable to update, delete clauses. However, we will only see the use of where with a select clause in here.
Oracle where clause example
Consider the countries table in the HR schema. It has three columns country_id, country_name, region_id.
Here, different countries can belong to a region. So, let us say we want to find all the countries which belong to region id 3.
Before that let us see the data in the countries table by executing the query below.
SELECT * FROM countries;
This gives us all the countries in the countries table.
We will add filter putting some condition to the region_id column. So, the query to find all countries belong to region id 3 is as follows.
SELECT * FROM countries WHERE region_id = 3;
When you execute this query, it gives you only rows with region id as 3. We can see that Australia, China, India, Japan, Malaysia, Singapore countries belong to region id 3.
Oracle where clause more example
Let us say we want to find out the country_id of India with the help of a query. We can do that as follows.
SELECT country_id FROM countries WHERE country_name = 'India'; CO -- IN
We understood the basic SQL WHERE clause syntax, usage. Now, we will talk about filtering rows based on string patterns, with numeric, with the date datatypes in the coming posts.