Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data.

As far as I know the SELECT statement can only be used to query data, not change it. (Not taking INSERT ... SELECT into account here.)

So is SELECT part of DML and if so, why?

share|improve this question
add comment

2 Answers 2

up vote 7 down vote accepted

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows:

The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it manipulates (i.e. modifies) data. In common practice though, this distinction is not made and SELECT is widely considered to be part of DML.

As an example of the lack of distinction, the Oracle 11.2 Concepts Guide includes SELECTS as DML as follows:

Data manipulation language (DML) statements query or manipulate data in existing schema objects. Whereas DDL statements enable you to change the structure of the database, DML statements enable you to query or change the contents.

In the strictest sense a SELECT can do more than just query data. At least in Oracle there is the for_update_clause that “lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction.” (From the SQL Lanugage Reference).

share|improve this answer
1  
+1 Hints in other RDBMS will also lock rows. –  gbn Sep 12 '11 at 4:46
add comment

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc

You just don't change the database state when you do.

It boils down to whether you read "manipulate" to include "change state"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.