forked from phoenix-hbase/phoenix-hbase.github.com
-
Notifications
You must be signed in to change notification settings - Fork 0
/
index.html
812 lines (685 loc) · 67 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
SQL Grammar
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
<!-- [search] { -->
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="highlightTab();">
<div class="pageTitle">Phoenix Language Reference</div>
<div>
<p>Phoenix is a SQL layer over HBase delivered as an embedded JDBC driver.
The top level commands, SQL syntax, built-in functions, and supported data types are documented in the separate tabs below.
</p>
<br/>
</div>
<div class="menu">
<a id="grammarTab" class="inactiveTab" href="index.html">SQL Grammar</a>
<a id="functionsTab" class="inactiveTab" href="functions.html">Functions</a>
<a id="dataTypesTab" class="inactiveTab" href="datatypes.html">Data Types</a>
</div>
<div id="content" class="content">
<!-- } -->
<h3 id="grammar">Commands</h3>
<!-- syntax-start
<p class="notranslate">
<a href="#select">SELECT</a><br />
<a href="#upsert_values">UPSERT VALUES</a><br />
<a href="#upsert_select">UPSERT SELECT</a><br />
<a href="#delete">DELETE</a><br />
<a href="#create">CREATE</a><br />
<a href="#drop">DROP</a><br />
<a href="#alter_table">ALTER TABLE</a><br />
<a href="#explain">EXPLAIN</a><br />
</p>
syntax-end -->
<!-- railroad-start -->
<table class="notranslate index">
<tr>
<td class="index">
<a href="#select" >SELECT</a><br />
<a href="#upsert_values" >UPSERT VALUES</a><br />
<a href="#upsert_select" >UPSERT SELECT</a><br />
</td><td class="index">
<a href="#delete" >DELETE</a><br />
<a href="#create" >CREATE</a><br />
<a href="#drop" >DROP</a><br />
</td><td class="index">
<a href="#alter_table" >ALTER TABLE</a><br />
<a href="#explain" >EXPLAIN</a><br />
</td>
</tr>
</table>
<!-- railroad-end -->
<h3>Other Grammar</h3>
<!-- syntax-start
<p class="notranslate">
<a href="#column_family" >Column Family</a><br />
<a href="#options" >Options</a><br />
<a href="#pk_column" >PK Column</a><br />
<a href="#select_expression" >Select Expression</a><br />
<a href="#split_point" >Split Point</a><br />
<a href="#table_expression" >Table Expression</a><br />
<a href="#order" >Order</a><br />
<a href="#expression" >Expression</a><br />
<a href="#and_condition" >And Condition</a><br />
<a href="#condition" >Condition</a><br />
<a href="#compare" >Compare</a><br />
<a href="#operand" >Operand</a><br />
<a href="#summand" >Summand</a><br />
<a href="#factor" >Factor</a><br />
<a href="#term" >Term</a><br />
<a href="#bind_parameter" >Bind Parameter</a><br />
<a href="#value" >Value</a><br />
<a href="#case" >Case</a><br />
<a href="#case_when" >Case When</a><br />
<a href="#name" >Name</a><br />
<a href="#quoted_name" >Quoted Name</a><br />
<a href="#alias" >Alias</a><br />
<a href="#null" >Null</a><br />
<a href="#data_type" >Data Type</a><br />
<a href="#string" >String</a><br />
<a href="#boolean" >Boolean</a><br />
<a href="#numeric" >Numeric</a><br />
<a href="#int" >Int</a><br />
<a href="#long" >Long</a><br />
<a href="#decimal" >Decimal</a><br />
<a href="#number" >Number</a><br />
<a href="#comments" >Comments</a><br />
</p>
syntax-end -->
<!-- railroad-start -->
<table class="notranslate index">
<tr>
<td class="index">
<a href="#column_family" >Column Family</a><br />
<a href="#options" >Options</a><br />
<a href="#pk_column" >PK Column</a><br />
<a href="#select_expression" >Select Expression</a><br />
<a href="#split_point" >Split Point</a><br />
<a href="#table_expression" >Table Expression</a><br />
<a href="#order" >Order</a><br />
<a href="#expression" >Expression</a><br />
<a href="#and_condition" >And Condition</a><br />
<a href="#condition" >Condition</a><br />
<a href="#compare" >Compare</a><br />
</td><td class="index">
<a href="#operand" >Operand</a><br />
<a href="#summand" >Summand</a><br />
<a href="#factor" >Factor</a><br />
<a href="#term" >Term</a><br />
<a href="#bind_parameter" >Bind Parameter</a><br />
<a href="#value" >Value</a><br />
<a href="#case" >Case</a><br />
<a href="#case_when" >Case When</a><br />
<a href="#name" >Name</a><br />
<a href="#quoted_name" >Quoted Name</a><br />
<a href="#alias" >Alias</a><br />
</td><td class="index">
<a href="#null" >Null</a><br />
<a href="#data_type" >Data Type</a><br />
<a href="#string" >String</a><br />
<a href="#boolean" >Boolean</a><br />
<a href="#numeric" >Numeric</a><br />
<a href="#int" >Int</a><br />
<a href="#long" >Long</a><br />
<a href="#decimal" >Decimal</a><br />
<a href="#number" >Number</a><br />
<a href="#comments" >Comments</a><br />
</td>
</tr>
</table>
<!-- railroad-end -->
<h3 id="select" class="notranslate">SELECT</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
SELECT <a href="index.html#select_expression">selectExpression</a> [,...] FROM <a href="index.html#table_expression">tableExpression</a> [ WHERE <a href="index.html#expression">expression</a> ]
[ GROUP BY <a href="index.html#expression">expression</a> [,...] ] [ HAVING <a href="index.html#expression">expression</a> ]
[ ORDER BY <a href="index.html#order">order</a> [,...] ]
[ LIMIT {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} ]
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SELECT <a href="index.html#select_expression">selectExpression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">FROM <a href="index.html#table_expression">tableExpression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">WHERE <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">GROUP BY <a href="index.html#expression">expression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">HAVING <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ORDER BY <a href="index.html#order">order</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">LIMIT</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
SELECT <a href="index.html#select_expression">selectExpression</a> [,...] FROM <a href="index.html#table_expression">tableExpression</a> [ WHERE <a href="index.html#expression">expression</a> ]
[ GROUP BY <a href="index.html#expression">expression</a> [,...] ] [ HAVING <a href="index.html#expression">expression</a> ]
[ ORDER BY <a href="index.html#order">order</a> [,...] ]
[ LIMIT {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} ]
</pre>
syntax-end -->
<p>Selects data from a table. <code>GROUP BY</code> groups the the result by the given expression(s). <code>HAVING</code> filter rows after grouping. <code>ORDER BY</code> sorts the result by the given column(s) or expression(s) and is only allowed for aggregate queries or queries with a <code>LIMIT</code> clause. <code>LIMIT</code> limits the number of rows returned by the query (no limit if null or smaller than zero). Only single tables are currently supported - joins are currently not supported.</p>
<p>Example:</p>
<p class="notranslate">
SELECT * FROM TEST;<br />SELECT ID, COUNT(1) FROM TEST GROUP BY ID;<br />SELECT NAME, SUM(VAL) FROM TEST GROUP BY NAME HAVING COUNT(1) > 2;<br />SELECT 'ID' COL, MAX(ID) AS MAX FROM TEST;<br />SELECT * FROM TEST LIMIT 1000;</p>
<h3 id="upsert_values" class="notranslate">UPSERT VALUES</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
UPSERT INTO <a href="index.html#name">tableName</a> [( <a href="index.html#name">columnName</a> [,...] )] VALUES ( <a href="index.html#term">constantTerm</a> [,...] )
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">UPSERT INTO <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#name">columnName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">VALUES ( <a href="index.html#term">constantTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
UPSERT INTO <a href="index.html#name">tableName</a> [( <a href="index.html#name">columnName</a> [,...] )] VALUES ( <a href="index.html#term">constantTerm</a> [,...] )
</pre>
syntax-end -->
<p>Inserts if not present and updates otherwise the value in the table. The list of columns is optional and if not present, the values will map to the column in the order they are declared in the schema. The values must evaluate to constants.</p>
<p>Example:</p>
<p class="notranslate">
UPSERT INTO TEST VALUES('foo','bar',3);<br />UPSERT INTO TEST(NAME,ID) VALUES('foo',123);</p>
<h3 id="upsert_select" class="notranslate">UPSERT SELECT</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
UPSERT INTO <a href="index.html#name">tableName</a> [( <a href="index.html#name">columnName</a> [,...] )] <a href="index.html#select">select</a>
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">UPSERT INTO <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#name">columnName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#select">select</a></code></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
UPSERT INTO <a href="index.html#name">tableName</a> [( <a href="index.html#name">columnName</a> [,...] )] <a href="index.html#select">select</a>
</pre>
syntax-end -->
<p>Inserts if not present and updates otherwise rows in the table based on the results of running another query. The values are set based on their matching position between the source and target tables. The list of columns is optional and if not present will map to the column in the order they are declared in the schema. If auto commit is on, and both a) the target table matches the source table, and b) the select performs no aggregation, then the population of the target table will be done completely on the server-side (with constraint violations logged, but otherwise ignored). Otherwise, data is buffered on the client and, if auto commit is on, committed in row batches as specified by the UpsertBatchSize connection property (or the phoenix.mutate.upsertBatchSize <code>HBase</code> config property which defaults to 10000 rows)</p>
<p>Example:</p>
<p class="notranslate">
UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM test.sourceTable WHERE col5 < 100<br />UPSERT INTO foo SELECT * FROM bar;</p>
<h3 id="delete" class="notranslate">DELETE</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
DELETE FROM <a href="index.html#name">tableName</a> [ WHERE <a href="index.html#expression">expression</a> ]
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DELETE FROM <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">WHERE <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
DELETE FROM <a href="index.html#name">tableName</a> [ WHERE <a href="index.html#expression">expression</a> ]
</pre>
syntax-end -->
<p>Deletes the rows selected by the where clause. If auto commit is on, the deletion is performed completely server-side.</p>
<p>Example:</p>
<p class="notranslate">
DELETE FROM TEST;<br />DELETE FROM TEST WHERE ID=123;<br />DELETE FROM TEST WHERE NAME LIKE 'foo%';</p>
<h3 id="create" class="notranslate">CREATE</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
CREATE { TABLE | VIEW } [IF NOT EXISTS] <a href="index.html#name">tableName</a>
( <a href="index.html#pk_column">pkColumn</a> [...] ) [<a href="index.html#options">tableOptions</a>] [<a href="index.html#column_family">columnFamily</a> [...]] [ SPLIT ON ( <a href="index.html#split_point">splitPoint</a> [,...] ) ]
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">TABLE</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">VIEW</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">tableName</a></code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#pk_column">pkColumn</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#options">tableOptions</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#column_family">columnFamily</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">SPLIT ON ( <a href="index.html#split_point">splitPoint</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
CREATE { TABLE | VIEW } [IF NOT EXISTS] <a href="index.html#name">tableName</a>
( <a href="index.html#pk_column">pkColumn</a> [...] ) [<a href="index.html#options">tableOptions</a>] [<a href="index.html#column_family">columnFamily</a> [...]] [ SPLIT ON ( <a href="index.html#split_point">splitPoint</a> [,...] ) ]
</pre>
syntax-end -->
<p>Creates a new table or view. For the creation of a table, the <code>HBase</code> table and any column families defined are created if they don't already exist (using uppercase names unless they are double quoted in which case they are case sensitive). Column families outside of the ones listed are not affected. At create time, an empty key value is added to the first column family of any existing rows. Upserts will also add this empty key value. This is done to improve query performance by having a key value column we can guarantee always being there (minimizing the amount of data that must be projected). Alternately, if a view is created, the <code>HBase</code> table and column families must already exist. No empty key value is added to existing rows and no data mutations are allowed - the view is read-only. Query performance for a view will not be as good as performance for a table. For a table only, <code>HBase</code> table and column configuration options may be passed through as key/value pairs to setup the <code>HBase</code> table as needed.</p>
<p>Example:</p>
<p class="notranslate">
CREATE TABLE my_table ( id BIGINT not null, date DATE not null)<br />CREATE TABLE my_schema.my_table ( pkCol1 char(10) not null) my_family ( kvCol1 varchar )<br />CREATE TABLE my_schema.my_table ( pkCol1 char(10) not null, pkCol2 integer ) my_family ( kvCol1 varchar )<br />CREATE TABLE IF NOT EXISTS my_table ( id char(10) not null) MAX_FILESIZE=2000000 cf1 ( value integer ) DATA_BLOCK_ENCODING='NONE',VERSIONS=? split on (?, ?, ?)</p>
<h3 id="drop" class="notranslate">DROP</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
DROP {TABLE | VIEW} [IF EXISTS] <a href="index.html#name">tableName</a>
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">TABLE</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">VIEW</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">tableName</a></code></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
DROP {TABLE | VIEW} [IF EXISTS] <a href="index.html#name">tableName</a>
</pre>
syntax-end -->
<p>Drops a table or view. When dropping a table, the data in the table is deleted. For a view, on the other hand, the data is not affected. Note that the schema is versioned, such that snapshot queries connecting at an earlier time stamp may still query against the dropped table.</p>
<p>Example:</p>
<p class="notranslate">
DROP TABLE my_schema.my_table<br />DROP VIEW my_view</p>
<h3 id="alter_table" class="notranslate">ALTER TABLE</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
ALTER TABLE <a href="index.html#name">tableName</a> { { ADD [IF NOT EXISTS] { <a href="index.html#pk_column">pkColumn</a> | <a href="index.html#column_family">columnFamily</a> } } | { DROP COLUMN [IF EXISTS] [ <a href="index.html#name">columnFamilyName</a>. ] <a href="index.html#name">columnName</a> } }
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ALTER TABLE <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ADD</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#pk_column">pkColumn</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#column_family">columnFamily</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP COLUMN</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">columnFamilyName</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">columnName</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
ALTER TABLE <a href="index.html#name">tableName</a> { { ADD [IF NOT EXISTS] { <a href="index.html#pk_column">pkColumn</a> | <a href="index.html#column_family">columnFamily</a> } } | { DROP COLUMN [IF EXISTS] [ <a href="index.html#name">columnFamilyName</a>. ] <a href="index.html#name">columnName</a> } }
</pre>
syntax-end -->
<p>Adds or removes a column from an existing table or view. When a column is dropped from a table, the data in that column is deleted as well. <code>PK</code> columns may not be dropped, and only nullable <code>PK</code> columns may be added. For a view, the data is not affected when a column is dropped. Note that creating or dropping columns only affects subsequent queries and data modifications. Snapshot queries that are connected at an earlier timestamp will still use the prior schema that was in place when the data was written.</p>
<p>Example:</p>
<p class="notranslate">
ALTER TABLE my_schema.my_table ADD cf2 (dept_id char(10), dept_name varchar) VERSIONS=10<br />ALTER TABLE my_table ADD parent_id char(15)<br />ALTER TABLE my_table DROP COLUMN cf2.dept_id<br />ALTER TABLE my_table DROP COLUMN dept_name<br />ALTER TABLE my_table DROP COLUMN parent_id</p>
<h3 id="explain" class="notranslate">EXPLAIN</h3>
<!-- railroad-start -->
<pre name="bnf" style="display: none">
EXPLAIN {<a href="index.html#select">select</a>|<a href="index.html#upsert_select">upsertSelect</a>|<a href="index.html#delete">delete</a>}
</pre>
<div name="railroad">
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">EXPLAIN</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#select">select</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#upsert_select">upsertSelect</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#delete">delete</a></code></td><td class="le"></td></tr></table></td></tr></table>
</div>
<!-- railroad-end -->
<!-- syntax-start
<pre>
EXPLAIN {<a href="index.html#select">select</a>|<a href="index.html#upsert_select">upsertSelect</a>|<a href="index.html#delete">delete</a>}
</pre>
syntax-end -->
<p>Computes the logical steps necessary to execute the given command. Each step is represented as a string in a single column result set row.</p>
<p>Example:</p>
<p class="notranslate">
EXPLAIN SELECT NAME, COUNT(*) FROM TEST GROUP BY NAME HAVING COUNT(*) > 2;<br />EXPLAIN SELECT entity_id FROM CORE.CUSTOM_ENTITY_DATA WHERE organization_id='00D300000000XHP' AND SUBSTR(entity_id,1,3) = '002' AND created_date < CURRENT_DATE()-1;</p>
<h3 id="column_family" class="notranslate">Column Family</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">columnFamilyName</a> ( <a href="index.html#name">columnName</a> <a href="index.html#data_type">dataType</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#options">columnFamilyOptions</a></code></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#name">columnFamilyName</a> ( { <a href="index.html#name">columnName</a> <a href="index.html#data_type">dataType</a> } [,...] ) [<a href="index.html#options">columnFamilyOptions</a>]
</pre>
syntax-end -->
<p>Define a new column family and its columns. The column family is created in <code>HBase</code> if it doesn't already exist (using uppercase names by default unless double quoted).</p>
<p>Example:</p>
<p class="notranslate">my_family ( col1 varchar(1000) )<br />cf (col1 integer, col2 char(20) )</p>
<h3 id="options" class="notranslate">Options</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">name</a> =</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#value">value</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
{<a href="index.html#name">name</a>= {<a href="index.html#value">value</a> | <a href="index.html#bind_parameter">bindParameter</a>}} [,...]
</pre>
syntax-end -->
<p>Sets an option on an <code>HBase</code> table or column by modifying the <code>HBase</code> table.</p>
<p>Example:</p>
<p class="notranslate">DATA_BLOCK_ENCODING='NONE',VERSIONS=10<br />MAX_FILESIZE=2000000000,MEMSTORE_FLUSHSIZE=80000000</p>
<h3 id="pk_column" class="notranslate">PK Column</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">columnName</a> <a href="index.html#data_type">dataType</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">NOT</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">NULL</code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#name">columnName</a> <a href="index.html#data_type">dataType</a> [[NOT] NULL]
</pre>
syntax-end -->
<p>Define a new primary key column. The column name is case insensitive by default and case sensitive if double quoted.</p>
<p>Example:</p>
<p class="notranslate">id char(15) not null<br />key bigint null<br />col1 integer</p>
<h3 id="select_expression" class="notranslate">Select Expression</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#term">term</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">AS</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#alias">columnAlias</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#term">term</a> [ [ AS ] <a href="index.html#alias">columnAlias</a> ]
</pre>
syntax-end -->
<p>An expression in a <code>SELECT</code> statement.</p>
<p>Example:</p>
<p class="notranslate">ID AS VALUE</p>
<h3 id="split_point" class="notranslate">Split Point</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#value">value</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#value">value</a> | <a href="index.html#bind_parameter">bindParameter</a>
</pre>
syntax-end -->
<p>Defines a split point for a table. Use a bind parameter with preparedStatement.setBinary(int,byte[]) to supply arbitrary bytes.</p>
<p>Example:</p>
<p class="notranslate">'A'</p>
<h3 id="table_expression" class="notranslate">Table Expression</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">schemaName</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">AS</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#alias">tableAlias</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
{ [ <a href="index.html#name">schemaName</a>. ] <a href="index.html#name">tableName</a> } [ [ AS ] <a href="index.html#alias">tableAlias</a> ]
</pre>
syntax-end -->
<p>A reference to a table. Joins and sub queries are not currently supported.</p>
<p>Example:</p>
<p class="notranslate">PRODUCT_METRICS AS PM</p>
<h3 id="order" class="notranslate">Order</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#expression">expression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">NULLS</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">FIRST</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">LAST</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
{ <a href="index.html#expression">expression</a> } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
</pre>
syntax-end -->
<p>Sorts the result by an expression. Only supported for queries that use <code>GROUP BY</code>.</p>
<p>Example:</p>
<p class="notranslate">NAME DESC NULLS LAST</p>
<h3 id="expression" class="notranslate">Expression</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#and_condition">andCondition</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">OR <a href="index.html#and_condition">andCondition</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#and_condition">andCondition</a> [ { OR <a href="index.html#and_condition">andCondition</a> } [...] ]
</pre>
syntax-end -->
<p>Value or condition.</p>
<p>Example:</p>
<p class="notranslate">ID=1 OR NAME='Hi'</p>
<h3 id="and_condition" class="notranslate">And Condition</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#condition">condition</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">AND <a href="index.html#condition">condition</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#condition">condition</a> [ { AND <a href="index.html#condition">condition</a> } [...] ]
</pre>
syntax-end -->
<p>Value or condition.</p>
<p>Example:</p>
<p class="notranslate">ID=1 AND NAME='Hi'</p>
<h3 id="condition" class="notranslate">Condition</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#operand">operand</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#compare">compare</a> <a href="index.html#operand">operand</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IN ( <a href="index.html#operand">constantOperand</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">NOT</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">LIKE <a href="index.html#operand">operand</a></code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IS</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">NOT</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">NULL</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">NOT <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#expression">expression</a> )</code></td></tr></table></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#operand">operand</a> [ <a href="index.html#compare">compare</a> { <a href="index.html#operand">operand</a> }
| IN ( { <a href="index.html#operand">constantOperand</a> [,...] } )
| [ NOT ] LIKE <a href="index.html#operand">operand</a>
| IS [ NOT ] NULL ]
| NOT <a href="index.html#expression">expression</a>
| ( <a href="index.html#expression">expression</a> )
</pre>
syntax-end -->
<p>Boolean value or condition. When comparing with <code>LIKE</code>, the wildcards characters are <code>_</code> (any one character) and <code>%</code> (any characters). To search for the characters <code>%</code> and <code>_</code>, the characters need to be escaped. The escape character is <code> \ </code> (backslash). Patterns that end with an escape character are invalid and the expression returns <code>NULL</code>.</p>
<p>Example:</p>
<p class="notranslate">NAME LIKE 'Jo%'</p>
<h3 id="compare" class="notranslate">Compare</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">< ></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">< =</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">> =</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">=</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">! =</code></td></tr></table></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<> | <= | >= | = | < | > | !=
</pre>
syntax-end -->
<p>Comparison operator. The operator != is the same as <>.</p>
<p>Example:</p>
<p class="notranslate"><></p>
<h3 id="operand" class="notranslate">Operand</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#summand">summand</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">|| <a href="index.html#summand">summand</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#summand">summand</a> [ { || } <a href="index.html#summand">summand</a> [...] ]
</pre>
syntax-end -->
<p>A string concatenation.</p>
<p>Example:</p>
<p class="notranslate">'foo'|| s</p>
<h3 id="summand" class="notranslate">Summand</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#factor">factor</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">+</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">-</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#factor">factor</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#factor">factor</a> [ { + | - } <a href="index.html#factor">factor</a> [...] ]
</pre>
syntax-end -->
<p>An addition or subtraction of numeric or date type values</p>
<p>Example:</p>
<p class="notranslate">a + b<br />a - b</p>
<h3 id="factor" class="notranslate">Factor</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#term">term</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">*</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">/</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#term">term</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#term">term</a> [ { * | / } <a href="index.html#term">term</a> [...] ]
</pre>
syntax-end -->
<p>A multiplication or division.</p>
<p>Example:</p>
<p class="notranslate">c * d<br />e / 5</p>
<h3 id="term" class="notranslate">Term</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#value">value</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="functions.html">Function</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#case">case</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#case_when">caseWhen</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#operand">operand</a> )</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#alias">tableAlias</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">columnName</a></code></td></tr></table></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#value">value</a>
| <a href="index.html#bind_parameter">bindParameter</a>
| function
| <a href="index.html#case">case</a>
| <a href="index.html#case_when">caseWhen</a>
| ( <a href="index.html#operand">operand</a> )
| [ <a href="index.html#alias">tableAlias</a>. ] <a href="index.html#name">columnName</a>
</pre>
syntax-end -->
<p>A value.</p>
<p>Example:</p>
<p class="notranslate">'Hello'</p>
<h3 id="bind_parameter" class="notranslate">Bind Parameter</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">?</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">: <a href="index.html#number">number</a></code></td></tr></table></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
?
| :<a href="index.html#number">number</a>
</pre>
syntax-end -->
<p>A parameters can be indexed, for example <code>:1</code> meaning the first parameter.</p>
<p>Example:</p>
<p class="notranslate">:1<br />?</p>
<h3 id="value" class="notranslate">Value</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#string">string</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#numeric">numeric</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#boolean">boolean</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#null">null</a></code></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#string">string</a> | <a href="index.html#numeric">numeric</a> | <a href="index.html#boolean">boolean</a> | <a href="index.html#null">null</a>
</pre>
syntax-end -->
<p>A literal value of any data type, or null.</p>
<p>Example:</p>
<p class="notranslate">10</p>
<h3 id="case" class="notranslate">Case</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CASE <a href="index.html#term">term</a> WHEN <a href="index.html#expression">expression</a> THEN <a href="index.html#term">term</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ELSE <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">END</code></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
CASE <a href="index.html#term">term</a> { WHEN <a href="index.html#expression">expression</a> THEN <a href="index.html#term">term</a> } [...]
[ ELSE <a href="index.html#expression">expression</a> ] END
</pre>
syntax-end -->
<p>Returns the first expression where the value is equal to the test expression. If no else part is specified, return <code>NULL</code>.</p>
<p>Example:</p>
<p class="notranslate">CASE CNT WHEN 0 THEN 'No' WHEN 1 THEN 'One' ELSE 'Some' END</p>
<h3 id="case_when" class="notranslate">Case When</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CASE WHEN <a href="index.html#expression">expression</a> THEN <a href="index.html#term">term</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ELSE <a href="index.html#term">term</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">END</code></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
CASE { WHEN <a href="index.html#expression">expression</a> THEN <a href="index.html#term">term</a>} [...]
[ ELSE <a href="index.html#term">term</a> ] END
</pre>
syntax-end -->
<p>Returns the first expression where the condition is true. If no else part is specified, return <code>NULL</code>.</p>
<p>Example:</p>
<p class="notranslate">CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END</p>
<h3 id="name" class="notranslate">Name</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">A-Z | _</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">A-Z | _</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">0-9</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#quoted_name">quotedName</a></code></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
{ { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } | <a href="index.html#quoted_name">quotedName</a>
</pre>
syntax-end -->
<p>Unquoted names are not case sensitive. There is no maximum name length.</p>
<p>Example:</p>
<p class="notranslate">my_column</p>
<h3 id="quoted_name" class="notranslate">Quoted Name</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">" anything "</code></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
"anything"
</pre>
syntax-end -->
<p>Quoted names are case sensitive, and can contain spaces. There is no maximum name length. Two double quotes can be used to create a single double quote inside an identifier.</p>
<p>Example:</p>
<p class="notranslate">"first-name"</p>
<h3 id="alias" class="notranslate">Alias</h3>
<!-- railroad-start -->
<code class="c"><a href="index.html#name">name</a></code>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#name">name</a>
</pre>
syntax-end -->
<p>An alias is a name that is only valid in the context of the statement.</p>
<p>Example:</p>
<p class="notranslate">A</p>
<h3 id="null" class="notranslate">Null</h3>
<!-- railroad-start -->
<code class="c">NULL</code>
<!-- railroad-end -->
<!-- syntax-start
<pre>
NULL
</pre>
syntax-end -->
<p><code>NULL</code> is a value without data type and means 'unknown value'.</p>
<p>Example:</p>
<p class="notranslate">NULL</p>
<h3 id="data_type" class="notranslate">Data Type</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="datatypes.html#char_type">charType</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#varchar_type">varcharType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#integer_type">integerType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#bigint_type">bigintType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#decimal_type">decimalType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#timestamp_type">timestampType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#date_type">dateType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#time_type">timeType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#unsigned_long_type">unsignedLongType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="datatypes.html#unsigned_int_type">unsignedIntType</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="datatypes.html#binary_type">binaryType</a></code></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="datatypes.html#char_type">charType</a> | <a href="datatypes.html#varchar_type">varcharType</a> | <a href="datatypes.html#integer_type">integerType</a> | <a href="datatypes.html#bigint_type">bigintType</a> | <a href="datatypes.html#decimal_type">decimalType</a> | <a href="datatypes.html#timestamp_type">timestampType</a> | <a href="datatypes.html#date_type">dateType</a> | <a href="datatypes.html#time_type">timeType</a> | <a href="datatypes.html#unsigned_long_type">unsignedLongType</a> | <a href="datatypes.html#unsigned_int_type">unsignedIntType</a> | <a href="datatypes.html#binary_type">binaryType</a>
</pre>
syntax-end -->
<p>A type name.</p>
<p>Example:</p>
<p class="notranslate">CHAR(15)<br />VARCHAR<br />VARCHAR(1000)<br />INTEGER</p>
<h3 id="string" class="notranslate">String</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">' anything '</code></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
'anything'
</pre>
syntax-end -->
<p>A string starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string.</p>
<p>Example:</p>
<p class="notranslate">'John''s car'</p>
<h3 id="boolean" class="notranslate">Boolean</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">TRUE</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">FALSE</code></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
TRUE | FALSE
</pre>
syntax-end -->
<p>A boolean value.</p>
<p>Example:</p>
<p class="notranslate">TRUE</p>
<h3 id="numeric" class="notranslate">Numeric</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#int">int</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#long">long</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#decimal">decimal</a></code></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
<a href="index.html#int">int</a> | <a href="index.html#long">long</a> | <a href="index.html#decimal">decimal</a>
</pre>
syntax-end -->
<p>The data type of a numeric value is always the lowest possible for the given value. If the number contains a dot this is decimal; otherwise it is int, long, or decimal (depending on the value).</p>
<p>Example:</p>
<p class="notranslate">SELECT -10.05<br />SELECT 5<br />SELECT 12345678912345</p>
<h3 id="int" class="notranslate">Int</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">-</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
[ - ] <a href="index.html#number">number</a>
</pre>
syntax-end -->
<p>The maximum integer number is 2147483647, the minimum is -2147483648.</p>
<p>Example:</p>
<p class="notranslate">10</p>
<h3 id="long" class="notranslate">Long</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">-</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
[ - ] <a href="index.html#number">number</a>
</pre>
syntax-end -->
<p>Long numbers are between -9223372036854775808 and 9223372036854775807.</p>
<p>Example:</p>
<p class="notranslate">100000</p>
<h3 id="decimal" class="notranslate">Decimal</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">-</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">. <a href="index.html#number">number</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
[ - ] { <a href="index.html#number">number</a> [ . <a href="index.html#number">number</a> ] }
</pre>
syntax-end -->
<p>A decimal number with fixed precision and scale. Internally, <code>java.lang.BigDecimal</code> is used.</p>
<p>Example:</p>
<p class="notranslate">SELECT -10.5</p>
<h3 id="number" class="notranslate">Number</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="d"><code class="c">0-9</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
0-9 [...]
</pre>
syntax-end -->
<p>The maximum length of the number depends on the data type used.</p>
<p>Example:</p>
<p class="notranslate">100</p>
<h3 id="comments" class="notranslate">Comments</h3>
<!-- railroad-start -->
<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">- - anything</code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">/ / anything</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">/ * anything * /</code></td></tr></table></td><td class="le"></td></tr></table>
<!-- railroad-end -->
<!-- syntax-start
<pre>
-- anything | // anything | /* anything */
</pre>
syntax-end -->
<p>Comments can be used anywhere in a command and are ignored by the database. Line comments end with a newline. Block comments cannot be nested, but can be multiple lines long.</p>
<p>Example:</p>
<p class="notranslate">// This is a comment</p>
<!-- [close] { -->
</div>
<!-- } --></body></html>