How to Declare a Variable in SQL?

Variables in SQL are a fundamental step towards building efficient and powerful database applications. It enhances the flexibility and efficiency of our database queries.

Understanding how to declare variables in SQL is very important for writing scalable code. Variables act as placeholders for data, which enable us to manipulate and store information within our SQL programs.

Here, we’ll explore the various methods and best practices for declaring variables in SQL, along with the syntax and examples, that help us write more dynamic and effective queries.

Variables act as containers for values and enable various operations on the stored data. SQL provides several methods to declare and use variables, including the use of the WITH clause, SET command enhances, and various methods.

The methods to declare variables in SQL are:

1. Using SET Command
2. Using WITH Clause
3. Using Scalar Subqueries,
4. Using Temporary Tables

Although SQL does not have a built-in SET command like some other database systems, we can use variable assignment using user-defined functions.

Query:

SELECT  "GFG" AS NAME

Output:

Explanation: The query selects the string “GFG” and renames it as “NAME“. Consequently, the output consists of a single column labeled “NAME” containing the string “GFG” in each row.

The WITH clause in SQL that allows defining temporary result sets within a query. Variables can be declared and assigned values within the WITH clause.

Query:

WITH Customers AS (
    SELECT "GFG" AS name
)
SELECT * FROM Customers;

Output:

Explanation: The output shows a virtual table named “Customers” created using a Common Table Expression (CTE). It consists of a single column labeled “name”, containing the value “GFG“. The SELECT statement retrieves all rows from this “Customers” table, displaying the single row with “GFG” as the name.

Temporary tables can be used to store and manipulate data within a session. We can take advantage of temporary tables to mimic variable declaration.

Query:

CREATE TEMP TABLE Temp_Table (num INTEGER,name STRING);
INSERT INTO Temp_Table VALUES (23,"GFG");
SELECT num,name FROM Temp_Table;

Output:

Explanation: The output displays a temporary table named “Temp_Table” created with two columns: “num” of type INTEGER and “name” of type STRING. One row is inserted with values (23, “GFG”). The subsequent SELECT statement retrieves and displays the values from the “num” and “name” columns of “Temp_Table“.

Subqueries in SQL are used for embedding queries within queries.

Query:

SELECT (SELECT 23) AS num;

Output:

Explanation: The output simply returns the value 23 as “num” using a subquery within the SELECT statement. It assigns the result of the inner query directly to the column “num” in the outer query’s result set.