Hi DBgeek Army,
Welcome back to the world of Oracle!
Where clause helps to select desired rows or helps to limit the number of rows returned by the SELECT statement. If we omit this clause in the query, Oracle performs the action for all the rows available in the Table specified in the query.
For example, if there is a SELECT statement, it will show all the records from the Table or if there there is an UPDATE statement, Oracle will update all the records available in the Table.
It is pretty easy to apply in the sql query. See the syntax below.
Syntax:
We just need to add keyword WHERE and a valid condition on the basis of what we want as an output.
Let’s select the Employees table of HR schema without WHERE clause.
SELECT
employee_id,
first_name,
last_name,
department_id
FROM
hr.employees;
Below table is showing only a few rows out of Employees Table but when you run it on your own System you will see all 107 records.
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
---|---|---|---|
100 | Steven | King | 90 |
101 | Neena | Kochhar | 90 |
102 | Lex | De Haan | 90 |
103 | Alexander | Hunold | 60 |
104 | Bruce | Ernst | 60 |
105 | David | Austin | 60 |
106 | Valli | Pataballa | 60 |
107 | Diana | Lorentz | 60 |
108 | Nancy | Greenberg | 100 |
109 | Daniel | Faviet | 100 |
Let’s select the same table once again with WHERE clause and in the output, we just want those Employees whose Department_id is 100.
SELECT
employee_id,
first_name,
last_name,
department_id
FROM
hr.employees
WHERE
department_id = 100;
When you run above SQL statement, you will see only those Employees whose Department_id is 100
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
---|---|---|---|
108 | Nancy | Greenberg | 100 |
109 | Daniel | Faviet | 100 |
110 | John | Chen | 100 |
111 | Ismael | Sciarra | 100 |
112 | Jose Manuel | Urman | 100 |
113 | Luis | Popp | 100 |
You can see DEPARTMENT_ID in the above table. All selected records have DEPARTMENT_ID as 100.
You can check UPDATE to understand how important WHERE clause is.
If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.
Thank you,
Kapil Kumar
dbgeek.in