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?

Answer 1

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.

Answer 2

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).