The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT.
We use the SQL WHERE clause when we require to fetch the data from single or multiple tables. If the condition is specified then it will return a specific value. We can also use the WHERE clause with the UPDATE, DELETE SQL statement
SELECT column FROM table_name WHERE conditions GROUP BY column ORDER BY column
PRODUCT | COMPANY | QTY | RATE | COST |
---|---|---|---|---|
Item1 | Com1 | 2 | 10 | 20 |
Item2 | Com2 | 3 | 25 | 75 |
Item3 | Com1 | 2 | 30 | 60 |
Item4 | Com3 | 5 | 10 | 50 |
Item5 | Com2 | 2 | 20 | 40 |
Item6 | Cpm1 | 3 | 25 | 75 |
Item7 | Com1 | 5 | 30 | 150 |
Item8 | Com1 | 3 | 10 | 30 |
Item9 | Com2 | 2 | 25 | 50 |
Item10 | Com3 | 4 | 30 | 120 |
SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY;
Com1 5 Com2 3 Com3 2
This clause works exactly as the where clause with the only difference that it operates on the output of the GROUP BY clause even the syntax is same.
The HAVING clause is an additional filter that is applied to the result set. Logically, the HAVING clause filters rows from the intermediate result set built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses are typically used with a GROUP BY clause
SELECT column1, column2 FROM table_name WHERE conditions GROUP BY column1, column2 HAVING conditions ORDER BY column1, column2;
SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY HAVING COUNT(*)>2;
The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
CUSTOMER_ID | NAME | ADDRESS |
---|---|---|
12 | Kathrin | US |
23 | David | Bangkok |
34 | Alina | Dubai |
45 | John | UK |
56 | Harry | US |
SELECT * FROM CUSTOMER ORDER BY NAME;
CUSTOMER_ID | NAME | ADDRESS |
---|---|---|
34 | Alina | Dubai |
23 | David | Bangkok |
56 | Harry | US |
45 | John | UK |
12 | Kathrin | US |
SELECT * FROM CUSTOMER ORDER BY NAME DESC;
CUSTOMER_ID | NAME | ADDRESS |
---|---|---|
12 | Kathrin | US |
45 | John | UK |
56 | Harry | US |
23 | David | Bangkok |
34 | Alina | Dubai |