Analytics with Hadoop - Big Data Made Easy: A Working Guide to the Complete Hadoop Toolset - page 273

The value of the column supplier is filtered so that it is “not like” UK Trade% and “not like” Corporate% . This
means that the only columns that are selected are those in which the supplier name does not start with the strings UK
Trade and Corporate .
The percent ( % ) character is a wild card that matches any data in the column; when placed at the end of the string,
it matches everything from that point to the end of the string. Strings are expressed in single quotes and the string is
case sensitive.
I could also use the WHERE clause as follows:
WHERE
supplier = 'ADETIQ LTD' OR
supplier = 'ADS GROUP LTD'
This clause then explicity filters the supplier column on the values that are equal to ADETIQ LTD or ADS GROUP LTD ,
and returns the two data rows, as shown:
2013 2 ADETIQ LTD 783.84
2013 2 ADS GROUP LTD 15549.0
The next clause filters the supplier column to equal ADETIQ LTD and not equal ( <> ) the value ADS GROUP LTD , as
follows:
WHERE
supplier = 'ADETIQ LTD' AND
supplier <> 'ADS GROUP LTD'
And so it returns a single row:
2013 2 ADETIQ LTD 783.84
The Subquery
The SELECT statements can also be used in subqueries. Subqueries are handy when you want to reduce or filter data
from a table before using it. They are coupled for Hive QL in both the FROM clause and the WHERE clause. That is, in the
FROM section of the parent SELECT statement, you simply enclose your subquery in parentheses.
For example, I could select all columns from the trade.rawtrans table, filtering the supplier column to values
containing the string INDIA . This then becomes a derived table with an alias of b, as follows:
SELECT
DateConv (b.paydate) as paydate,
b.supplier,
b.amount
FROM
(
SELECT a.* FROM trade.rawtrans a WHERE a.supplier LIKE '%INDIA%'
) b ;
And this returns the column data:
2013-02-01 THE INDIA SHOP (IMPORTS) LTD 1000.0
2013-02-08 LIVING MEDIA INDIA LTD 4109.3
2013-02-22 UK INDIA BUSINESS COUNCI 4125.0