Is the semicolon necessary in SQL?

Question

Sometimes it works anyway if I forget the ;. But sometimes it doesn't.

And in JDBC and Android SQLite, it seems that I don't need ; at all. I am confused.

When should I use a semicolon?

https://stackoverflow.com/questions/18487082/query-terminate-with-semicolon-get-sqlexception-and-process-does-not-terminat — Alex78191, Sep 24 '19 at 23:52

score 11 · Accepted Answer · answered Sep 22 '13 at 07:49

11

semicolon indicates end of a statement, so if there are multiple statements then you should use semicolon else it will work fine.

I generally use semicolon as a practice, it can be useful even when you are running queries on sql client e.g. in Sql Developer using semicolon is very helpful if you have multiple statements on worksheet, as you can simply go to that particular statement and use F9 to execute that, without semicolon this is not possible.

answered Sep 22 '13 at 07:49

Lokesh

7,810
6
48
78

6

In most JDBC drivers even if you add semicolon to separate statement it will not work ... – aleroot Sep 22 '13 at 08:01
2

Executing multiple statements in a single string is - by specification - not allowed in JDBC – Mark Rotteveel Sep 22 '13 at 10:28

aleroot · Answer 2 · 2013-09-22T07:53:53.137

It is not mandatory if you run a single query at time, it comes necessary instead if you want to run multiple query with a single command.

However in most of JDBC drivers out there it is not possible to add multiple query separated with semicolon in a single JDBC Command, it exist however the addBatch method that allow you to add multiple statements :

java.sql.Statement stmt=con.createStatement();   
stmt.addBatch(insert_query1); //insert_query1   
stmt.addBatch(insert_query2); //insert_query2

As a rule of thumb, in JDBC semicolon is not necessary at all, if you need multiple statement use addBatch.

score 4 · Answer 3 · edited Jan 26 '21 at 16:21

Usually the semicolon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semicolon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.

On the other hand, the JDBC API is intended to execute a single(!) statement at a time, therefore you don't need such a separator (the statement is the whole string). This means that a semicolon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.

Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).

score 3 · Answer 4 · answered Sep 22 '13 at 07:51

Depends on the DBMS and version number. Semicolons are often optional at the end of a single statement. But if you are going to execute a script with more than one statement, they need to be terminated by a semicolon.

Except maybe the last one. But it seems bad form to be inconsistent.

Is the semicolon necessary in SQL?

4 Answers4

Linked