SQL SELECT - DISTINCT, COUNT, SUM, NULL, IN

             There are the following keywords, we can use with SELECT statement to improve the performance of our query. And these are:

• DISTINCT
• COUNT
• SUM
• NULL
• IN

Let's discuss about each keyword with example:

1. DISTINCT:

            This keyword is used with SELECT statement to only select distinct data. Since there is a probability of occurence of same data multiple times inside a table or database.

            Syntax:

                    SELECT DISTINCT col_name1, col_name2, .. FROM table_name;

            Example: Consider the following Employee table

                     emp_id   emp_fname   emp_lname  dept         city           salary  

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

        

           Let say, instead of selecting all entries of a city, you want to see all different cities:

                           SELECT DISTINCT city FROM Employee;

           Output:

                            city

                            UK

                            Delhi         

Note: DISTINCT and UNIQUE keywords are same.

2. COUNT:

        This keyword returns the number of rows in a table.

        Syntax:

                SELECT COUNT(col_name) FROM table_name;

        Example:

        Let's say, you want to count the total number of employees stored in the Employee table:

                SELECT COUNT(emp_id) FROM Employee;

         Output: 4

Note: If NULL value is present in as a column value, then it will not be counted by COUNT keyword. It will simply skip the tuple.

        Hence to count NULL values as well use * sign as:

                SELECT COUNT(*) FROM table_name;

        Let's say, you want to calculate no. of employees who are from Delhi city then:

                SELECT COUNT(emp_id) FROM Employee where city = "Delhi";

                using WHERE clause you can get the result.

3. SUM:

        This keyword is used to return the summed value of a column.

        Syntax:

                SELECT SUM(col_name) FROM Employee WHERE condition.

       Note: Where clause is optional here.

       Example: 

       Let say you want to calculate total salary of employees in Employee table:

                SELECT SUM(salary) FROM Employee;

                Output:  242000

        To calculate sum of salary of those employees who lives in UK:

                SELECT SUM(salary) FROM Employee WHERE city="UK";

                

                Output: 125000

4. NULL or NOT NULL:

        It is common thing that some of the column values are optional, hence while inserting the data inside the table, this field can contain NULL value.

        Hence to select those records who contains NULL as a value, we use NULL keyword.

        Example:

            Consider the following Employee table:

            

                     emp_id   emp_fname   emp_lname  dept         city           salary  

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

            Here you can clearly see the 1 and 4 row contain null values in city column. Hence to retrieve these rows we can write sql query as:

                            SELECT * FROM Employee WHERE city IS NULL

    

                     Output:

                            1   Anup     Mittal     ME    NULL    60000

                            2   Pratik    Thapar    CSE  NULL    62000

             Similarly, you can check for fields which does not contain NULL value using NOT NULL.

5. IN:

        IN operator in SQL is used to replace OR clause. It can be used with SELECT, DELETE, UPDATE and INSERT statement as OR clause.

        Syntax:

               SELECT col1, col2,... FROM table_name WHERE col_name in (val1, val2, ...);

        Example:

            Let say we want to select those employees who either lives in Delhi or UK.

                SELECT * FROM Employee WHERE city IN ("Delhi", "UK");

Continue Leaning: ORDER BY clause