Apache Tajo - SQL Queries

This chapter explains about the following significant Queries.

Predicates
Explain
Join

Let us proceed and perform the queries.

Predicates

Predicate is an expression which is used to evaluate true/false values and UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses and other constructs where a Boolean value is required.

IN predicate

Determines whether the value of expression to test matches any value in the subquery or the list. Subquery is an ordinary SELECT statement that has a result set of one column and one or more rows. This column or all expressions in the list must have the same data type as the expression to test.

Syntax

IN::= 
<expression to test> [NOT] IN (<subquery>) 
| (<expression1>,...)

Query

select id,name,address from mytable where id in(2,3,4);

Result

The above query will generate the following result.

id,  name,   address 
------------------------------- 
2,  Amit,  12 old street 
3,  Bob,   10 cross street 
4,  David, 15 express avenue

The query returns records from mytable for the students id 2,3 and 4.

Query

select id,name,address from mytable where id not in(2,3,4);

Result

The above query will generate the following result.

id,  name,  address 
------------------------------- 
1,  Adam,   23 new street 
5,  Esha,   20 garden street 
6,  Ganga,  25 north street 
7,  Jack,   2 park street 
8,  Leena,  24 south street 
9,  Mary,   5 west street 
10, Peter,  16 park avenue

The above query returns records from mytable where students is not in 2,3 and 4.

Like Predicate

The LIKE predicate compares the string specified in the first expression for calculating the string value, which is refered to as a value to test, with the pattern that is defined in the second expression for calculating the string value.

The pattern may contain any combination of wildcards such as −

Underline symbol (_), which can be used instead of any single character in the value to test.
Percent sign (%), which replaces any string of zero or more characters in the value to test.

Syntax

LIKE::= 
<expression for calculating the string value> 
[NOT] LIKE 
<expression for calculating the string value> 
[ESCAPE <symbol>]

Query

select * from mytable where name like ‘A%';

Result

The above query will generate the following result.

id,  name,  address,     age,  mark 
------------------------------- 
1,  Adam,  23 new street,  12,  90 
2,  Amit,  12 old street,  13,  95

The query returns records from mytable of those students whose names are starting with ‘A’.

Query

select * from mytable where name like ‘_a%';

Result

The above query will generate the following result.

id,  name,  address,    age,  mark 
——————————————————————————————————————- 
4,  David,  15 express avenue,  12,  85 
6,  Ganga,  25 north street,    12,  55 
7,  Jack,  2 park street,       12,  60 
9,  Mary,  5 west street,       12,  75

The query returns records from mytable of those students whose names are starting with ‘a’ as the second char.

Using NULL Value in Search Conditions

Let us now understand how to use NULL Value in the search conditions.

Syntax

Predicate  
IS [NOT] NULL

Query

select name from mytable where name is not null;

Result

The above query will generate the following result.

name 
------------------------------- 
Adam 
Amit 
Bob 
David 
Esha 
Ganga 
Jack 
Leena 
Mary 
Peter  
(10 rows, 0.076 sec, 163 B selected)

Here, the result is true so it returns all the names from table.

Query

Let us now check the query with NULL condition.

default> select name from mytable where name is null;

Result

The above query will generate the following result.

name 
------------------------------- 
(0 rows, 0.068 sec, 0 B selected)

Explain

Explain is used to obtain a query execution plan. It shows a logical and global plan execution of a statement.

Logical Plan Query

explain select * from mytable;  
explain 
-------------------------------  
   => target list: default.mytable.id (INT4), default.mytable.name (TEXT), 
      default.mytable.address (TEXT), default.mytable.age (INT4), default.mytable.mark (INT4) 
   
   => out schema: {
   (5) default.mytable.id (INT4), default.mytable.name (TEXT), default.mytable.address (TEXT), 
      default.mytable.age (INT4), default.mytable.mark (INT4)
   } 
   
   => in schema: {
	(5) default.mytable.id (INT4), default.mytable.name (TEXT), default.mytable.address (TEXT), 
      default.mytable.age (INT4), default.mytable.mark (INT4)
   }

Result

The above query will generate the following result.

The query result shows a logical plan format for the given table. The Logical plan returns the following three results −

Target list
Out schema
In schema

Global Plan Query

explain global select * from mytable;  
explain 
------------------------------- 
------------------------------------------------------------------------------- 
Execution Block Graph (TERMINAL - eb_0000000000000_0000_000002) 
------------------------------------------------------------------------------- 
|-eb_0000000000000_0000_000002 
   |-eb_0000000000000_0000_000001 
