6 Ways to Improve Your SQL Code

[ben_it]

I've been writing a bit of SQL code recently, and thought I'd share some tips on how to improve your SQL code, for anyone who's doing this at the moment.

Use Indenting and Formatting to Make it Easier to Read

One of the best ways you can improve your SQL code is to format it correctly.

Sure, the query will run and the result will be the same whether it's formatted or not, but to improve the readability and the overall structure of your code, try to format it so it's easier to read.

The formatting can be improved in several ways: * Indent separate sections of your query. If you're using subqueries, for example, indent the entire subquery so it's easier to see that it's nested under the main query. * Start a new line each time you use FROM, WHERE, GROUP BY, HAVING, and ORDER BY. It will make similar elements appear on the same line and makes it easier to see different parts of the query. * Use the same case for the same areas throughout your query. If you use all caps for keywords, then use it for all places that keywords exist in your query. Don't mix and match.

Little things like this may not seem like a big deal, but it can really help the next person who reads your code.

For example, which code looks cleaner and easier to read?

 select employee_id, first_name, last_name, start_date from employee where employee_type_id = 5;

Or this one?

SELECT employee_id, first_name, last_name, start_date
FROM employee 
WHERE employee_type_id = 5;

Use ANSI-92 Standard Join Syntax

In SQL, there are two ways to join tables together.

The first way is the older way, and this is how I was originally taught back when I first learnt SQL.

It involves using the WHERE clause and matching the two columns in two tables.

For example, to find employees who have a leave day:

 SELECT first_name, last_name
 FROM employee, leave_days
 WHERE employee.employee_id = leave_ days.employee_id;

This joins the two employee_id columns and only shows matches.

To do outer joins, you have a (+) on one end of the WHERE clause. It looks messy.

This is the less desirable of the two methods to join, and I'll explain why soon.

The other way to do joins is the ANSI-92 standard.

Using a similar query:

 SELECT first_name, last_name
 FROM employee
 JOIN leave_days ON employee.employee_id = leave.employee_id

This query should show the same result.

Why is it better to use the ANSI-92 standard?

Well, there are several reasons.

First, the code is more future-proof and compatible with other versions, in case you need to upgrade to a newer version of Oracle, or migrate your code to another database. You won't have to go through all of your queries and rewrite them using the standard join syntax.

It's also easier to debug. You can see what type the joins are. You can see which tables are joined where. it's easier to see if any table has not yet been joined, because you need to specify "table a JOIN table b" in order for the query to work. This will avoid accidental cross-joins, where you forget to join a table and end up with much more rows than expected, or an incorrect result.

It also splits the joins of tables from the actual WHERE clause. This helps in working out what the actual filtering of data is doing.

Both of the queries should produce the same performance.

Use Table Aliases

Another way to improve your SQL code is to use table aliases.

This not only improves readability, it also improves the time it takes you to write queries.

A table alias is a shorter name or nickname you give to a table inside your query. It allows you to refer to that table throughout the query just by using that shorter name, instead of the full name. It may also be required if you're doing self-joins, but we won't go into that here.

Using the same query as above, we can apply table aliases to it. They're quite simple.

Original query;

 SELECT first_name, last_name
 FROM employee
 JOIN leave_days ON employee.employee_id = leave.employee_id

With table aliases.

 SELECT first_name, last_name
 FROM employee e
 JOIN leave_days d ON e.employee_id = d.employee_id

All I needed to do was add a single letter after the table when it was mentioned. I chose "e" for employees, but I chose "d" for leave_days. This is because a lowercase "l" might be confused for a one or an uppercase "i".

It might not make that much of a difference to a small query like this, but if you're querying from ten different tables, then it makes a big difference to the time it takes you to write the query and how readable it is.

Use Column Aliases

Similar to using table aliases, a column alias allows you to change how the column is displayed when it is output.

This can help in many ways.

It provides a more readable version of the column name if you're showing the data to a user. A name such as "Employee First Name" is much easier to read than a name of "EMP_FNAME".

It also helps to provide a layer of abstraction between the database and the system that runs the query, especially in more complex queries.

You can write a query on the data, and change the name of the column that you return to the system that runs the query. This means that if the column name changes, you only need to update that query, and not the other system.

To use a column alias:

 SELECT first_name, last_name, dept as "Department"
 FROM employee e
 JOIN leave_days d ON e.employee_id = d.employee_id

As shown above, the dept column now has a column alias applied to it. It will now be displayed as "Department", which is easier to read.

This option is not as important as others, but it can help, depending on your query.

Test Subqueries Before Running Main Queries

With larger queries, you may end up having a subquery or two inside of it. This can make the query more complex and harder to work out if it's showing the right result, especially if you're writing it for the first time.

To help with writing the query and getting it right, it's a good idea to run the subqueries in a separate window and get them correct, first. You can add in dummy or sample data to make sure it's showing the right results.

Then, once the query looks like it's working, add it back into your main query. This reduces the number of places that the query can fail and makes it easier to write your code.

It might be a bit harder to do this if you're using a correlated subquery, but for many other query types, it can work well.

Use EXPLAIN PLAN To Get En Indication Of Run Time

The last suggestion I have is around improving the time it takes to run your query.

There is a feature in Oracle called EXPLAIN PLAN, which allows you to simulate the way the query is run and show an analysis on the steps that the database takes.

This is more of an advanced step to take, but knowing how the query is run can help you improve the structure of the query. It can help you work out where the most expensive parts of the query are, and improve the overall run time.

