Michael J.A. Clark
Michael Clark is a professional software developer who creates high-quality products for startups in Cambridge, UK. Skills: C#, Java, PHP, XHTML, AS3, CSS, ML.

Sections

Contact details

Email
mjac@mjac.co.uk
Skype
mjacdotuk
Twitter
mjacuk

Conforming to the ANSI SQL standard

SQL is used for communicating with database servers. My preferred server MySQL does not conform to the ANSI standard and prevents my applications from being ported to other SQL databases like PostgreSQL or MS Access.

An incompatible query

This query executes successfully in MySQL:

SELECT * FROM `table` WHERE `id` = '20' && `date` < NOW()

It uses non-standard features:

  • Functions such as NOW(), used to retrieve the current date in MySQL, are not provided by every database server.
  • && represents, the correct, AND operator in a lot of programming languages but should not be used to replace it in SQL queries — AND is standard.
  • `id` = '20' should be `id` = 20: numeric values should be sent without quotes.

Back-ticks are not required in most cases.

Comments