SET vs SELECT in SQL

In SQL, the use of SET and SELECT commands is fundamental for manipulating data and variables within a database. Understanding the differences between these two commands is crucial for performing various operations efficiently. This article explores the concepts of SET and SELECT in SQL, delving into their functionalities, and syntax, and providing practical examples to illustrate their usage.

In this article, we look here the two famous and important topics of SQL, SET versus SELECT in SQL, using the syntax, method, and some working examples that will help easily follow the process.

SET and SELECT in SQL

In SQL, the two most important statements for assigning variables are SET and SELECT. SET is used to assign single values efficiently, while SELECT is used to retrieve data and assign multiple values.

SET

SET is primarily used for assigning values to variables. It’s commonly employed within stored procedures, scripts, or batches to store a single scalar value.

Syntax:

SET @variable_name = expression;

SELECT

SELECT, on the other hand, serves a broader purpose. It retrieves data from one or more tables, views, or expressions, and it is not limited to assigning values to variables. It is a versatile command used for querying and returning result sets.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE condition;

Example of SET vs SELECT in SQL

Example 1: Using SET to Assign a Variable

-- Create Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Status VARCHAR(50)
);

-- Insert Sample Data into Orders Table
INSERT INTO Orders (OrderID, Status) VALUES
(1, 'Shipped'),
(2, 'Processing'),
(3, 'Shipped'),
(4, 'Delivered');

-- Declare and Set a Variable
DECLARE @totalOrders INT;
SET @totalOrders = 0;

-- Update Variable Using SET
SET @totalOrders = @totalOrders + (SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped');

-- Display the Result
SELECT @totalOrders AS TotalShippedOrders;

Output:

TotalShippedOrders
——————-
2

Explanation:

The output of the script is TotalShippedOrders: 2. It declares a variable @totalOrders, sets it to 0, and updates it by counting the ‘Shipped‘ orders from the “Orders” table. The final result reflects the total count of orders with a ‘Shipped‘ status.

Example 2: Using SELECT to Retrieve Data

-- Create Employees Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Insert Sample Data into Employees Table
INSERT INTO Employees (EmployeeID, EmployeeName, Department, Salary) VALUES
(1, 'John Doe', 'IT', 60000.00),
(2, 'Jane Smith', 'HR', 55000.00),
(3, 'Robert Johnson', 'Finance', 70000.00),
(4, 'Emily White', 'Marketing', 48000.00);

-- Retrieve Employee Details with SELECT
SELECT EmployeeID, EmployeeName, Department
FROM Employees
WHERE Salary > 50000;

Output:

EmployeeID	EmployeeName	Department
———–	——————	———–
1	John Doe	IT
3	Robert Johnson	Finance

Explanation:

This SELECT query retrieves employee details such as EmployeeID, EmployeeName, and Department for those employees whose salary is greater than 50000.

Conclusion

So, Overall the choice between SET and SELECT in SQL depends on the task at hand. Use SET when dealing with scalar values, especially for variable assignments within scripts or stored procedures. On the other hand, SELECT is the go-to command for querying and retrieving data from tables or views, offering a more comprehensive approach when dealing with result sets. Understanding the nuances of SET and SELECT enables SQL developers to apply the appropriate command based on the requirements, contributing to effective data manipulation and variable handling within a database environment.