Oracle 11g: The difference between WHERE and HAVING

SQL> select * from sys.SHIP_CABINS ;SHIP_CABIN_ID    SHIP_ID ROOM_ ROOM_STYLE ROOM_TYPE            WINDOW         GUESTS      SQ_FT BALCONY_SQ_FT------------- ---------- ----- ---------- -------------------- ---------- ---------- ---------- -------------            1            102   Suite      Standard             Ocean               4        533            2            103   Stateroom  Standard             Ocean               2        160            3            104   Suite      Standard             None                4        533            4            105   Stateroom  Standard             Ocean               3        205            5            106   Suite      Standard             None                6        586            6            107   Suite      Royal                Ocean               5       1524            7            108   Stateroom  Large                None                2        211            8            109   Stateroom  Standard             None                2        180            9            110   Stateroom  Large                None                2        225           10            702   Suite      Presidential         None                5       1142           11            703   Suite      Royal                Ocean               5       1745           12            704   Suite      Skyloft              Ocean               8        722

 Group by without WHERE and HAVING

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type ;ROOM_STYLE ROOM_TYPE             GUEST SUM---------- -------------------- ----------Stateroom  Standard                      7Suite      Standard                     14Stateroom  Large                         4Suite      Skyloft                       8Suite      Royal                        10Suite      Presidential                  5

HAVING

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type having room_type<>'Royal' ;ROOM_STYLE ROOM_TYPE             GUEST SUM---------- -------------------- ----------Stateroom  Standard                      7Suite      Standard                     14Stateroom  Large                         4Suite      Skyloft                       8Suite      Presidential                  5

WHERE

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins where room_type<>'Royal' group by room_style, room_type ;ROOM_STYLE ROOM_TYPE             GUEST SUM---------- -------------------- ----------Stateroom  Standard                      7Suite      Standard                     14Stateroom  Large                         4Suite      Skyloft                       8Suite      Presidential                  5

Summary:

There is no difference between them when there is no aggregate function in HAVING clause.

However, we do this in HAVING.

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type having sum(GUESTS)>8 ;ROOM_STYLE ROOM_TYPE             GUEST SUM---------- -------------------- ----------Suite      Standard                     14Suite      Royal                        10

 

 There is no way to filter out sum(GUESTS)>8 entries using WHERE, because it doesn't allow to use Aggregate function in WHERE clause.