Productivity Tips and Tricks for Writing SQL

If you’re a database developer, database administrator, or data analyst, writing SQL to get data into and out of databases is a key part of your job. Doing this quickly and effectively improves your productivity. Conversely, working with a tangled mess of statements and data leaves you stuck.

In this article, we’ll cover ways to structure SQL to make it easier to read and debug. Techniques such as common table expressions (CTEs) and table aliases can transform statements from indecipherable riddles to clear logic.

But before you start writing SQL, it’s important to ensure you’re on solid foundations. Laboring with a schema that’s a haphazard muddle of confusing names and data flaws is a challenge. Building on tables with clear names and clean data simplifies your selects. So, we’ll start by looking at how to get these foundations in place.

Choose Good Names

Good table names are clear and concise. The names for core tables in your application will be single-word nouns. These map to the corresponding business concepts. For example, customers, payments and invoices. Children of these tables extend the parent name with context like customer_addresses and invoice_items.

Sadly, naming your database objects is a rare luxury. Once you create a table or column, its name is fixed. While you can rename them, you have to change all code to the new name simultaneously. In large codebases, this is impractical.

The good news is there are tricks you can use to bring clarity to confusing names:

• Use views to do virtual renames.
• Add schema metadata.

A view is a stored query. You can use these to give a more understandable name to tables or columns. For example, this view makes it clear that the table cust_adrs stores customer addresses and the purpose of its columns:

create view customer_addresses as
  select c_id customer_id,
         a_id address_id,  
         st   start_date,
         en   end_date
  from   cust_adrs;

You can then use the view like a regular table. Provided you only give new aliases in the view — i.e., the only SQL clauses are select and from, and the select has no expressions — accessing the view is the same as using the table. Over time, you can shift code to use views with better names.

But this approach takes time. There will be an extended period while you’re still working with the original opaque names. Adding metadata can help give context to these.

Table and column comments — free-form text describing objects — are a widely supported way to do this.

Oracle AI Database 26ai extended this concept with schema annotations, the key-value pairs you can use to document your tables, views, columns and indexes. For example, these statements annotate the unclear names for the table cust_adrs and its column c_id with a descriptive display value:

alter table cust_adrs
  modify ( c_id annotations ( display 'Customer ID' ) );

alter table cust_adrs
  annotations ( display 'Customer Addresses' );

You can view the annotations by querying the [dba|all|user]_annotations_usage views:

select object_name, column_name, annotation_name, annotation_value
from   user_annotations_usage
where  object_name = 'CUST_ADRS';

OBJECT_NAME    COLUMN_NAME    ANNOTATION_NAME    ANNOTATION_VALUE      
CUST_ADRS      <null>         DISPLAY            Customer Addresses    
CUST_ADRS      C_ID           DISPLAY            Customer ID

Schema annotations have been backported to 19.28.

Using clear names is the first step to building a good foundation. The next is to structure your tables effectively.

Normalize Your Schema

Database normalization is the process of removing redundant information from your tables. This avoids data duplication and makes certain types of data errors impossible.

Working with normalized data means you spend less time dealing with data quality issues, such as finding and removing duplicate rows. This frees you up for more productive tasks like building new features.

The normalization process defines a series of normal forms. These are rules that tables must conform to in order to reach that level of normalization. The first three normal forms are:

• First normal form (1NF): Each row and column stores a single value and there are no duplicate rows.
• Second normal form (2NF): There are no columns that depend on part of a primary or unique key.
• Third normal form (3NF): There are no columns that depend on columns that are not part of a primary or unique key.

While higher normal forms exist, these relate to overlapping keys and multiple many-to-many relationships. These are rare in practice. Ensuring your tables are in 3NF will cover most cases you work with.

A good smell check to see if a table is normalized to at least 3NF is to ask:

“If I update one column in a table, does that imply I have to update other columns simultaneously?”

If the answer is yes, you’ve almost certainly violated a normal form. To fix this, split the dependent columns into a new table or remove them altogether.

For example, say you’re building a quiz-taking app. When players submit answers, you want to record the time they started, finished, and took to complete a quiz, alongside their answer. This gives a table like:

create table quiz_answers (
  quiz_id    integer,
  user_id    integer,
  answer     clob,
  start_time timestamp,
  end_time   timestamp,
  time_taken interval day to second,
  primary key ( quiz_id, user_id )
)

