SQL Syntax

We use TPCH schema as an example.

Single Table Queries

Equal conditions

-- equal
select * from lineitem where l_shipdate = date '1998-12-01';
CREATE INDEX SQLLAB_IDX1533504424 ON LINEITEM(L_SHIPDATE);

select * from lineitem where l_shipdate = date '1998-12-01' and l_shipmode = '0';
CREATE INDEX SQLLAB_IDX1942674217 ON LINEITEM(L_SHIPDATE,L_SHIPMODE);

select * from lineitem where l_shipmode is null;
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_SHIPMODE);

select * from lineitem where l_shipmode in ('0');
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_SHIPMODE);

select * from lineitem where l_shipmode in ('0','1');
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_SHIPMODE);

select * from lineitem where l_shipmode <=> '0';
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_SHIPMODE);

select * from lineitem where l_shipdate = (select max(l_shipdate) from lineitem);
CREATE INDEX SQLLAB_IDX1533504424 ON LINEITEM(L_SHIPDATE);

Range Conditions

-- range
select * from lineitem where l_shipdate >= date '1998-12-01';
CREATE INDEX SQLLAB_IDX0156881833 ON LINEITEM(L_SHIPDATE);

select * from lineitem where l_shipdate between date '2010-12-01' and date '2020-12-01';
CREATE INDEX SQLLAB_IDX1241878058 ON LINEITEM(L_SHIPDATE)；

select * from customer where c_phone like "139%";
CREATE INDEX SQLLAB_IDX0326568991 ON CUSTOMER(C_PHONE);

Grouping and Ordering

-- grouping
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by l_shipdate;
CREATE INDEX SQLLAB_IDX1614428511 ON LINEITEM(L_SHIPDATE，L_QUANTITY); -- index only

-- ordering
select * from lineitem order by l_shipdate limit 10;
CREATE INDEX SQLLAB_IDX1424903467 ON LINEITEM(L_SHIPDATE);

Combinition of syntax

-- equal + range
select * from lineitem where l_shipdate = date '1998-12-01' and l_quantity >100;
CREATE INDEX SQLLAB_IDX2048143506 ON LINEITEM(L_SHIPDATE,L_QUANTITY);

-- ordering+grouping
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by l_shipdate order by l_shipdate;
CREATE INDEX SQLLAB_IDX1360881332 ON LINEITEM(L_SHIPDATE);

-- where+group
select l_shipdate, sum(l_quantity) as sum_qty from lineitem where l_receiptdate = '2020-01-01' group by l_shipdate;
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_RECEIPTDATE,L_SHIPDATE,l_quantity);

Multi-Tables

-- 
SELECT * FROM ORDERS, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY;
CREATE INDEX SQLLAB_IDX2127618499 ON ORDERS(O_ORDERKEY);
CREATE INDEX SQLLAB_IDX0339323878 ON LINEITEM(L_ORDERKEY);

SELECT * FROM ORDERS JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY;
CREATE INDEX SQLLAB_IDX1531629550 ON ORDERS(O_ORDERKEY);
CREATE INDEX SQLLAB_IDX1365836084 ON LINEITEM(L_ORDERKEY);

SELECT * FROM ORDERS LEFT JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY;
CREATE INDEX SQLLAB_IDX1336974557 ON LINEITEM(L_ORDERKEY);

SELECT * FROM ORDERS RIGHT JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY;
CREATE INDEX SQLLAB_IDX1002609246 ON ORDERS(O_ORDERKEY);

SELECT * FROM ORDERS LEFT JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY WHERE l_shipdate = date '1998-12-01';
CREATE INDEX SQLLAB_IDX0711368375 ON LINEITEM(L_ORDERKEY,L_SHIPDATE);

Subqueries

-- subquery1
select *
from
	supplier,(select l_suppkey, sum(l_extendedprice) as total_revenue from lineitem group by l_suppkey) revenue
where
	s_suppkey = revenue.l_suppkey;
CREATE INDEX SQLLAB_IDX0151075817 ON SUPPLIER(S_SUPPKEY);
CREATE INDEX SQLLAB_IDX1968327707 ON LINEITEM(L_SUPPKEY,L_EXTENDEDPRICE);

-- subquery2
select *
from
	supplier
where
	s_suppkey = (select l_suppkey from lineitem order by l_suppkey desc limit 1);
CREATE INDEX SQLLAB_IDX0664775210 ON SUPPLIER(S_SUPPKEY);

-- subquery3
with revenue as 
(select l_suppkey, sum(l_extendedprice) as total_revenue from lineitem group by l_suppkey)
select *
from
	supplier,
	revenue
where
	s_suppkey = l_suppkey;
CREATE INDEX SQLLAB_IDX0343576594 ON SUPPLIER(S_SUPPKEY);
CREATE INDEX SQLLAB_IDX1518532301 ON LINEITEM(L_SUPPKEY,L_EXTENDEDPRICE);

Query Rewrite

Transitive Closure

-- example 1
select
	o_custkey as cust_no,
	l_extendedprice * (1 - l_discount)
from
	orders,
	lineitem
where l_orderkey = o_orderkey
and l_orderkey = 'ORD1234';
CREATE INDEX SQLLAB_IDX0837835805 ON ORDERS(O_ORDERKEY,O_CUSTKEY);
CREATE INDEX SQLLAB_IDX1989932894 ON LINEITEM(L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);

-- example 2
select
	o_custkey as cust_no,
	l_extendedprice * (1 - l_discount)
from
	orders,
	lineitem
where l_orderkey = o_orderkey
and l_orderkey > 'ORD1234';
CREATE INDEX SQLLAB_IDX1000938250 ON ORDERS(O_ORDERKEY,O_CUSTKEY);
CREATE INDEX SQLLAB_IDX1358517686 ON LINEITEM(L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);

-- example 3
select
	c_custkey,
	c_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
from
	customer,
	orders,
	lineitem
where
	c_custkey = o_orderkey
	and l_orderkey = o_orderkey
	and l_orderkey = 'ORD1234'
group by
	c_custkey,
	c_name;
CREATE INDEX SQLLAB_IDX1161414921 ON ORDERS(O_ORDERKEY);
CREATE INDEX SQLLAB_IDX1584481406 ON CUSTOMER(C_CUSTKEY,C_NAME);
CREATE INDEX SQLLAB_IDX0637857516 ON LINEITEM(L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);

Ordinal

-- orderby ordinal 重写
select * from lineitem order by l_shipdate limit 10 
CREATE INDEX SQLLAB_IDX1424903467 ON LINEITEM(L_SHIPDATE);
sql
-- groupby ordinal 重写
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by 1
CREATE INDEX SQLLAB_IDX1614428511 ON LINEITEM(L_SHIPDATE,L_QUANTITY);

Update/Delete/Insert/Merge

delete from lineitem where l_shipdate = date '1998-12-01';
CREATE INDEX SQLLAB_IDX1533504424 ON LINEITEM(L_SHIPDATE);

update lineitem set l_shipmode='' where l_shipmode is null;
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_SHIPMODE);

insert into lineitem select * from lineitem where l_shipmode in ('0');
CREATE INDEX SQLLAB_IDX2000792795 ON LINEITEM(L_SHIPMODE);

replace into lineitem select * where l_shipdate = date '1998-12-01' and l_shipmode = '0';
CREATE INDEX SQLLAB_IDX1942674217 ON LINEITEM(L_SHIPDATE,L_SHIPMODE);