Clauses

SQL statements require clauses to fulfill the statement condition or make it complete. For example, the SELECT statement will not be able to fulfill its action unless we provide what kind of SELECT action is actually needed. Most of these clauses are used with SELECT; however, some of them may find other uses as well. Now, lets learn the most common clauses in SQL, which are typically used in Impala. They are as follows:

  • FROM: The SELECT statement cannot be completed without the FROM clause. The FROM clause specifies one or more tables containing the data that the query retrieve from. The common syntax for the FROM clause along with a few examples is as follows:
    FROM [table_name,…]
      WHERE…. [Condition];

    Examples:

    SELECT name, age, class, city, state, country FROM studentslist;
SELECT name, age, class, city, state, country FROM studentslist WHERE age < 18;
  • WHERE: The next clause in our list is WHERE. The WHERE clause is used in Impala to extract only those records that fulfill the defined criteria in SQL statements. WHERE is very popular and is one of the most-used clauses along with SELECT and FROM. The syntax for the WHERE clause with an example is as follows:
    SELECT column_name,column_name FROM table_name 
             WHERE column_name operator value;

    Example:

    SELECT name, age, class, city, state, country FROM studentslist WHERE city = 'San Francisco';

    Let's study the condition operators in the following table, which are used with WHERE:

    Operator

    Description

    =

    Equal

    <>

    Not equal to

    >

    Greater than

    <

    Less than

    >=

    Greater than or equal to

    <=

    Less than or equal to

    BETWEEN

    Between an inclusive range

    LIKE

    Search for a pattern

    IN

    To specify multiple possible values for a column

  • WITH: Sometimes SQL statements can become very complex while dealing with multiple tables and associated conditions. To make the SQL statement easier to understand and process, the WITH clause is used before the SELECT statement to define aliases for the expression that are referenced multiple times within SELECT statements. Using the WITH clause, you can apply a single command to the existing SELECT statements without modifying these statements. The syntax of the WITH clause is shown in the following example:
    WITH [common_table_statements];

    Example:

    WITH myWithEx1 as (SELECT 1), myWithEx2 as (SELECT 2) INSERT into
SELECT * FROM myWithEx1 UNION ALL SELECT * from myWithEx2;

    In the previous example, we have defined two aliases, myWithEx1 and myWithEx2, which are referenced by the SELECT query as defined.

  • GROUP BY: The GROUP BY statement is used along with aggregate functions such as COUNT(), AVG(), SUM(), MIN(), or MAX(), to group the results set by one or more columns defined in the SQL statement.

    The syntax of the GROUP BY statement is as follows:

    SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

    In the following example, we are counting items on ID from table1 and then grouping them by ID from table2:

    SELECT table1.Name,COUNT(table2.ID) AS TotalOrders FROM table2
LEFT JOIN table1
ON table2.ID=table1.ID
GROUP BY Name;
  • ORDER BY: The ORDER BY clause is used with SQL statements to sort the result data by one or more columns. The sorting is done in ascending order by default, and to change sort order you can use DESC at the end of the statement. The syntax of the ORDER BY clause is shown in the following example:
    SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

    Example:

    SELECT * FROM citizens ORDER BY state;
SELECT * FROM items ORDER BY price DESC;
  • HAVING: The HAVING clause is also used with aggregate functions such as COUNT(), AVG(), SUM(), MIN(), or MAX(), when a filter operation is conducted on a SELECT query. This clause also works with GROUP BY in some cases. The syntax of a HAVING clause is as follows:
    SELECT column_name, aggregate_function(column_name) FROM table_name
  WHERE column_name operator value  GROUP BY column_name
  HAVING aggregate_function(column_name) operator value;
  • LIMIT: Sometimes, when you want to limit the results of a SELECT query, you can use the LIMIT clause to set the maximum number of rows in the result set. The syntax of the LIMIT clause is as follows:
    SELECT column_name(s) FROM table_name LIMIT number;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.12.108.86