How to Declare a Variable in SQL?

Variables in SQL are fundamental for building efficient and scalable database applications. They enhance the flexibility and efficiency of database queries by acting as placeholders for data. Understanding how to declare and use variables in SQL is crucial for writing dynamic and effective queries

In this article, we will explain 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.

1. Using the SET Command

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". The result contains a single column labeled NAME, displaying the value GFG in each row.

2. Using the WITH Clause

WITH clause in SQL allows the creation of Common Table Expressions (CTEs), which act as temporary result sets within a query. Variables can be simulated using CTEs.

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.

3. Using Temporary Variables to Declare Variable in SQL

Temporary tables can be used to store and manipulate data within a session. These tables exist only for the duration of the session and can be used to store and manipulate data temporarily.

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".

4. Using Subqueries

Subqueries allow embedding one query within another, effectively assigning values to columns dynamically.

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.

Conclusion

Understanding how to declare and use variables in SQL enables developers to write efficient, scalable, and dynamic queries. Whether using the SET command, WITH clause, temporary tables, or subqueries, each method offers unique advantages for handling data. By mastering these techniques, we can unlock the full potential of SQL in our database applications, paving the way for more flexible and robust database solutions.