Using ANSI Style SQL
September 5th, 2010
As a software vendor supporting MysQL, Oracle and MS SQL, it is often a challenge to get our developers to write consistent SQL calls for our database abstraction layer. How do we make sure that our queries will work for all 3 databases, easy to read, easy to understand and ultimately easy to support.
A lot of developers choose to work with the old Theta style when writing queries, as it is simpler to understand to begin with, for example
SELECT e.name, d.dept_name, r.region_name
FROM employee e, dept d, region r
WHERE d.id=e.dept_id
AND d.region_id=r.id
AND r.region_name=’EMEA’;
In ANSI style, this is equivalent to
SELECT e.name, d.dept_name, r.region_name
FROM employee e
INNER JOIN dept d on d.id=e.dept_id
INNER JOIN region r on r.id=d.region_id
WHERE r.region_name=’EMEA’;
I took me a while myself to become an ANSI style convert, but once I am used to using it, I can see that ANSI style offers many advantages.
Absolute CLARITY when performing outer joins.
I have worked with databases for close to 19 years, and I always get confused when using (+) in an outer join. An outer join uses a (+) on the side of the operator where we want to have nulls returned of if no value matches (now who can remember that?) ANSI style outer joins is so much more intuitive.
– When t2 might have no value matched
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.col1=t2.col1;
– When t1 might have no value matched
SELECT *
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.col1=t2.col1;
– When no value matched could be on either table
SELECT *
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.col1=t2.col1;
ANSI makes you think and write better queries
it makes you think about how you want the database engine to process your query. Rather than just stringing all your tables in the FROM clause (like FROM table1, table2, table3, table4) and hope for the best, it makes you think which one is your base table, what filtering you want applied to this table, and then whether a particular join functions as a filtering, or more as an enrichment. In the example below, I am interested to find the skillsets owned by an employee with the name SMITH who works in SUPPORT. So the way to write this query, is to as quickly as possible filter out the data that we do need from the EMP/DEPT table, prior to joining with the SKILLS table.
SELECT e.first_name, e.surname, d.dept_name, s.skill_name
FROM emp e
JOIN dept d ON d.id=e.dept_id and d.dept_name=’SUPPORT’ and e.surname=’SMITH’
JOIN SKILLS s on e.id=s.emp_id ;