4

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?

8

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

  • 1
    +1 Hints in other RDBMS will also lock rows. – gbn Sep 12 '11 at 4:46
8

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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