Apache Tajo - SQL Queries
This chapter explains about the following significant Queries.
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.
