You’ve learned the basics of SQL.
But what else is there to know?
There are many features available that can give you an “aha” moment and take your
SQL to the next level.
And the good news is that they are available in all of the popular database vendors - Oracle,
SQL Server, MySQL, Postgres, and probably others too.
In this video, I’ll explain 7 different SQL features that you may not have heard of,
show some examples, and share some resources for learning more about them.
Let’s get into it!
Welcome to the Database Star YouTube channel - the place for developers looking to improve
their database and SQL skills.
The first handy feature you might not know about is called “window functions”.
A window function is a way of writing a function that allows you to perform a calculation based
on a certain set of rows, but without the need for grouping your results.
It’s like performing an aggregate function, like SUM, on a set of rows, but seeing all
results.
One common use for this is to calculate a running total.
Without window functions, you might need to SELECT your data and calculate a running total
inside your application code, such as JavaScript or PHP.
However, you can use window functions to calculate it in SQL.
This means all of our logic is in SQL and the database can run the calculations efficiently.
You don’t need to do any additional calculations in your application.
Let’s see an example.
We’ve got some sample data set up here.
For all of the sample data used in this video, check out the link in the description to the
script in my GitHub repository.
I’ll also be demonstrating this in MySQL, but the feature works the same way in Oracle,
SQL Server, and Postgres.
So we’ve got our sample data here, and let’s say we want to see a running total of the
order amount.
A running total is where a SUM function is used on the current value and all values before
it.
We can write the SUM function using the “window function” syntax.
I’ve got another video you can watch that goes more in-depth on window functions, which
I’ve linked here.
Using SUM as a window function looks like this.
We have the SUM function, and the expression or column we want to sum, which is the order
amount column.
We then have the keyword OVER, which indicates that this is to be used as a window function.
Inside the brackets, we have a Partition By clause, and an Order By clause.
Both of these are optional.
The Partition By clause will let you define the window or subgroup of data to look at.
The Order By clause defines the order that the function will run on the data.
This is different to how the results are ordered.
For our example, we can omit the Partition By clause, as we want to calculate the running
total of all records.
If we wanted the running total per month, for example, we would use this Partition By
clause.
We use the Order By clause to define what the previous row is when we calculate a running
total.
How do we know what the previous row is?
It’s the one with the previous order ID.
So, we use ORDER BY, then order_id, then ASC.
Next, we’ll give it a column alias of running_total, so we know what is in the results.
We add an Order By to the end of the query so that the results are shown in the same
order the running total is shown.
We can run this query, and see our results here.
We can see that all orders are shown, and there is a new column called running_total
that shows the running total.
This was calculated using a window function.
Window functions are powerful and very useful in certain situations.
So if you ever need to perform calculations on groups of rows, consider using window functions.
The second feature is called a CTE, or Common Table Expression.
They are useful in SQL, especially when your queries get more complicated.
A CTE is a query that you define within another query.
You define one query and give it a name.
This is your CTE.
You can then write your main query, and refer to your first query by its name.
A CTE can help by making your query more readable, simplify your query, and you can use it like
a view within your query.
Let’s see an example.
Here’s an example of a query with a Common Table Expression.
Once again, I’m showing this in MySQL, but it works the same in many other databases.
A CTE is defined using the WITH clause at the top of the query.
We say WITH, and then we give a name to this query, called department_count.
We then have the word AS, then brackets.
Inside the brackets is the query that will be set to the name of department_count.
We have a SELECT query that selects a few columns from the department table.
After the brackets, we have our main SELECT query.
This is the query that is run and shows results.
We select some columns from tables, and can select from department_count.
It’s like this department_count CTE is a table or a view.
You can use it in your query here.
Now, this seems like a simple example, and not really that useful.
But if you have a long query, you can use a CTE to give a name to a complex subquery
so it’s easier to refer to in your main query.
You can also use a CTE for a subquery that’s used more than once, so you only have to define
it once and use it multiple times.
For more about CTEs, check out my recent YouTube video here that goes more in-depth on the
topic.
The third SQL feature I want to share is row limiting.
Row limiting is the ability to return a specific number of rows from a query.
A SELECT query has a WHERE clause that filters based on criteria, but there’s no guarantee
on how many rows are returned.
Sometimes you want to limit your results to an exact number of rows, such as 5 rows or
10 rows.
You can do this using the row limiting feature in SQL.
Let’s see an example.
Let’s say we have a simple table of customers and their order value, or the total value
of orders they have placed with our business.
We want to see the top 5 customers by order value.
We don’t know what the order values are, we just want to know the top 5 customers and
their order values.
In MySQL and PostgreSQL, we can do this by adding the LIMIT keyword to the end of the
query.
We put this after the ORDER BY keyword.
We specify LIMIT, then the number of rows.
So, to see the top 5 customers, we select the customer data from this table, order by
the order value, and specify LIMIT 5.
We will see the top 5 customers here.
In Oracle, we use a different keyword, called FETCH.
We add the words FETCH FIRST 5 ROWS ONLY to the end of the query, and it has the same
effect as LIMIT.
If we run this query, we’ll see the top 5 customers by the total order.
In SQL Server, it’s done a little differently again.
We add the word TOP after the SELECT keyword, then specify the number of rows we want to
see.
In this example, we want to see 5 rows, so we say Select Top 5, then our columns.
If we were to run this query, we’ll only see 5 rows.
This concept of row limiting can be helpful for reports, or showing a ranking of data,
or other similar features you may need to build into your application or system.
The fourth feature I’ll share is the ability to automatically increment column values.
This might be something you’re already familiar with, so if you are, then that’s good.
If not, I’ll explain what it is.
The ability to automatically increment column values is called “auto increment”, and
is a helpful feature for inserting records.
When you define a primary key on a table, you need to provide a unique value for it.
A great way to do this is to have the database generate unique values for you.
This is done using this built-in auto-increment feature.
The feature will generate a number and automatically increment the next number for you to use.
There’s no need to create a separate table to store the last-used number, or use stored
procedures to calculate and check values.
So, how do you do it?
It’s done a little differently in each database.
In Oracle, you can use either a sequence or an identity column.
To use a sequence, you create a new object called a sequence, like this.
Whenever you insert a new value, you refer to the NEXTVAL property of the sequence, like
this query here.
This will ensure the value inserted is unique.
The only problem with this approach is that if you use the INSERT ALL statement to insert
multiple records into one table, and use this NEXTVAL property, it uses the same value for
each row.
I don’t know a way around this except to avoid using INSERT ALL.
The other way is to use an identity column.
You can do this by adding some keywords to the column definition when you create your
table.
For this ID column here, we add the words GENERATED BY DEFAULT ON NULL AS IDENTITY.
This will define this as an identity column, and the values will be automatically generated.
In SQL Server, it’s done by adding the Identity keyword next to the column name when you create
the table.
Here’s an example.
You add in the data type next to the ID column, and then the Identity keyword.
You then specify brackets and two values inside the brackets.
The first value here is 1, and is the starting number of the column.
The second number is the increment for each new value, which I’ve also added as 1.
So the first row gets the value of 1, the second row gets 2, and so on.
In MySQL, we add the word AUTO_INCREMENT to the column definition.
It goes after the data type here.
There’s no need to specify the starting number or the increment.
Another thing to keep in mind is that there is an underscore between AUTO and INCREMENT.
I often forget this, and then get errors when I try to create my table.
Finally, in Postgres, there are three ways.
You can use a Serial data type for a column when you define it.
This example here shows a Create Table statement that uses the Serial data type for the primary
key.
This will cause the values to be generated automatically.
Another method is using a sequence.
It works in a similar way to Oracle.
You create a sequence using the Create Sequence command, like this example here.
You can specify the starting value and the increment value.
In this example we start at 1 and increment by 1.
The third option is to use an Identity column.
We can do this by adding the keywords GENERATED BY DEFAULT AS IDENTITY after the data type
when declaring a column in a table.
This means the new values are generated for this column automatically.
It works in a similar way to the Serial data type.
So, that’s how you can use an auto-increment value for a column in several different databases.
It’s a great way to populate values for primary key columns.
SQL feature number 5 is temporary tables.
A temporary table, or temp table, is a table that exists temporarily on your database.
They can be used for storing data that you only need for a short period during your session.
If you’re working with a set of data and you retrieve it from the database multiple
times, you could consider storing the results in a temp table the first time and getting
the results from that.
This means you can query the temp table and work with the data much easier and faster,
rather than getting the data from the source again, saving you time.
How can you create one?
In Oracle, you can create either a global temporary table or a private temporary table.
Global temp tables are like regular tables, but their data is private and the table is
removed at the end of the session.
All users can access the table but the data is unique to each user.
Private temp tables are only accessible to the current user and are also removed at the
end of the session.
To create a Global temp table, you run a Create Global Temporary Table command, which works
in the same way as a regular Create Table command.
Here’s an example of creating a global temp table.
At the end of the statement, you specify either On Commit Delete Rows or On Commit Preserve
Rows.
Delete rows means that when a commit is performed, the rows are deleted from the table.
Preserve rows means that when you commit, the rows are preserved in the table until
the end of the session.
Once this table is created, you can insert data into it and select from it just like
a regular table.
To create a private temp table, you need to use Create Private Temporary Table.
You also need to ensure your table name begins with ora$ptt.
If not, you’ll get an error.
Once you create the table, you can also work with it just like a global temp table or a
regular table.
In SQL Server, you can create either a local temp table or a global temp table.
To create a temp table, you can use either the Create statement or the Select Into statement.
Here’s how to use a Create statement.
You use the Create Table statement and specify the columns, just like a regular table.
The difference is that the table name begins with a hash character, which indicates it
is a local temporary table.
To create a global temporary table, you start your table name with two hash characters.
There is no Local or Global keyword that can be used.
Another way to create a temp table is to use the Insert Into statement.
This will create the table and insert data into it in the same command.
You specify Insert, then the table name, then the columns.
You then specify the Select statement to run to populate into the new temp table.
You can then work with the temp table just like a regular table, and it is removed at
the end of the session.
In MySQL and Postgres, you create a temporary table by simply adding the word Temporary
to the Create Table statement.
Here’s an example.
We can run this Create Temporary Table statement and a new temp table is created.
We can then work with this temp table in the same way as a regular table.
Feature number 6 I want to share is called bind variables.
Let’s say you have an application that runs an SQL query.
This query has a parameter, or something that you provide from the application to the query.
This is so the query can return data based on a certain value, such as a user or a customer
or some other input.
Here’s an example.
It filters data on the assigned to ID column.
But the ID value changes based on which user is in the application.
So you need to allow your query to use different values.
One way to do this is using string concatenation, like this example.
However, this is a poor way of doing it as it leaves you open to a risk of something
called SQL Injection.
The solution to this is to use something called a bind variable.
A bind variable is an SQL feature that lets you turn part of a query into a parameter.
You then provide the value for that parameter when you run the query.
This can be done without concatenating values in a query string or writing separate queries.
In concept, your query will look something like this.
Instead of writing a specific number or concatenating a variable, you have the bind variable here.
When you run the query, you’ll either be prompted for the variable if you run it in
an IDE, or you’ll need to provide it from your application code.
There are two main benefits of using bind variables: security and performance.
Security because it helps to avoid SQL injections, and performance because it can help improve
the performance of the query as the database doesn’t need to analyse each query separately.
I’ve written more about this in my guide to bind variables, which is linked in the
description.
The syntax to use a bind variable is different for each database.
🎯 Demo (18_bind_oracle_postgres.sql) in VS Code - query with bind variables with :
To add a bind variable to a query in Oracle or Postgres, you use a colon character followed
by the name of the bind variable.
In SQL Server, you use the @ symbol, followed by the name of the bind variable.
In MySQL, you use a question mark.
There’s no need to add a name for the bind variable.
The concept works the same, but the syntax is different in each database.
The final feature I want to share is called “pivot”.
It allows you to turn rows of data into columns.
It’s like creating a pivot table in Excel, if you’ve ever done that before.
It means you can show a summary of data where you have some values as rows, and others as
columns, and the intersection shows the calculation of each row and column.
So, if you ever need to create a Pivot-table style of output in SQL, it’s possible.
The way to do it in each database is different, but I’ll explain them briefly.
In Oracle, you use the PIVOT keyword and provide several inputs inside brackets.
Here’s an example using a table of data.
Inside the brackets after PIVOT, we specify the SUM of the sale amount column, then for
the customer_id column, and then the individual customer IDs to use as column headers.
Our output will look like this.
We have locations in the first column, customer IDs as column headers, and the sum of amounts
for the values.
I’ll share links to my guide on pivot tables in the description below so you can find out
more about pivoting data and how to do it.
In SQL Server, the example is a little longer.
You specify the first column, then the names of each of the other columns to see.
You then have a From clause that selects your source data.
You then add the Pivot keyword, and inside brackets you specify the function to use and
the column to use it on.
You also specify which column headers to use.
In this example, we’ve specified store locations of North, Central, South, and West.
You’ll get the output that looks like a pivot table.
In MySQL, there is no PIVOT keyword.
However, you can use the SUM function, or any other aggregate function, with the CASE
keyword.
Here’s an example.
For each column we want to show, we have a SUM function.
Inside the SUM function we have a case statement that returns a column to be summed, otherwise
it returns 0.
This is repeated but slightly different for each column we want to see.
The result is a pivot table-style output.
In Postgres, you use a crosstab function for this.
Here’s an example.
We select * from the crosstab function.
Inside the function, the parameter is our SQL query to get the data.
We then specify an alias, then inside the brackets, we specify the column headers to
show and the data types.
There’s more to know about pivot tables than these simple examples, but I just wanted
to share that they are possible and they are different in each database.
I hope at least one of those SQL features has given you a little “aha” moment, or
you’ve learned something about what can be done with SQL.
I only scratched the surface of these topics, so if you want to learn more about them and
see some examples, check out the links in the description below for guides I’ve written
or videos I’ve created on these features.
If you want to learn more about database design and SQL, visit my website at databasestar.com
If you liked this video, consider subscribing to my channel.
Thanks for watching.