Recently, my attention was drawn to a question which is often asked. Here, I am going to explain why select is called DML (Data Manipulation Language).
First of all, do not confuse the word with "Manipulation". We are assuming “manipulation equals to insert, update and delete”. No guys, it is not true. We cannot say that the SELECT statements are used for only retrieving the data, it is used to manipulate the data.
The word “Manipulation” means to play with the data, not only retrieve or fetch the data. We can include retrieve, store, modify, delete, insert, update, and copy operations in DML.
Data manipulation can be,
- Data aggregation
- Data Grouping
- Data Ordering like ascending or descending
- Performing mathematical operations
- Data conversion or type conversion
- Pattern matching with regex
- Condition retrieving of data
- Working with CTE
Other than the above list, there are many other operations which can be included in the DML list.
Let me explain how a SELECT statement can change the data of database tables...
Example 1
- SELECT * INTO tb_Table2 FROM tb_Table1
The above statement will copy all the records and columns from tb_Table1 to tb_Table2. It is a kind of manipulation.
Example 2
- DECLARE @str AS VARCHAR(MAX)
- SELECT @str = CONCAT(@str, StudentID, ',') FROM Students
- SELECT @str AS CommaSaperatedStudentIDs
This query will fetch and append all the comma separated StudentIDs in @str variable. We did string manipulation.
That's it. We learned why SELECT is called DML statement in SQL. Comment for reporting any mistake, change, or your valuable suggestions.
Thanks for reading. Keep Learning - Keep Writing.