WHERE clause

Link to this posting

Postby Ursego » 22 Feb 2013, 08:21

Join tables using the ANSI (the "new") syntax (with keyword JOIN) instead of listing the tables in FROM clause and restrictions in WHERE clause (the "old" syntax).

Of course, using of ANSI syntax must be allowed by coding standards of your company. I had used the old one for a few years before coming to one of my projects where I was forced to adopt the coding rules of the company, and these rules forced developers to use only the "new" method. In the first days of working there I was feeling a bit uncomfortable, but now I am happy and recommend everybody to move to the ANSI standard - it is more understandable, and when you are looking at one table of a complicated join, you in fact concentrate on that table only. Previously, the WHERE clause was a terrible heap where many tables were mixed. Now you have a few simple fragments instead of one complicated.

*** BAD code: ***

Code: Select all
 SELECT @country_name = country.country_name
   FROM country,
  WHERE country.country_id = city.country_id
    AND city.city_id = address.city_id
    AND address.address_id = @address_id

*** GOOD code: ***

Code: Select all
 SELECT @country_name = country.country_name
   FROM country
   JOIN city
     ON city.country_id = country.country_id
   JOIN address
     ON address.city_id = city.city_id
    AND address.address_id = @address_id
User avatar
Site Admin
Posts: 128
Joined: 19 Feb 2013, 20:33

IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:

free counters

eXTReMe Tracker