SELECT Statement in SQL

Introduction

 
In this chapter, we will learn how to SELECT SQL works and different options used with the SELECT statement.  
 

SQL SELECT statement

 
SQL SELECT statement is used to select data from a database table or tables.
 
SQL SELECT statement is used to select records from an RDBMS( Relational Database Management System) data table. The SELECT statement can select all records, select a set of records based on a condition, filter records, sort records, group by records, and more.
 
Here are some of the use cases of the SQL SELECT statement.
  1. SQL Select *
  2. SQL Select Distinct
  3. SQL Select Where
  4. SQL Select Having
  5. SQL Select Order By
  6. SQL Select And, Or, Not
  7. SQL Select Insert Into
  8. Nested SQL Select
  9. SQL Select with Case
  10. SQL Select with If…Then

1. SQL SELECT *

 
SELECT * statement selects all rows from a database table.
    1. SELECT * FROM Table Name   
      SELECT all rows from a table, EmployeeDetail.
       
      The output looks like the following
       
       select
       
      NOTE
       
      SELECT * is not recommended unless you need all the columns data. Use SELECT Column1, Column2 fromTable_Name format for better performance.
       
      SELECT Column names
       
      The following SELECT statement uses column names to select data from a table.
       
      SQL Example
      1. Select EmpName, EmpAddress from EmployeeDetail      
      The output looks like the following
       
      SelectName 
       

      2. SQL SELECT DISTINCT
       

      The SELECT DISTINCT statement is used to return only DISTINCT(different) values.
       
      Inside a table, a column may often contain many duplicate values and sometimes you only want to list the different (distinct) values.
       
      Syntax
        1. SELECT DISTINCT column-name    
        2. FROM table-name  
          The output looks like the following
           
           DistinctSelect
           

          3. SELECT FROM WHERE 

          • SELECT WHERE statement is used to filter records.
          • SELECT WHERE statement is used to extract only those records that fulfill a specified condition. 
          • SELECT WHERE statement is not used in the SELECT statement, it is used in UPDATE, DELETE statement, etc.
          Example : = > < >= <= <>
           
          This is an example of equals (=) statement
           
          where 
           
          This is an example of  greater than (>) statement
           
           
          This is an example of less than (<) statement
           
          LessThan 
           
          This is an example of a greater than or equal (>=) statement
           
           
           
          This is an example of a less than or equal (<=) statement
           
          LessThanEqual 
           
          Less than greater than not equal (< >).
           
          Note: in some versions of SQL this operator may be written as !=
           
          This is an example of < > statement
           
          LessThanGreaterThan 
           

          BETWEEN

           
          The BETWEEN statement is used BETWEEN a certain range for the SQL
           
          The output looks like the following
           
          Between 
           

          LIKE

           
          The LIKE operator is used in a WHERE clause statement to search for a specified pattern in a column in the SQL.
           
          This is used in two wildcards, it is often used in conjunction with the LIKE operator:
          • % - The percent sign represents zero, one, or multiple characters
          • _ - The underscore represents a single character 
           The output looks like the following
           
          like 
           

          IN

           
          The IN operator is used to specify multiple values in a WHERE clause.
           
          The IN operator is a shorthand for multiple OR conditions in SQL.
           
          The output looks like the following
           
           In
            1. SELECT * FROM EmployeeDetail    
            2. SELECT GROUP BY    

            4. SELECT HAVING

             
            HAVING can be used only in the SELECT statement. HAVING is typically used with the GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.
             
            Syntax:
              1. SELECT    
              2.     select_list    
              3. FROM    
              4.     table_name    
              5. GROUP BY    
              6.     group_list    
              7. HAVING    
              8.     conditions;   
                The output looks like the following
                 
                 SelectCount 
                 

                5. SELECT ORDER BY

                 
                The SELECT ORDER BY statement is the result set of a query by the specified column list and, optionally, limits the rows returned to a specified range. The order in which rows are returned in a result set is not guaranteed unless an ORDER BY clause is specified.
                 
                Syntax :
                  1. SELECT column1, column2,   
                  2. FROM table_name    
                  3. ORDER BY column1, column2 ASC|DESC;   
                    The output looks like the following
                     
                    Select OrderBy 
                     

                    6. SQL SELECT AND, OR, NOT
                     

                    SELECT AND statement:

                    • SELECT AND statement require two conditions to be true in a WHERE statement.
                    • SELECT WHERE statement with NOT negates the specified condition
                    • SELECT AND statement: A WHERE statement with AND requires that two conditions are true 
                    Syntax
                      1. SELECT column1, column2    
                      2. FROM table_name    
                      3. WHERE condition1 AND condition2 AND condition3;   
                        The output looks like the following
                         
                        SelectAnd  
                         

                        SELECT OR statement

                         
                        SELECT OR statement requires that one of two conditions is true in a where statement in the SQL.
                         
                        Syntax
                          1. SELECT column1, column2,    
                          2. FROM table_name    
                          3. WHERE condition1 OR condition2 OR condition3;  
                            The output looks like the following
                             
                            SelectOr  
                             

                             SELECT NOT statement

                             
                            a WHERE statement with NOT negates the specified condition 
                             
                            The NOT operator displays a record if the condition(s) is NOT TRUE. 
                             
                            Syntax
                              1. SELECT column1, column2,    
                              2. FROM table_name    
                              3. WHERE NOT condition;  
                              The output looks like the following
                               
                               SelectNot
                               

                              7. SQL SELECT INSERT INTO
                               

                              The SQL SELECT INSERT INTO Select statement copies data from one table and insert data into another table.
                               
                              Syntax
                                1. INSERT INTO table2    
                                2. SELECT * FROM table1    
                                3. WHERE condition;  
                                  The output looks like the following
                                   
                                   SelectIsert 
                                   

                                  8. Nested SQL SELECT statement

                                   
                                  a subquery can be used anywhere where an expression is allowed in the Nested SQL SELECT statement
                                   
                                  Syntax
                                    1. SELECT column_name [, column_name ]      
                                    2. FROM   table1 [, table2 ]      
                                    3. WHERE  column_name OPERATOR      
                                    4.    (SELECT column_name [, column_name ]      
                                    5.    FROM table1 [, table2 ]      
                                    6.    [WHERE])   
                                    The output looks like the following
                                     
                                     NestedSelect
                                     

                                    9. SQL SELECT with CASE

                                     
                                    The SQL SELECT with the CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
                                     
                                    If there is no ELSE part and no conditions are true, it returns NULL.
                                     
                                    Syntax
                                      1. CASE    
                                      2.      WHEN condition1 THEN result1    
                                      3.      WHEN condition2 THEN result2    
                                      4.      WHEN conditionN THEN resultN    
                                      5.      ELSE result    
                                      6.  END;  
                                        The output looks like the following
                                         
                                        SelectCase 
                                         

                                        10. SQL SELECT with IF…THAN
                                         

                                        The SQL SELECT with IF and THAN statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
                                         
                                        If there is no ELSE part and no conditions are true, it returns NULL.
                                         
                                        Syntax 
                                        1. IF <Condition>      
                                        2.      {Statement | Block_of_statement}         
                                        3. ELSE         
                                        4.      {Statement | Block_of_statement}]      

                                        Why SQL * Is harmful?

                                         
                                        The SQL * is harmful as of the following points
                                         
                                        1. Unnecessary I/O
                                        2. Increased network traffic
                                        3. More application memory
                                        4. Depends on the column order
                                        5. Fragile views
                                        6. Conflict in a join query
                                        7. Risky while copying data.
                                         

                                        Summary

                                         
                                        In the next chapter, you will learn how to use SQL SELECT -HAVING BY.
                                        Author
                                        Naresh Beniwal
                                        Editor 7.1k 1.5m
                                        Next » SQL INSERT INTO Statement