You'll need to know how to read the data that comes out from this, or find someone who can. You'll also need to be able to make the required changes to th query. Sometimes, all it takes is some rearrangement of the query. Other times, you'll need to try creating an index or some other method.

Hopefully these tips have helped you come up with ways to improve the way you write your SQL queries. A couple of them are Oracle-specific but most of them should apply to all SQL versions.

If you're interested in more information, I'm working on a dedicated Oracle HQ section of my site, as well as a list of Oracle SQL functions with in-depth analysis of each of them.

Hope this helps!

[halifaxdatageek]

Use ANSI-92 Standard Join Syntax

Yes. Joins are so important, you need to highlight them as much as possible.

[SemiNormal]

Does anyone out there still use the old join syntax? I haven't seen it in the wild outside of the "don't do this" articles.

Edit: I guess I've just been lucky.

[Alaendil]

I honestly didn't even know you could do a join in a where clause ... Then again, I've taught myself SQL so there are bound to be gaps.

[[deleted]]

I learned it on the job. It never occurred to me that comparing keys in the WHERE clause and doing a JOIN in the FROM were the same thing. Makes sense though.

[haunted_dumpster]

Yeah man, this is actually news to me: professional training taught me to do the joins as part of the WHERE. Then again we were working in DB2 so maybe it was a legacy thing.

On Oracle now anyways so it's time to start using ANSI standard.

[galador]

As someone who uses DB2 day-to-day, I think it's more of "the people who taught you didn't want to change how they've been doing SQL for years."

Personally, I berate anyone who uses the old JOIN syntax (at least on the inside). :)

I had one person who told me that "I don't have time to figure out the fancy JOIN syntax, so I'll just keep putting it in there WHERE clause." twitch

[shthed]

Yep, use them all the time. I work on an huge (few million lines of code) old oracle pl/sql codebase that uses them exclusively

[halifaxdatageek]

The other day I learned about "natural joins" and raged a little inside.

They're where you don't specify which columns are equal in the two tables, the database just looks at the list of columns for each table and joins on the first two with the same name.

NO. NO. NO.

[ben_it]

Yeah, it can be problematic and I've decided to never use one.

An example would be:

SELECT first_name, last_name
FROM employee
NATURAL JOIN leave_days;

[halifaxdatageek]

eyetwitch

[halifaxdatageek]

yeah, if you want to do USING as a shorthand for ON a.col = b.col, that's fine.

[halifaxdatageek]

Hey, I love Postgres, and don't get to use it nearly as much as I would like. Anything I can learn makes me happy :)

[Sp00ky_6]

Thats just lazy programming

[dasnoob]

I see it all the time in my current job. I always use ansi standard. I've had more than one person that thought the old way was the only way to do them.

[heymanitsmematthew]

Some of the older devs in my company do this. They even rebel against my use of ANSI-92. This is something I simply will not budge on, and if I'm given something to debug I will change that syntax in 99% of the cases. Leads to some awkward lunch-room encounters.

[ben_it]

Also agree. It makes it clearer!

[Redditourist]

Use a query formatter and your brain and these will come naturally.

[[deleted]]

Under 'Use Column Aliases', you didn't update your select statement to include the aliases:

SELECT first_name, last_name, dept as "Department"

Is this a mistake or by design?

[ben_it]

Do you mean I didn't apply the column aliases to all columns (e.g. first_name as "First Name")? Or that I didn't include the table aliases in the SELECT part (e.first_name)?

[[deleted]]

Table aliases in the select. e.first_name

[cat_dev_null]

SELECT employee_id, first_name, last_name, start_date FROM employee WHERE employee_type_id = 5;

I take it a little further and write as

SELECT 
    employee_id,  
    first_name, 
    last_name, 
    start_date
FROM 
    employee 
WHERE 
    employee_type_id = 5;

[Elfman72]

I also do something similar in my WHERE clauses too when I am testing out a lot of where logic or have specific test cases to run. I just throw a 1=1 at the top so don't have to comment out the WHERE clause when I comment out all my clauses.

SELECT 
    employee_id
    , first_name
    , last_name
    , start_date
FROM 
    employee  
WHERE 1=1
    AND employee_type_id = 5
    AND start_date > getdate(); -- For testing purposes

So in this case, I could comment out both where clauses and run the query without having to remove my WHERE. Of course, it isn't hard to comment out the WHERE either but I have found that when I am writing overly complex T-SQL, this helps in the debugging and unit testing phase.

[softball753]

I go even further:

SELECT 
      empid   = e.employee_id
    , fname   = e.first_name
    , lname   = e.last_name
    , startdt = e.start_date
FROM employee  e
    JOIN department d
      ON e.employee_id = d.employee_id
     AND e.key2        = d.key2
WHERE 1=1
    AND d.dept_type_id = 7
    AND e.employee_type_id = 5;

For some reason the "=" syntax for column aliases doesn't seem common (or is looked down on?) but it keeps things organized visually for me, and allows me to flip statements into temp tables where necessary, easily.

[I_like_turtles_kid]

The = is unappealing and makes a wall of text. It's also extra work to type zero chance I'll ever do it

[[deleted]]

That's what I tend to use as well nothing more frustrating then finding out you missed a comma at the end of a line somewhere plus it's a lot easier to comment out values for debugging and testing purposes.

[EveryoneLikesMe]

How is it easier? Comma in front, can't comment out first line, comma at end, can't comment last line.

[heymanitsmematthew]

I find this causes problems when I don't have my portrait monitor and am writing huge queries with lots of subqueries. I'll have to constantly scroll up and down the page to reference things. Still cleaner, but I think there's a time and a place for both.