But there’s a relationship between non-key values: time taken = end time – start time. Changing any of these three columns implies you have to change at least one of the other two also. Avoid this inconsistency by removing one of these columns from the answers table.

Note there is an exception to the update test. This arises if you change all the columns in a table’s primary key or one of its unique constraints. In this case, you’re changing an identifier for the row, so other values will likely change as well.

As with bad names, unnormalized tables are tricky to change in existing applications. Normalizing your data from the start saves you from wading through junk data.

But normalization alone is not enough to save you. To keep your data clean, you should also create constraints.

Create Appropriate Constraints

Database constraints enforce data rules. The database ensures all data meet these rules.

Without constraints in place, data errors will creep in, which can cause customers to lose faith in your applications. Finding and fixing these errors is time-consuming. Creating constraints at the start avoids this pain.

The main constraints are:

• Primary key: Ensures values are mandatory and unique. A table can only have one primary key.
• Unique constraints: Like a primary key, a unique constraint stops you from storing duplicate values. Unlike a primary key, you can store nulls in unique columns, and one table can have many unique constraints.
• Foreign keys: Define a parent-child relationship. The foreign key points from columns in the child table to the primary key or a unique constraint in the parent. With this in place, you can’t have orphaned rows.
• Not-null constraints: Ensure you can store only non-null values in the columns, i.e., they’re mandatory.
• Check constraints: Verify a condition is true or unknown for every row.

Defining these constraints helps cement the foundations laid by normalization. For example, primary keys or unique constraints are necessary to enforce the “no duplicate rows” rule in 1NF.

Constraints can also help if you find yourself working with unnormalized data. While discussing normalization, we saw how storing start times, end times and durations for quiz answers can lead to inconsistencies. While removing one of these columns is the best solution, this may be impractical in a longstanding application.

Instead, you can ensure that all data conforms to the formula by adding this check constraint:

alter table quiz_answers
  add constraint quan_answer_time_c
  check ( ( end_time – start_time ) = time_taken )

Once in place, new data that violates this rule will be rejected.

Unfortunately, it’s likely there is existing data where this rule is false. If so, adding the constraint will fail, and you’ll have the time-consuming job of fixing it. Fortunately, there’s a trick you can use to stop more invalid data from arriving:

Create unvalidated constraints.

These ignore existing data and apply the rules only to new data. Do this in Oracle Database with the following:

alter table … add constraint … novalidate;

While you should still clean the existing data, you can be sure that no new errors will creep in.

Choosing good names, normalizing your tables, and creating constraints give you a solid structure to be productive when writing SQL. With these in place, we can turn our attention to structuring your SQL effectively.

Structure Queries Clearly

Large SQL statements can be hard to read and debug. CTEs, aka the with clause, enable you to break them into smaller parts.

CTEs are named subqueries that come at the top of select statements. You access these subqueries like regular tables later in the query.

• You can build the query bit-by-bit.
• You can give each CTE a meaningful name.
• You can check the results of each CTE.

For example, the Oracle Dev Gym offers free quizzes, workouts and classes to help you learn SQL. Each of these activities has its own tables. Combining all these in one query to report all activities is a daunting task.

Using the with clause, you can create a CTE for each activity type. You can start with getting quiz totals:

with quiz_totals as ( … )
select * from quiz_totals

Then add workout totals and verify they are correct:

with quiz_totals as ( … ),
     workout_totals as ( … )
select * from workout_totals

Repeat this for class totals and combine the results of each CTE to get all totals, like so:

with quiz_totals as ( … ),
     workout_totals as ( … ),
     class_totals as ( … ),
     all_totals as (
       select * from quiz_totals union all
       select * from workout_totals union all
       select * from class_totals
     )
select * from all_totals

If you need to change the queries for any activity type, it’s clear that the logic is contained in the corresponding CTE. It is far simpler than hunting through a mass of nested subqueries.

Using CTEs to break up logic into smaller problems makes the process more manageable. However, each CTE can still reference many tables. Whenever you’re working with many tables, there’s an important question to answer: Which columns belong to which table?

Make this clear by prefixing each column with its table’s alias.

Use Good Table Aliases

Without table aliases, knowing where each column is from is tough. This makes queries harder to understand and change.

However, unaliased columns have a bigger problem: they can lead to errors.