------------------------------------------------------------------------------- 
Order of Execution 
------------------------------------------------------------------------------- 
1: eb_0000000000000_0000_000001 
2: eb_0000000000000_0000_000002 
-------------------------------------------------------------------------------  
======================================================= 
Block Id: eb_0000000000000_0000_000001 [ROOT] 
=======================================================  
SCAN(0) on default.mytable 
   
   => target list: default.mytable.id (INT4), default.mytable.name (TEXT), 
      default.mytable.address (TEXT), default.mytable.age (INT4), default.mytable.mark (INT4) 
   
   => out schema: {
	(5) default.mytable.id (INT4), default.mytable.name (TEXT),default.mytable.address (TEXT), 
      default.mytable.age (INT4), default.mytable.mark (INT4)
   } 
   
   => in schema: {
	(5) default.mytable.id (INT4), default.mytable.name (TEXT), default.mytable.address (TEXT), 
      default.mytable.age (INT4), default.mytable.mark (INT4)
   }  
======================================================= 
Block Id: eb_0000000000000_0000_000002 [TERMINAL] 
======================================================= 
(24 rows, 0.065 sec, 0 B selected)

Result

The above query will generate the following result.

Here, Global plan shows execution block ID, order of execution and its information.

Joins

SQL joins are used to combine rows from two or more tables. The following are the different types of SQL Joins −

Inner join
{ LEFT | RIGHT | FULL } OUTER JOIN
Cross join
Self join
Natural join

Consider the following two tables to perform joins operations.

Table1 − Customers

Id	Name	Address	Age
1	Customer 1	23 Old Street	21
2	Customer 2	12 New Street	23
3	Customer 3	10 Express Avenue	22
4	Customer 4	15 Express Avenue	22
5	Customer 5	20 Garden Street	33
6	Customer 6	21 North Street	25

Table2 − customer_order

Id	Order Id	Emp Id
1	1	101
2	2	102
3	3	103
4	4	104
5	5	105

Let us now proceed and perform the SQL joins operations on the above two tables.

Inner Join

The Inner join selects all rows from both the tables when there is a match between the columns in both tables.

Syntax

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Query

default> select c.age,c1.empid from customers c inner join customer_order c1 on c.id = c1.id;

Result

The above query will generate the following result.

age,  empid 
------------------------------- 
21,  101 
23,  102 
22,  103 
22,  104 
33,  105

The query matches five rows from both the tables. Hence, it returns the matched rows age from the first table.

Left Outer Join

A left outer join retains all of the rows of the “left” table, regardless of whether there is a row that matches on the “right” table or not.

Query

select c.name,c1.empid from customers c left outer join customer_order c1 on c.id = c1.id;

Result

The above query will generate the following result.

name,       empid 
------------------------------- 
customer1,  101 
customer2,  102 
customer3,  103 
customer4,  104 
customer5,  105 
customer6,

Here, the left outer join returns name column rows from the customers(left) table and empid column matched rows from the customer_order(right) table.

Right Outer Join

A right outer join retains all of the rows of the “right” table, regardless of whether there is a row that matches on the “left” table.

Query

select c.name,c1.empid from customers c right outer join customer_order c1 on c.id = c1.id;

Result

The above query will generate the following result.

name,      empid 
------------------------------- 
customer1,  101 
customer2,  102 
customer3,  103 
customer4,  104 
customer5,  105

Here, the Right Outer Join returns the empid rows from the customer_order(right) table and the name column matched rows from customers table.

Full Outer Join

The Full Outer Join retains all rows from both the left and the right table.

Query

select * from customers c full outer join customer_order c1 on c.id = c1.id;

Result

The above query will generate the following result.

The query returns all the matching and non-matching rows from both the customers and the customer_order tables.

Cross Join

This returns the Cartesian product of the sets of records from the two or more joined tables.

Syntax

SELECT *  FROM table1  CROSS JOIN table2;

Query

select orderid,name,address from customers,customer_order;

Result

The above query will generate the following result.

The above query returns the Cartesian product of the table.

Natural Join

A Natural Join does not use any comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between the two relations.

Syntax

SELECT * FROM table1 NATURAL JOIN table2;

Query

select * from customers natural join customer_order;

Result

The above query will generate the following result.

Here, there is one common column id that exists between two tables. Using that common column, the Natural Join joins both the tables.

Self Join

The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax

SELECT a.column_name, b.column_name...  
FROM table1 a, table1 b  
WHERE a.common_filed = b.common_field

Query

default> select c.id,c1.name from customers c, customers c1 where c.id = c1.id;

Result

The above query will generate the following result.

id,   name 
------------------------------- 
1,   customer1 
2,   customer2 
3,   customer3 
4,   customer4 
5,   customer5 
6,   customer6

The query joins a customer table to itself.