SQL Clauses - WHERE, AND, OR, AS, WITH and HAVING

 SQL provides the following clauses to write query

• WHERE 
• AND
• OR 
• AS
• WITH
• HAVING

Let's understand each SQL clause in detail:

Consider the Following Structure of table:

    create table Employee(

emp_id INT NOT NULL PRIMARY KEY,

emp_fname varchar(10),

        emp_lname varchar(10),

        dept varchar(10),

        city varchar(20),

        salary FLOAT

    );

    Consider the table consists of the following rows:

    

            emp_id   emp_fname   emp_lname  dept         city           salary   

1	Anup	Mittal	ME	Delhi	60000
2	John	Doe	CSE	Bangalore	50000
3	Namita	Thankur	CSE	Delhi	55000
4	Aman	Singh	ME	UK	65000
5	Pratik	Thapar	CSE	Delhi	62000

1. WHERE:

        This is an optional clause and used with SELECT, UPDATE or DELETE statement. It is basically used to filter out specific records from the table based on the condition.

       It uses the following condition operators:

•  = : equal
• <>: not equal
•  > : greater than
• >=: greater than or equal
•  < : less than 
• <=: less than or equal

Example: 

         SELECT * FROM Employee WHERE salary>=55000;

Output:

                     emp_id   emp_fname   emp_lname  dept         city           salary  

1	Anup	Mittal	ME	Delhi	60000
3	Namita	Thankur	CSE	Delhi	55000
4	Aman	Singh	ME	UK	65000
5	Pratik	Thapar	CSE	Delhi	62000

2. AND

        This clause is used when we want to apply multiple condition at the same time in the query.

Note: It is used with INSERT, DELETE, UPDATE and SELECT statement.

     Using it with select

           SELECT * FROM Employee WHERE salary>=55000 AND city="Delhi";

          Output

                     emp_id   emp_fname   emp_lname  dept         city           salary                

1	Anup	Mittal	ME	Delhi	60000
3	Namita	Thankur	CSE	Delhi	55000
5	Pratik	Thapar	CSE	Delhi	62000

Note: It is used in queries where we want to select all those rows which satisfies all of the conditions.

3. OR

       This clause is same as AND clause, the only difference is that the query will execute if any of the provided conditions is true.

      Example:

           SELECT * FROM Employee WHERE salary>=55000 OR city="Delhi";

       Output:

                     emp_id   emp_fname   emp_lname  dept         city           salary  

1	Anup	Mittal	ME	Delhi	60000
3	Namita	Thankur	CSE	Delhi	55000
4	Aman	Singh	ME	UK	65000
5	Pratik	Thapar	CSE	Delhi	62000

4. AS

      This clause is used to assign an alias name to a column or table. It allows us to assign a temporary name to a column or table without actually renaming it.

       Example:

          SELECT emp_id AS ID, emp_fname AS NAME FROM Employee;

          SELECT emp_id AS ID, concat(emp_fname," ", emp_lname ) AS NAME FROM Employee;

           Output:

                                    ID      NAME 

1	Anup Mittal
2	John Doe
3	Namita Thankur
4	Aman Singh
5	Pratik Thapar

5. WITH:

       This clause allows us to write a sub-query which can be used at multiple places.

      Example:

           WITH temporaryTable (averageSalary) AS (SELECT avg(salary) FROM Employee);

            SELECT * FROM Employee WHERE Employee.salary > temporaryTable.averageSalary;

Note: Not all databases support with clause.

6. HAVING:

       This clause is used to filter records in SQL queries. Having clause is used to define the condition in groups define by GROUP BY clause.

Note: It is implemented after the GROUP BY clause.

     Syntax:

           SELECT col1, col2, ..., colN FROM table_name GROUP BY col_name HAVING condition;

    Example: let say we want to calculate how much salary goes to a particular department.

         SELECT sum(salary), dept FROM Employee GROUP BY dept;

        Output:

                                        sum(salary) dept

125000	ME
167000	CSE

          

          --To select those dept which is provided more than 1,50,000 salary:

          SELECT sum(salary), dept FROM Employee GROUP BY dept HAVING sum(salary)>150000;

            Output:                          

                                        sum(salary) dept

167000

CSE

Continue Learning: HAVING VS WHERE clause