Skip to main content

Statements for Queries

Statements for common queries​

UNION/UNION ALL​

select first_name from t_test1
union all
select first_name from t_test2;

DISTINCT​

select distinct first_name from t_test1;

LIKE​

select * from t_test where first_name like 'zhou%';

GROUP BY/ORDER BY​

select first_name,count(*) from t_test1 group by first_name order by 2;

HAVING​

select e.id, count(e.id), round(avg(e.score), 2)
from t_test1 e
group by e.id
having avg(e.score) > (select avg(score) from t_test1);

Statements used for aggregate queries​

select first_name,count(*) from t_test group by first_name;
select sum(score) from t_test;

Statements used for pagination queries​

select * from t_test1 limit 10;
select * from t_test1 limit 10,10;

Statements used for join queries​

INNER JOIN​

select t1.id,t1.first_name,t2.last_name from t_test1 t1,t_test2 t2 where t1.id = t2.id;

LEFT JOIN​

select t1.id,t1.first_name,t2.last_name from t_test1 t1 left join t_test2 t2 on t1.id = t2.id and t1.id=100;

RIGHT JOIN​

select t1.id,t1.first_name,t2.last_name from t_test1 t1 right join t_test2 t2 on t1.id = t2.id and t1.id=100;

Statements used for subqueries​

Statement for scalar subqueries​

select e.id,
e.first_name,
(select d.first_name from t_test2 d where d.id = e.id) as first_name
from t_test1 e;

Statement for derived subqueries​

select a.first_name, b.last_name
from t_test1 a, (select id,last_name from t_test2) b
where a.id = b.id;

IN/NOT IN​

select * from t_test1 where id in(select id from t_test2);

EXISTS/NOT EXISTS​

select * from t_test1 A where exists (select 1 from t_test2 B where B.id = A.id);