Use ANSI Style for MySQL

I prefer to use ANSI style for writing MySQL queries.  When I started working on SQL, I used the old style aka Theta style for all my queries. Example of a query in  Theta style :

SELECT  e.title, i.first_name, i.response

FROM invitees i,

events e

WHERE e.id = i.event_id

AND e.date = ‘2008-07-04’;

which , in ANSI style is equivalent to :

SELECT e.title, i.name, i.response

FROM invitees i

INNER JOIN events e on e.id = i.event_id

WHERE e.date = ‘2008-07-04’

Many developers prefer using the old style. But believe me, ANSI style is lot better and advantageous.

First, MySQL  supports OUTER JOINs in ANSI style. Second, ANSI style forces you to think about your joins  clearly. Your code is lot more readable in ANSI style. For example, all the joins go in the FROM clause, and the WHERE clause is left with only statements which filter out the result set of the tables in the join.  You get a clear picture of how the tables are linked with each other.

For more information on Joins, see Reference below.

Reference : Join Syntax in MySQL

Advertisements

2 Comments »

  1. shaka said

    is there any advantages on performance

  2. Hi Shaka,

    I believe there are no advantages on performance if we use ANSI style for SQL. But syntactically it is better.

    There is a general habit of putting table joins (city.country_id = country.id) in the WHERE clause along with filter statements like ‘city = “Pune”‘. If we use ANSI style then the join statements are written in the FROM clause and we will have only filter statements in the WHERE clause. Hence it makes the code more readable.

    Also, you can’t do OUTER Joins without using ANSI style.

    So its a good habit of writing SQL statements in ANSI style as early as possible while programming.

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: