Where vs Having Clause in SQL

Here we'll see how to use WHERE clause in PL/SQL and what need to be taken care while using where clause with character, numeric and data types.
Also we look into the comparison between WHERE and HAVING clause including syntax, how to use both in the same query and there performance comparison.

Table employe used:

WHERE clause

Basically, the SQL WHERE clause allows you to filter the results from an SQL statement.
The WHERE clause extends the SELECT statement by providing the language to restrict rows returned based on one or more conditions and it always follows the FROM clause.


Numeric-Based Conditions
Conditions must be formulated appropriately for different column data types. 
The conditions restricting rows based on numeric columns can be specified in several different ways.



SELECT LAST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY = 1000;

SELECT LAST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY = '3000';








The first query retrieve the data where salary is greater than 1000 and second query retrieve the data where salary is greater than 3000 but the first query specify the number 1000, while second enclose the number within quotes like  a character literal.
Both format are acceptable to oracle since an implicit data type conversion is performed when necessary.

Character-Based Conditions
Conditions determining which rows are selected based on character data, are specified by enclosing character literals in the conditional clause, within single quotes.

SELECT EMP_LAST_NAME
FROM EMPLOYE
WHERE JOB_ID='CLERK';






If you tried specifying the character literal without the quotes, an Oracle error would be raised. Remember that character literal data is case sensitive, so the following WHERE clauses are not equivalent.

Clause 1: where job_id=CLERK;Clause 2: where job_id='ClerK';
Clause 3: where job_id='clerk';
Clause 1 generates an “ORA-00904: “SA_REP”: invalid identifier” error since the literal SA_REP is not wrapped in single quotes.
Clause 2 and Clause 3 are syntactically correct but not equivalent.  Further, neither of these clauses yields any data.

Character-based expressions form either one or both parts of a condition separated by a conditional operator.
For instance:
where 'A '||last_name||first_name = 'A King'

Date-Based Conditions
DATE columns are useful when storing date and time information. Date literals must be enclosed in single quotation marks just like character data; otherwise an error is raised.
The literals are automatically converted into DATE values based on the default date format, which is DD-MON-RR.


SELECT EMPLOYEE_ID FROM JOB_HISTORY
WHERE START_DATE = END_DATE;This query testing the equality between two dates.

Note: however, that DATE values are only equal to each other if there is an exact match between all their components including day, month, year, hours, minutes, and seconds.

SELECT EMP_NAME,SALARY FROM EMPLOYE 
WHERE START_DATE='11-AUG-2013';







Here  the START_DATE column is compared to the character literal: '11-AUG-2013'.This is acceptable to the Oracle server.

SELECT EMP_NAME,SALARY FROM EMPLOYE 
WHERE START_DATE='11-AUG-13';







The statement is equivalent to the previous since the literal '11-AUG-13' is converted to the date value 11-AUG-2013.
This is due to the RR component being less than 50, so the current (twenty-first) century, 20, is prefixed to the year RR component to provide a century value.

SELECT * FROM EMPLOYE 
WHERE END_DATE='21-AUG-99';







The century component for the literal '21-AUG-99' becomes the previous (twentieth) century, 19, yields a date value of 21-AUG-1999 for the fourth statement, since the RR component, 99, is greater than 50.

Comparison Operators
You can use all the comparison operator in where clause <, >, <=, >=, <>, !=
Clause 1: where salary <= 3000;
Clause 2: where salary >= 5000;
Clause 3: where salary <> salary+500;
Clause 4: where salary != 4000+salary;

In addition to all the above operator you can also use IN, BETWEEN, LIKE, IS NULL,AND, OR, NOT operators.


HAVING Clause
The Oracle PL/SQL HAVING clause is used to filter or restrict the groups formed by the GROUP_BY clause. It follows the GROUP_BY clause in the SELECT statement. The HAVING clause can also precede the GROUP_BY clause, but this isn't logical and is not recommended. All grouping is performed (and group functions executed) prior to evaluating the HAVING clause.
The Oracle HAVING clause can be used with other functions such as SUM, COUNT, MIN, and MAX etc.

How a HAVING clause works

• The select clause specifies the columns.
• The from clause supplies a set of potential rows for the result.
• The where clause gives a filter for these potential rows.
• The group by clause divide the rows in a table into smaller groups.
• The having clause gives a filter for these group rows.

Example Syntax:

SELECT <column list>, <group by function>

FROM <table name>

WHERE <conditions>

GROUP_BY <column list>

HAVING <group by function condition>

Example of having clause;

Query selecting JOB_ID where sum of salaries greater than or equal to 4000.

SELECT JOB_ID, SUM(SALARY)

FROM EMPLOYE

GROUP BY JOB_ID

HAVING SUM(SALARY)>=4000;

Difference between WHERE and HAVING

1. The WHERE clause will filter or limit rows as they are selected from the table, but before grouping is done. The HAVING clause will filter rows after the grouping.
2. where is conditions on the select ... from
   having is conditions on the aggregate results from the group by ...
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
4. When WHERE and HAVING clause used together in one query, WHERE clause is applied first in individual row and rows which passed the WHERE condition is included for creating groups. Once groups are created, HAVING clause is used to filter group based on specified condition.
5. Syntax difference : WHERE is used before group by clause and HAVING is used after group by clause.
6. You can use WHERE clause with SELECT, UPDATE and DELETE whereas HAVING clause can only be used with SELECT query directly but indirectly you can use HAVING clause with update and delete query also.
   DELETE from USER
   where USER_ID IN (select USER_ID from USER group by USER_ID having count(*)<10);

HAVING vs WHERE performance

The theory says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. 

So WHERE is faster.

Only use HAVING where you cannot put the condition on a WHERE (like computed columns)

You can just see the execution plan for both and check for yourself by setting AUTOTRACE ON;

SQL> SET AUTOTRACE ON;

execute the queries.

Q. It is possibile to use a WHERE clause after a HAVING clause?

No, not in the same query.
s already discussed,where clause goes before the having and the group by. If you want to filter out records before the grouping the condition goes in the where clause, and if you want to filter out grouped records the condition goes in the having clause.

if you find this information useful, please comment.