Chapter 8: Speaking SQL

people in your own department on a day-to-day basis. In exactly the same way, you could always refer to a column in a database using the Table.Column form, but it’s only necessary when there’s the potential for confusion.

As for the join itself, the code is fairly clear: we’re joining the Departments table and the Employees table into a single, virtual table by matching the values in the

Departments.DepartmentID column with those in the Employees.DepartmentID column. From this virtual table, we’re only interested in the names of the employees whose records match the filter Departments.Department LIKE '%Engineering'.

By eliminating the WHERE clause and adding the department’s name to the column list, we could generate a list that contained all the employees and their associated departments. Try this query:

SELECT Employees.Name, Departments.Department

FROM Departments

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

The results are as you’d expect:

Name	Department
--------------------------------	---------------------------------
Zak Ruvalcaba	Executive
Jessica Ruvalcaba	Marketing
Ted Lindsey	Engineering
Shane Weebe	Engineering
David Levinson	Marketing
Geoff Kim	Accounting
(6 row(s) affected)

Expressions and Operators

In the wonderful world of programming, an expression is any piece of code that, once evaluated, results in a value. For instance, 1 + 1 is a very simple expression. In SQL, expressions work in much the same way, though they don’t necessarily have to be mathematical. For a simple example, let’s create a list that contains employees and their cities as single strings. Try this query:

SELECT EmployeeID, Name + ', ' + City AS NameAndCity

FROM Employees

310

Expressions and Operators

The results are shown below:

EmployeeID NameAndCity

---------- -------------------------------------------------------

1Zak Ruvalcaba, San Diego

2Jessica Ruvalcaba, San Diego

3Ted Lindsey, San Diego

4Shane Weebe, San Diego

5David Levinson, San Diego

6Geoff Kim, San Diego

(6 row(s) affected)

Note that the results of the expression are used to create a virtual column. This column doesn’t exist in reality, but is calculated using the values of other columns. We give this column the name NameAndCity using the AS keyword.

Expressions would be quite useless if we didn’t have operators. Over the course of the previous sections, you’ve seen the operators =, AND, >=, <=, LIKE and IN at work. The following is a list of operators that you’ll need to know to use SQL effectively.

+

The addition operator adds two numbers or combines two strings.

–

The subtraction operator subtracts one number from another.

*

The multiplication operator multiplies one number with another.

/

The division operator divides one number by another.

>

The greater-than operator is used in WHERE clauses to determine whether the first value is greater than the second. For example, the following query would return all the records from the table whose EmployeeID is greater than ten (i.e. 11 and up).

SELECT Name

FROM Employees

WHERE EmployeeID > 10

311

Chapter 8: Speaking SQL

<

The less-than operator is used in WHERE clauses to determine whether the first value is less than the second. The result of the following query would return from the table all records whose EmployeeID is less than ten (i.e. nine and lower).

SELECT Name

FROM Employees

WHERE EmployeeID < 10

>=

The greater-than or equal-to operator is used in WHERE clauses to determine whether the first value is greater than, or equal to, the second. The following query would return the record with EmployeeID of ten, and every one after that.

SELECT Name

FROM Employees

WHERE EmployeeID >= 10

<=

The less-than or equal-to operator is used in WHERE clauses to determine whether the first value is less than, or equal to, the second. The result of the following query would be the record with EmployeeID of ten, and every one before that.

SELECT Name

FROM Employees

WHERE EmployeeID <= 10

<>, !=

This operator is used to check whether a value is not equal to a second.

OR

This operator is used with the WHERE clause in the SELECT statement. The OR operator can be used when a certain condition needs to be met, or when only one of two conditions needs to be met. For example, the following query’s results would return the employees with employee IDs of 1 or 2.

SELECT Name

FROM Employees

WHERE EmployeeID = 1 OR EmployeeID = 2

312