[ Home ] [ Up ] [ DML Statements ] [ DDL Statements ] [ Transaction Statements ]

Data Manipulation Language

Categories of DML Statements

Here is a summary of the categories of SQL statements covered in this section:

• If you wish to read data from tables, you use a SELECT statement
• To enter one or more new rows of data into a table, you use an INSERT statement
• To remove one or more rows of data from a table, you use a DELETE statement
• To change one or more rows of data in a table, you use an UPDATE statement

SELECT Statements

The most commonly used statement in SQL is the SELECT statement. It is used to read data from one or more tables.

SELECT Statements vs Query Expressions

There are actually two contexts in which a SELECT may be executed:

• To initiate a SELECT statement
• To define or return a table-valued expression (often called a query expression)

The difference is that a table-valued expression has no concept of ordering, while a SELECT statement may return rows in a specified order (specified in an ORDER BY clause). If no ORDER BY clause is specified, then the results of a SELECT statement may be returned in any order.

Simple SELECT Statements

Here is an example of the simplest kind of SELECT statement:

SELECT name 
FROM employees;

which, when given the table:

would give the results:

Alfred J Prufrock
Angela Parfitt
Michael Solistes
Charles Schultz
Barbara Smith
...

Note that the result of this SELECT statement is a table-valued expression, which conforms to the same rules as a table, so I could represent this as a table.

Note: SQL is a language case insensitive language. Thus, SELECT, select, Select, and seLect are all equivalent. However, for the purposes of distinguishing between SQL keywords and user-defined names, it is often useful to use a convention. Here, I use the convention that SQL keywords are upcased, while user-defined names are lowercased.

Specifying Multiple Columns

We could form a slightly more complex SELECT statement by returning more than one column from the table:

SELECT name, age, salary 
FROM employees;

which produces the following table-valued expression:

Specifying the Order of Rows Returned by a SELECT Statement

If we are executing a SELECT statement (as opposed to a query expression), we may specify the order in which the rows are returned:

SELECT name, age, salary 
FROM employees
ORDER BY salary;

this will result in the same set of rows being returned as in the above example, except that the order in which those rows are returned is based on the values in the row's Salary column.

Note that it is possible to specify the order even if the ordered column is not returned:

SELECT name, age 
FROM employees
ORDER BY salary;

The WHERE clause

If you wish to filter the results of a SELECT statement by some criteria (called predicates), you can specify those predicates in a WHERE clause:

SELECT name, age, salary 
FROM employees
WHERE salary > 100000;

You may use a WHERE clause in a SELECT statement and also in a query expression.

In a SELECT statement, you can combine the WHERE and ORDER BY clauses:

SELECT name, age, salary 
FROM employees
WHERE salary > 100000
ORDER BY salary;

Using Multiple Predicates

You may combine predicates in a WHERE clause using AND, OR and NOT operators:

SELECT name, age, salary 
FROM employees
WHERE salary > 100000
  AND age < 50;

Performing Joins

If you wish to perform a join across one or more tables, you can list more than one table in the FROM clause:

SELECT employees.name, departments.name, age, salary
FROM employees, departments;

(Note that because both the Employees table and Departments table have a column called Name, we have to explicitly specify which one we want by explicit qualification.)

However, if you do not use a WHERE clause, this produces a table-valued expression with a very large cardinality (row count), because it matches every row in the employees table with every row in the departments table. So, if you have, say, 1000 employees and a 100 departments, this SELECT would return 1000 * 100 = 100,000 rows. (This is called the cartesian product of the two tables.) Rarely is this useful, and it consumes large amounts of resources to compute the result!

Instead, when you use multiple tables in a SELECT, you almost always should also use a WHERE clause to restrict the number of rows returned.

For example, if you want to list all employees names, followed by the name of their department, followed by their age and salary, you would use the following SELECT statement:

SELECT employees.name, departments.name, age, salary 
FROM employees, departments
WHERE department = departmentID;

This kind of join is called a natural equi-join, because it selects rows from the two tables that have equal values in the relevant columns.

Other Kinds of Joins

There are various other kinds of joins that can be accomplished using a SELECT statement, such as outer joins and unions, but these are more advanced features that we won't cover in this introduction.

Take a look at the references noted earlier for more details.

INSERT Statements

If you wish to enter new rows of data into a table, you use an INSERT statement. For example:

INSERT INTO employees
(employeeID, name, department, age, salary)
VALUES (234134, 'Frodo Benitas', 20, 23, 34000);

will enter a new employee into the Employees table. In this example, each column name is specified explicitly by name in the first list. However, if you wish to enter a value for every column, and rely on the "natural order" of the columns in the table, you may omit the first list entirely:

INSERT INTO employees
VALUES (234134, 'Frodo Benitas', 20, 23, 34000);

However, this requires you to know the order of the column names, and to supply a value for every column in the row. Sometimes, you don't know the value for a column, and would prefer to leave it blank. If you do this:

INSERT INTO employees
(employeeID, name, department, salary)
VALUES (234134, 'Frodo Benitas', 20, 34000);

where the age was omitted, then a null value will be placed in the age column for that row. However, it would be clearer if you were explicit about the assignment of a null value:

INSERT INTO employees
(employeeID, name, department, age, salary)
VALUES (234134, 'Frodo Benitas', 20, NULL, 34000);

DELETE Statements

To remove one or more rows of data from a table, you use the DELETE statement. For example:

DELETE FROM employees
WHERE name = 'Frodo Benitas';

will remove the row for Frodo Benitas from the Employees table. If there is more than one employee called Frodo Benitas, it will delete the others, also.

Beware! The statement:

DELETE FROM employees;

will cause all rows in the Employees table to be deleted!

UPDATE Statements

To change the data in one or more rows of a table, use the UPDATE statement. For example:

UPDATE employees
SET name = 'Fred Benitas',
    age = 34
WHERE employeeID = 234134;

will change the name and age of an existing row in the Employees table. Presumably, the employeeID is unique; if it is not, then all employees with employeeID 234134 will have their name and age changed.