Header

Variables

Introduction

It happens that you repeatedly reuse the same value in a query.

In that case, it can be useful to define this value in one place.

For this, you can use variables.

We will look at an example here.

1. Example of using a variable

We will work with an example.

In this example, we write a query that does the following:

Retrieve details of actors.
Those actors whose first letters of the first name match the most common first letters of names of film roles.

1.1. Query without variable

For this, we can use the following query:

SELECT
  id,
  first_name,
  last_name
FROM
  actors
WHERE
  LEFT(first_name, 2) = (
    SELECT
      most_used_character.left
    FROM
      (
        SELECT
          LEFT(role, 2),
          COUNT(role)
        FROM
          roles
        GROUP BY
          LEFT(role, 2)
        ORDER BY
          COUNT(role) DESC
        LIMIT
          1
      ) as most_used_character
  );

id	first_name	last_name
241542	Hiroshi (I)	Kawashima
319868	Hikaru	Midorikawa
672860	Hiroko	Kawasaki

Columns: 3
Rows: 3

1.2. Problem statement

You see in the query that we mention the number of letters (2) in three places.

If we want to change the number of letters, it must be done in three places.

That's not convenient.

By using a variable, we only need to do this in one place.

1.3. Solution with variable

In the query below, we rewrite the code.

Here, we use a variable.

WITH variables AS (
  SELECT
    2 AS count_characters
)
SELECT
  id,
  first_name,
  last_name
FROM
  actors,
  variables
WHERE
  LEFT(first_name, variables.count_characters) = (
    SELECT
      most_used_character.left
    FROM
      (
        SELECT
          LEFT(role, variables.count_characters),
          COUNT(role)
        FROM
          roles
        GROUP BY
          LEFT(role, variables.count_characters)
        ORDER BY
          COUNT(role) DESC
        LIMIT
          1
      ) as most_used_character
  );

id	first_name	last_name
241542	Hiroshi (I)	Kawashima
319868	Hikaru	Midorikawa
672860	Hiroko	Kawasaki

Columns: 3
Rows: 3

We now mention the number 2 only in one place.

2. Syntax variables

Within different SQL dialects, there are differences in the use of variables.

We look at some differences.

2.1. PostgreSQL

In this training, we use the PostgreSQL dialect.

There, you cannot directly create and use a variable from a query.

With the example below, we have solved this:

WITH <variables> AS (
  SELECT
    <value> AS <variable_name>
)

Then you retrieve the variables from FROM:

FROM
  <table_name>,
  <variables>

And you can use the variable in your query, for example:

WHERE
  <column_name> = <variables>.<variable_name>

2.2. Other dialects

In other dialects, such as MS SQL Server, you can create variables more explicitly.

You do this as follows:

DECLARE @<variable_name> AS <data_type> = <value>

Summary

With a variable, you can reuse a value repeatedly.
There are differences for the different SQL dialects.