Never forget to use aliases in your SQL queries

Newest frameworks does all hard work for us, I known a lot of programmers, most commonly Java developers, that don’t know anything about SQL, they only know about what can do with their preferred persistence framework. EclipseLink, Hibernate and others, have a solid implementations to the most known databases. These implementations always use columns and tables aliases due framework mapping.

When we are working with reports using SQL, and not Java objects, is our responsibility to know about SQL ANSI and database specifics, like PostgreSQL schemas, PSQL on Oracle, etc. It’s in this time that many developers fall down. Generally they only know to write something like that:

[sql]SELECT * FROM bar ORDER BY createdAt[/sql]

Don’t be like those guys!

The query below doesn’t have problems, except if ‘bar’ is a giant table with a lot of row (millions or billions). But, the problem is when we need to join many tables, ambiguous columns are the first problem, followed by wrong columns on our select.

[sql]select id, name from product, category where category_id = id order by description[/sql]

In the query above, is so easy to identify possible errors:

‘id’ can be a primary key on product table and in category too;
‘description’ follows the same principle, where is it from?

When you name you tables, you don’t have these headaches.

[sql]select c.id, c.name, p.price from product p, category c where p.category_id = c.id order by p.description[/sql]

Another important point is about database scope, like on many programming languages, scopes delimit variables’ visibility. Sometimes, when you have sub queries, you’ll pay attention to it.

[sql]select * from product where id not in (select id from defective_product);[/sql]

Sometimes MySQL can run correctly, another one, it cannot, its just lose internally and doesn’t match the correct ‘id’ fields. One more time, aliases can save them:

[sql]select p.* from product p where p.id not in (select fp.id from defective_product fp);[/sql]

Follow these principles even when you have more than one nested sub query.

select 
   p.* 
from 
   product p 
where 
   p.id not in (
      select 
         fp.id 
      from 
         defective_product fp 
      where 
         fp.category_id in (
            select cc.id from closed_category cc
         )
   )
;

select

p.*

from

product p

where

p.id not in (

select

fp.id

from

defective_product fp

where

fp.category_id in (

select cc.id from closed_category cc

)

;

Believe, aliases can save you from a tons of problems.

Share: