How to Write SQL Queries

SQL is a declarative, English-like domain language for querying, analyzing and manipulating data. SQL originated from relational databases but has since been widely adopted elsewhere. SQL is considered a declarative language, meaning users declare what results they want and not how to get to these results (the latter is the approach of imperative programming languages, such as C, Java and Python). Because of this and the ability to read SQL statements almost as English-language sentences, SQL is generally seen as one of the best high-level declarative programming languages for analyzing data due to its easy-to-learn syntax.

SQL has different language elements that can be divided at a high level between queries and data manipulation. SQL queries use the SELECT statement, while SQL used for data manipulation uses the INSERT, UPDATE, DELETE and MERGE statements. The data manipulation statements are collectively called Data Manipulation Language or DML.

This article will break down the anatomy of SQL query language, while the second part of the series will describe DML.

Defining SQL Queries

SQL queries are probably the most common operations used in SQL, as they allow users to retrieve and analyze data from one or more tables. SQL query statements include the following elements:

1. SELECT and FROM
2. SELECT without FROM
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY
8. OFFSET
9. FETCH
10. OFFSET and FETCH

The SELECT statement includes a couple of elements, but only the first two are required: SELECT and FROM. However, some databases including Oracle Database and MySQL make the FROM clause optional if the SELECT refers only to self-contained expressions, such as SELECT 1;SELECT sysdate; and SELECT my_function();. In these cases, the data is not derived from a table, hence FROM is not required.

Optional components are illustrated by putting [ ] around them.

A common misconception is that these components execute in the same order as they appear in the query. This is not the case, as the SELECT component is processed after the HAVING clause. The following lists the order that which the clauses are processed and their purpose:

1. FROM: Indicates which table(s) to retrieve data from. The FROM clause determines the working set of data that is being retrieved. This usually refers to a table but can also include a subquery (another SELECT query that acts as the input source for the current query).
2. JOIN: Specifies the rules for joining multiple tables. The JOIN clause is part of the FROM clause and combines the data from multiple tables into one data set. It is one of the fundamental operators of the relational model to combine different relations into one set. The JOIN clause allows join conditions that ensure that only rows that logically belong together are joined (rows with matching primary key –> foreign key relationships). Multiple JOIN clauses can be specified to join multiple tables into the data set. Because the JOIN clause is part of the FROM clause, it cannot be specified without a preceding FROM statement in the query.
3. WHERE: Filters the rows returned by the query. The WHERE clause filters the data set based on the provided predicates or filter conditions and discards all rows that do not match them. It narrows down the results, for example, to retrieve all countries from the continent of Europe instead of all countries in the world.
4. GROUP BY: Aggregates (or groups) rows with common values in the specified column(s) into one row. The resulting row is sometimes referred to as a grouping set. Because rows with common values are aggregated into one row, there will only be as many rows as there are unique values. For values of columns not specified in GROUP BY, aggregate functions in the SELECT clause are required to aggregate these values per group.
5. HAVING: Filters rows resulting from the GROUP BY clause. It is therefore a part of the GROUP BY and cannot be used without a preceding GROUP BY statement in the query.
6. SELECT: Defines the list of column(s) and expression(s) to appear in the query result output. The SELECT clause computes any expressions and defines the list of columns to be returned, or projected, as the query result.
7. ORDER BY: Identifies which column(s) to use to sort the resulting data and in which direction to sort them (ascending or descending). If the ORDER BY is omitted, the order of the rows returned by a SQL query is undefined.
8. OFFSET: Specifies the number of rows to skip in the result set before returning the data.
9. FETCH: Specifies the number of rows to return from the result.

Using SQL Queries

Now that you’re familiar with what the various SQL query clauses mean, you can start using them. You can follow along with these exercises using the data model in my GitHub repository.

SELECT and FROM

In its simplest form, a SQL query is comprised of a SELECT and a FROM clause:

This query selects all rows and all columns (as indicated by the * after SELECT, which is short for “all columns”) from a table called regions. If you want to return a given list of columns, you can call them out specifically:

SELECT without FROM

The SELECT statement can also compute expressions, for example, 1+2. Technically, neither the constant 1 nor the constant 2 come from any table, but the ISO SQL standard nevertheless requires a FROM clause. Many databases have “dummy” tables to enable such queries, such as the dual table in Oracle Database.

However, many databases, including Oracle Database, have loosened this restriction from the SQL standard and allow queries to omit the FROM clause in such cases:

JOIN

The relational model is all about normalizing data, that is putting independent data into separate tables and defining relationships between these tables. To recombine normalized data, you can use joins to join these tables back together.

The following example has two tables: the previously queried regions table, and the new countries table. To write a query that joins both tables into one result, you use the JOIN clause. Without a JOIN clause, if you specify the two tables in the FROM clause, every row from the table regions will be multiplied by every row from the table countries. This is often referred to as the cross product and it’s a common mistake that SQL beginners make. For example:

The output from this query is obviously incorrect. There are neither 1,372 countries, nor is Austria located in Africa. What we really want is to join all rows from the countries table with the regions table where the region_id is the same. This is generally referred to as the join condition and can be specified in the ON clause as part of the JOIN clause:

That’s more like it!

There is one more thing to note: The above queries specified SELECT r.name, c.name and put the letters r and c next to the table names. These are table aliases and are required to tell the database which table column you want. If the statement just said SELECT name, name, it would be unclear whether the query refers to the regions table column name or the countries table column name.

WHERE

The WHERE clause filters the rows produced by the FROM clause. Until now, you always got all the rows that were in the tables. If you want to return only all countries in South America, this is where the WHERE clause comes in. It can be used to match all rows with the regions.name column value 'South America':

GROUP BY

The GROUP BY clause is often a puzzle for SQL beginners. The clause is used to aggregate multiple rows into a group, essentially combining multiple rows into one row. When would such a thing ever be useful? Well, for example, the countries table contains a column called population but the regions table does not:

A common business question might be: “What is the total population of each region?” Given that the regions table does not have a column with that information, the answer can be provided only by calculating the sum of the population column for each country per region. So, you need a mechanism that takes the 196 rows of the countries table and puts them into seven groups or buckets based on their region (because there are seven rows in the regions table). However, the query cannot just put the 196 rows into seven rows; it needs to calculate the sum of the population per region based on the populations of the countries belonging to the region.

This can be done by applying the SUM() aggregate function over the population column:

This query shows something else interesting. Although there are seven regions in the regions table, the query produced six rows. This is because there is a region 'Antarctica', but there is no country with that region_id in the countries table. Hence, the JOIN clause filters that region out (because there is no matching region_id in the countries table as specified by the ON clause).

A GROUP BY clause does not require any JOIN clause; you can create groups in just a single table. For example, “How many countries start with the same letter?” can also be answered via a GROUP BY. To do this, create as many groups per the unique first letter value for all rows by using the SUBSTR() function, and then count the rows that fall into that group or category:

HAVING

The HAVING clause filters rows resulting from the GROUP BY clause based on the predicate(s) provided. For example, if you want to return only the regions that have a population of more than 500 million people, this cannot be specified in the WHERE clause because the WHERE clause is processed before the GROUP BY clause. Hence, the WHERE clause has no notion of the population of a region. This is where the HAVING clause comes in. From a logical perspective, it behaves the same as the WHERE clause, but it filters at a different processing stage:

ORDER BY

The ORDER BY clause sorts the resulting data. So far, the undefined sorting of the rows has worked out, except when it came to “countries per first letter.” The ORDER BY clause can be used to return the rows in alphabetical order:

By default, rows are sorted in ascending order, but you can reverse it with the DESC (DESCENDING) keyword:

OFFSET

The OFFSET clause specifies the number of rows to skip before starting to return data. This clause is shorthand for what would otherwise require analytic queries or subqueries. For example, asking “Give me all countries in South America ranked by square kilometers except the first three” can be answered with:

FETCH

The FETCH clause specifies the number of rows to return from the result. Some databases call this the LIMIT clause. Like the OFFSET clause, this is also a shorthand and can be used to answer business questions like “What are the top three countries in terms of population?” This can be answered with:

You may wonder what would happen if two rows tie on the third position; will both rows be returned? Or just the first? For these cases, the FETCH clause provides the ONLY and WITH TIES keywords. The above just used ONLY because it is unlikely that two countries have the same population.

However, when ranking countries by letter, there is much more room for overlap. For example, in the countries per first letter example, when ranked by number of countries, it is clear that some letters have the same number:

If you run the same FETCH clause on this query, the letter C would be omitted from the results, although it has exactly the same number of countries as the letter B:

This is where the WITH TIES keyword comes in, as it will include ties in the results:

OFFSET and FETCH

Combining the OFFSET and FETCH clauses allow another nice shorthand that would otherwise require an analytical query or subquery. Consider the question “What is the second smallest country on the planet in terms of square kilometers?” This can be answered by combining OFFSET to return results starting from the second row, and FETCH to fetch just the second row:

What’s Next?

My second article in this series will break down the anatomy of SQL’s data manipulation language (DML). You can find the data model used in this article and part two in my GitHub repository for this exercise.

Oracle offers a wide range of technologies for building, testing, and maintaining applications in the cloud and in your data center. Find free tools and learning resources at developer.oracle.com

Learn More

The latest from Oracle

Gerald Venzl has worked as a developer, DBA, performance engineer, software architect, consultant and enterprise architect. He is still active as a developer in his free time and on open source projects. Gerald is currently VP, developer initiatives, Oracle Database.

Read more from Gerald Venzl