SQL - HAVING, WHERE CLAUSE & ALIAS

HAVING clause:

·         The HAVING clause enables you to specify conditions that filter which group results appear in the final results.

·         The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax:

The following is the position of the HAVING clause in a query:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

Example:

Following is the example, which would display record for which similar age count would be more than or equal to 2:

SELECT ID, NAME, AGE, ADDRESS, SALARY

FROM CUSTOMERS

GROUP BY age

HAVING COUNT(age) >= 2;

WHERE CLAUSE:

·         Where clause is used to specify a condition when fetching the data.

·         If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records.

·         The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.

Syntax:

SELECT column1, column2, columnN

FROM table_name

WHERE [condition]

SELECT ID, NAME, SALARY

FROM CUSTOMERS

WHERE SALARY > 2000;

Alias Syntax:

·         You can rename a table or a column temporarily by giving another name known as alias.

·         The use of table aliases means to rename a table in a particular SQL statement.

·         The renaming is a temporary change and the actual table name does not change in the database.

Syntax:

SELECT column1, column2....

FROM table_name AS alias_name

WHERE [condition];

SELECT column_name AS alias_name

FROM table_name

WHERE [condition];

Example:

Consider the following two tables, CUSTOMERS table AND ORDERS table is as follows:

Now, following is the usage of table alias:

SELECT c.customerid, C.firstname, O.totalamount, O.orderdate

 FROM customer AS c, orders AS o

WHERE C.customerid=o.customerid

Following is the usage of column alias:


SELECT  customerID AS CUSTOMER_ID, firstNAME AS 

CUSTOMER_firstNAME

 FROM CUSTOMER


 WHERE city IS NOT NULL;