The most common issue is when two tables have columns with the same name. If you use the unaliased name, the database cannot identify which table it’s from, and the statement will fail. What’s worse is this problem can affect existing SQL if you add a column that causes a name clash.

Qualifying columns with their table avoids these problems. Single-letter table aliases taken from the start of the table name are appealing but can quickly lead to problems. For example, say you write a query that accesses both the customers and contracts tables. If you give one the alias “c,” how do you know which it relates to without scrolling through the statement?

A better approach is to use four-character aliases taken from the start of the table name:

• For a single-word table, the alias is its first four characters.
• Two-word tables take the first two letters of each word.
• Three-word tables use the first two letters of the first word and the first letter of the last two words.
• Four-word tables use the first character of each word.

For example,

• customers => cust
• order_items => orit
• shipment_list_batches => shlb

In rare cases, this gives different tables the same alias. If this happens, pick a new alias for one table, following this system as closely as possible. If you need to access the same table twice in a query, add a prefix to the alias stating the table’s role. The columns you’re joining them on are a good source for this.

For example, you may need to join customers to their delivery and payment addresses, both stored in the addresses table. Adding deli or paym as appropriate makes it clear which role the address table plays:

from customers cust
join addresses deli_addr
on   cust.delivery_address_id = deli_addr.address_id
join addresses paym_addr
on   cust.payment_address_id = paym_addr.address_id

Using a standard aliasing system quickly becomes second nature, makes it clear which table columns belong to and avoids errors. A standard structure is key to further aid the readability of your code.

Use a Consistent Style

The best way to format your SQL is the source of many debates. We all have our own preferences for where and how to indent clauses. Whether keywords should be in uppercase or lowercase is a long-running battle.

Ultimately, most of these choices come down to personal preference. So, the most important advice is:

Choose a formatting style and stick to it.

However you like to format your SQL, we can all agree that mixing and matching styles within a statement like this is jarring and hard to read:

SELECT Some_Columns
    From a_table
  JOIN another_table
on …

The best way to ensure a standard style is to use your editor’s auto formatter. Run it after writing each statement. This is quicker than formatting as you go. You can also share the rules with your colleagues to keep your whole codebase formatted similarly.

Occasionally, auto formatters can struggle to spot where to place line breaks in complex SQL using niche features. This can result in combining expressions into long lines that scroll right off the edge of the screen.

If you hit this problem, a trick to overcome it is to place an empty comment where you want line breaks. The formatter has to respect these, guaranteeing a line break exactly where you want it.

For example:

select case --
  when formatted_lines_are_too_long --
  then 'Use comments to break them up' --

Using a standard formatter is one of the many ways your editor can help you write SQL faster, so it’s worth investing time to learn your editor’s productivity features.

Get To Know Your Editor

You’ve likely enabled autocomplete for table and column names to help you write SQL. But this is just one way your tools can help you be more productive.

For example, the Oracle SQL Developer extension for VS Code has a few gems to help you.

You can drag tables or columns from the schema browser into the editor. It then asks you whether to use these in a select, insert, update or delete statement:

This saves you from having to type out every column by hand, a tedious task for tables with many columns.

You can also configure code snippets in VS Code that expand short sequences into large code blocks. Here are a few that I rely on to speed up the process of writing SQL:

• ssf => select * from
• ii => insert into $1 values ( $2 )
• crt => create table $1 ( c1 int );
• drt => drop table $1 cascade constraints purge;

Spending time learning the keyboard shortcuts in your editor for everyday tasks can also reap good rewards.

Conclusion

Business requirements can be complex. Translating these to SQL can be challenging and can lead to massive monstrosities if you’re careless.

Taking care to structure SQL clearly by using CTEs and good table aliases can speed up both SQL’s writing and maintenance processes. Using auto formatters and other tools in your editor can further streamline tasks and enhance your productivity.

But the most significant gains come from building a solid data model. Choosing good names, normalizing your tables, and creating constraints make understanding your schema simple and writing SQL a snap.

This blog was originally posted in two parts at

• https://thenewstack.io/3-foundational-principles-for-writing-efficient-sql/
• https://thenewstack.io/3-sql-writing-tips-and-tricks-to-enhance-productivity/

UPDATE Oct 2025: Adding details of annotations backport to 19.28

UPDATE Oct 2025: Oracle AI Database 26ai replaces Oracle Database 23ai