Operators

An operator is a symbol specifying an action that is performed on one or more expressions. Operators are used with multiple statements in SQL. There are various kinds of operators in SQL. They are as follows:

  • Arithmetic operators
  • Logical operators
  • The assignment operator
  • Scope-resolution operators
  • Bitwise operators
  • Set operators
  • Comparison operators
  • The string-concatenation operator
  • Compound operators
  • Unary operators

Explaining each kind of operator is out of our scope here, so we will discuss a few commonly used operators in Impala as follows.

  • BETWEEN: The BETWEEN operator is used with the WHERE clause, and this operator compares the values between the lower and upper bound given with the WHERE clause. The comparison is successful if the expression is greater than or equal to the lower bound and less than or equal to the upper bound. If the bound values are switched, the lower bound is greater than the upper bound; the BETWEEN operator does not match any values as a result. The syntax to use the BETWEEN operator with the WHERE clause is as follows:
    expression BETWEEN lower_bound AND upper_bound
Example:
SELECT column_name FROM table_name WHERE value_in_column BETWEEN lower_bound AND upper_bound;

    Here are a few key things to remember when using the BETWEEN operator in Impala:

    The BETWEEN operator works with any kind of data type; however, it is not practical to use it with BOOLEAN

    Sometimes it is possible that the values provided are in the lower or upper bound, so you can apply the Cast() operator to those values to convert them into compatible types

    It is also important to note that the BETWEEN operator is typically used with numeric data types, so other functions can also be used to extract numeric values, if possible.

    While using BETWEEN with a string, it is advisable to use the upper(), lower(), substr(), or trim() function to operate on the string instead of having them operated with the BETWEEN operator, because variation in the string length could change the outcome of the comparison operator in BETWEEN.

  • DISTINCT: The SQL DISTINCT operator is used with the SELECT statement to retrieve only unique data entries, depending on the column list selected in the table. Here are the key features of the DISTINCT operator when used with Impala:
    • The DISTINCT operator returns the unique values from the column
    • NULL is also included as a value in the column and as part of the result
    • The DISTINCT operator can also return unique combinations of values from multiple columns within the same SELECT statement
    • The DISTINCT operator can be combined with other functions, such as COUNT, to aggregate total unique values found in the reference column
    • Impala does not support using DISTINCT in more than one aggregate function in the same query

    Here is the syntax and an example of using the DISTINCT operator in Impala:

    SELECT DISTINCT column_name FROM table_name;
SELECT DISTINCT column_name1, column_name2 FROM table_name;
SELECT COUNT(DISTINCT column_name) FROM table_name;
SELECT SUM(DISTINCT column_name) FROM table_name;
SELECT SUM(DISTINCT column_name) FROM table_name WHERE (column_name CONDITION value);
  • LIKE: Sometimes performing wildcard-based comparisons with STRING data is required, and for this requirement the LIKE operator is used. In Impala, the LIKE operator is used as a comparison operator to match the STRING type of data. For a single character match, LIKE uses _ (underscore) and for multiple characters, the % (percentage) sign is used. Using the % wildcard at the end of the string to get efficient results is suggested.

    The following is the syntax of using the LIKE operator in Impala:

    SELECT column_name FROM table_name WHERE column_name LIKE '%';
SELECT column_name from table_name WHERE column_name LIKE '_';
SELECT column_name from table_name WHERE column_name LIKE '_' OR column_name LIKE '%';

    Now, let's see a few examples of using the LIKE operator in Impala. The following statement will return all the state names, which are only two characters and start with the letter C:

    SELECT DISTINCT(state_names) from us_state_list WHERE state_name LIKE 'C_';

    The following statement will return all the names that start with Jo and can be two or more characters long. In this Impala statement, the result will include John, Joe, Jo, and Johnson.

    SELECT DISTINCT(names) from names_list WHERE names LIKE 'Jo%';
..................Content has been hidden....................

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