Cover | Table of Contents

Table of Contents

Chapter 1: Retrieving Records

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter focuses on very basic SELECT statements. It is important to have a solid understanding of the basics as many of the topics covered here are not only present in more difficult recipes but also are found in everyday SQL.

You have a table and want to see all of the data in it.

Use the special "*" character and issue a SELECT against the table:

	1 select *
	2   from emp

The character "*" has special meaning in SQL. Using it will return every column for the table specified. Since there is no WHERE clause specified, every row will be returned as well. The alternative would be to list each column individually:

	select empno,ename,job,sal,mgr,hiredate,comm,deptno
	  from emp

In ad hoc queries that you execute interactively, it's easier to use SELECT *. However, when writing program code it's better to specify each column individually. The performance will be the same, but by being explicit you will always know what columns you are returning from the query. Likewise, such queries are easier to understand by people other than yourself (who may or may not know all the columns in the tables in the query).

You have a table and want to see only rows that satisfy a specific condition.

Use the WHERE clause to specify which rows to keep. For example, to view all employees assigned to department number 10:

	1 select *
	2   from emp
	3  where deptno = 10

The WHERE clause allows you to retrieve only rows you are interested in. If the expression in the WHERE clause is true for any row, then that row is returned.

Most vendors support common operators such as: =, <, >, <=, >=, !, <>. Additionally, you may want rows that satisfy multiple conditions; this can be done by specifying AND, OR, and parenthesis, as shown in the next recipe.

You want to return rows that satisfy multiple conditions.

Use the WHERE clause along with the OR and AND clauses. For example, if you would like to find all the employees in department 10, along with any employees who earn a commission, along with any employees in department 20 who earn at most $2000:

	1 select *
	2   from emp
	3  where deptno = 10
	4     or comm is not null
	5     or sal <= 2000 and deptno=20

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Retrieving All Rows and Columns from a Table

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have a table and want to see all of the data in it.

Use the special "*" character and issue a SELECT against the table:

	1 select *
	2   from emp

The character "*" has special meaning in SQL. Using it will return every column for the table specified. Since there is no WHERE clause specified, every row will be returned as well. The alternative would be to list each column individually:

	select empno,ename,job,sal,mgr,hiredate,comm,deptno
	  from emp

In ad hoc queries that you execute interactively, it's easier to use SELECT *. However, when writing program code it's better to specify each column individually. The performance will be the same, but by being explicit you will always know what columns you are returning from the query. Likewise, such queries are easier to understand by people other than yourself (who may or may not know all the columns in the tables in the query).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Retrieving a Subset of Rows from a Table

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have a table and want to see only rows that satisfy a specific condition.

Use the WHERE clause to specify which rows to keep. For example, to view all employees assigned to department number 10:

	1 select *
	2   from emp
	3  where deptno = 10

The WHERE clause allows you to retrieve only rows you are interested in. If the expression in the WHERE clause is true for any row, then that row is returned.

Most vendors support common operators such as: =, <, >, <=, >=, !, <>. Additionally, you may want rows that satisfy multiple conditions; this can be done by specifying AND, OR, and parenthesis, as shown in the next recipe.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Finding Rows That Satisfy Multiple Conditions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to return rows that satisfy multiple conditions.

Use the WHERE clause along with the OR and AND clauses. For example, if you would like to find all the employees in department 10, along with any employees who earn a commission, along with any employees in department 20 who earn at most $2000:

	1 select *
	2   from emp
	3  where deptno = 10
	4     or comm is not null
	5     or sal <= 2000 and deptno=20

You can use a combination of AND, OR, and parenthesis to return rows that satisfy multiple conditions. In the solution example, the WHERE clause finds rows such that:

• the DEPTNO is 10, or
• the COMM is NULL, or
• the salary is $2000 or less for any employee in DEPTNO 20.

The presence of parentheses causes conditions within them to be evaluated together.

For example, consider how the result set changes if the query was written with the parentheses as shown below:

	select *
	 from emp
	where (     deptno = 10
	        or comm is not null
	        or sal <= 2000
	      )
	  and deptno=20

	EMPNO ENAME  JOB     MGR  HIREDATE      SAL       COMM  DEPTNO
	----- ------ ----- -----  ----------- ----- ----------  ------
	 7369 SMITH  CLERK  7902  17-DEC-1980   800                 20
	 7876 ADAMS  CLERK  7788  12-JAN-1983  1100                 20

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Retrieving a Subset of Columns from a Table

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have a table and want to see values for specific columns rather than for all the columns.

Specify the columns you are interested in. For example, to see only name, department number, and salary for employees:

	1 select ename,deptno,sal
	2   from emp

By specifying the columns in the SELECT clause, you ensure that no extraneous data is returned. This can be especially important when retrieving data across a network, as it avoids the waste of time inherent in retrieving data that you do not need.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Providing Meaningful Names for Columns

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You would like to change the names of the columns that are returned by your query so they are more readable and understandable. Consider this query that returns the salaries and commissions for each employee:

	1 select sal,comm
	2   from emp

What's "sal"? Is it short for "sale"? Is it someone's name? What's "comm"? Is it communication? You want the results to have more meaningful labels.

To change the names of your query results use the AS keyword in the form: original_name AS new_name. Some databases do not require AS, but all accept it:

	1 select sal as salary, comm as commission
	2   from emp

	SALARY   COMMISSION
	-------  ----------
	    800
	   1600         300
	   1250         500
	   2975
	   1250        1300
	   2850
	   2450
	   3000
	   5000
	   1500           0
	   1100
	    950
	   3000
	   1300

Using the AS keyword to give new names to columns returned by your query is known as aliasing those columns. The new names that you give are known as aliases. Creating good aliases can go a long way toward making a query and its results understandable to others.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Referencing an Aliased Column in the WHERE Clause

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have used aliases to provide more meaningful column names for your result set and would like to exclude some of the rows using the WHERE clause. However, your attempt to reference alias names in the WHERE clause fails:

	select sal as salary, comm as commission
	  from emp
	 where salary < 5000

By wrapping your query as an inline view you can reference the aliased columns:

	1 select *
	2   from (
	3 select sal as salary, comm as commission
	4   from emp
	5        ) x
	6  where salary < 5000

In this simple example, you can avoid the inline view and reference COMM or SAL directly in the WHERE clause to achieve the same result. This solution introduces you to what you would need to do when attempting to reference any of the following in a WHERE clause:

• Aggregate functions
• Scalar subqueries
• Windowing functions
• Aliases

Placing your query, the one giving aliases, in an inline view gives you the ability to reference the aliased columns in your outer query. Why do you need to do this? The WHERE clause is evaluated before the SELECT, thus, SALARY and COMMISSION do not yet exist when the "Problem" query's WHERE clause is evaluated. Those aliases are not applied until after the WHERE clause processing is complete. However, the FROM clause is evaluated before the WHERE. By placing the original query in a FROM clause, the results from that query are generated before the outermost WHERE clause, and your outermost WHERE clause "sees" the alias names. This technique is particularly useful when the columns in a table are not named particularly well.

The inline view in this solution is aliased X. Not all databases require an inline view to be explicitly aliased, but some do. All of them accept it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Concatenating Column Values

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to return values in multiple columns as one column. For example, you would like to produce this result set from a query against the EMP table:

	CLARK WORKS AS A MANAGER
	KING WORKS AS A PRESIDENT
	MILLER WORKS AS A CLERK

However, the data that you need to generate this result set comes from two different columns, the ENAME and JOB columns in the EMP table:

	select ename, job
	  from emp
	  where deptno = 10

	 ENAME      JOB
	 ---------- ---------
	 CLARK      MANAGER
	 KING       PRESIDENT
	 MILLER     CLERK

Find and use the built-in function provided by your DBMS to concatenate values from multiple columns.

DB2, Oracle, PostgreSQL

These databases use the double vertical bar as the concatenation operator:

	1 select ename||' WORKS AS A '||job as msg
	2   from emp
	3  where deptno=10

MySQL

This database supports a function called CONCAT:

	1 select concat(ename, ' WORKS AS A ',job) as msg
	2   from
	3  where deptno=10

SQL Server

Use the "+" operator for concatenation:

	1 select ename + ' WORKS AS A ' + job as msg
	2   from emp
	3  where deptno=10

Use the CONCAT function to concatenate values from multiple columns. The || is a shortcut for the CONCAT function in DB2, Oracle, and PostgreSQL, while + is the shortcut for SQL Server.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Conditional Logic in a SELECT Statement

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to perform IF-ELSE operations on values in your SELECT statement. For example, you would like to produce a result set such that, if an employee is paid $2000 or less, a message of "UNDERPAID" is returned, if an employee is paid $4000 or more, a message of "OVERPAID" is returned, if they make somewhere in between, then "OK" is returned. The result set should look like this:

	ENAME             SAL  STATUS
	---------- ----------  ---------
	SMITH             800  UNDERPAID
	ALLEN            1600  UNDERPAID
	WARD             1250  UNDERPAID
	JONES            2975  OK
	MARTIN           1250  UNDERPAID
	BLAKE            2850  OK
	CLARK            2450  OK
	SCOTT            3000  OK
	KING             5000  OVERPAID
	TURNER           1500  UNDERPAID
	ADAMS            1100  UNDERPAID
	JAMES             950  UNDERPAID
	FORD            3000   OK
	MILLER          1300   UNDERPAID

Use the CASE expression to perform conditional logic directly in your SELECT statement:

	1 select ename,sal,
	2        case when sal <= 2000 then 'UNDERPAID'
	3             when sal >= 4000 then 'OVERPAID'
	4             else 'OK'
	5        end as status
	6   from emp

The CASE expression allows you to perform condition logic on values returned by a query. You can provide an alias for a CASE expression to return a more readable result set. In the solution, you'll see the alias STATUS given to the result of the CASE expression. The ELSE clause is optional. Omit the ELSE, and the CASE expression will return NULL for any row that does not satisfy the test condition.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Limiting the Number of Rows Returned

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to limit the number of rows returned in your query. You are not concerned with order; any n rows will do.

Use the built-in function provided by your database to control the number of rows returned.

DB2

In DB2 use the FETCH FIRST clause:

	1 select *
	2   from emp fetch first 5 rows only

MySQL and PostgreSQL

Do the same thing in MySQL and PostgreSQL using LIMIT:

	1 select *
	2   from emp limit 5

Oracle

In Oracle, place a restriction on the number of rows returned by restricting ROWNUM in the WHERE clause:

	1 select *
	2   from emp
	3  where rownum <= 5

SQL Server

Use the TOP keyword to restrict the number of rows returned:

	1 select top 5 *
	2   from emp

Many vendors provide clauses such as FETCH FIRST and LIMIT that let you specify the number of rows to be returned from a query. Oracle is different, in that you must make use of a function called ROWNUM that returns a number for each row returned (an increasing value starting from 1).

Here is what happens when you use ROWNUM <= 5 to return the first five rows:

1. Oracle executes your query.
2. Oracle fetches the first row and calls it row number 1.
3. Have we gotten past row number 5 yet? If no, then Oracle returns the row, because it meets the criteria of being numbered less than or equal to 5. If yes, then Oracle does not return the row.
4. Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5. Go to step 3.

As this process shows, values from Oracle's ROWNUM are assigned after each row is fetched. This is a very important and key point. Many Oracle developers attempt to return only, say, the fifth row returned by a query by specifying ROWNUM = 5.

Using an equality condition in conjunction with ROWNUM is a bad idea. Here is what happens when you try to return, say, the fifth row using ROWNUM = 5:

1. Oracle executes your query.
2. Oracle fetches the first row and calls it row number 1.
3. Have we gotten to row number 5 yet? If no, then Oracle discards the row, because it doesn't meet the criteria. If yes, then Oracle returns the row. But the answer will never be yes!
4. Oracle fetches the next row and calls it row number 1. This is because the first row to be returned from the query must be numbered as 1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Returning Random Records from a Table

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to return a specific number of random records from a table. You want to modify the following statement such that successive executions will produce a different set of five rows:

	select ename, job
	  from emp

Take any built-in function supported by your DBMS for returning random values. Use that function in an ORDER BY clause to sort rows randomly. Then, use the previous recipe's technique to limit the number of randomly sorted rows to return.

DB2

Use the built-in function RAND in conjunction with ORDER BY and FETCH:

	1 select ename,job
	2   from emp
	3  order by rand() fetch first 5 rows only

MySQL

Use the built-in RAND function in conjunction with LIMIT and ORDER BY:

	1 select ename,job
	2   from emp
	3  order by rand() limit 5

PostgreSQL

Use the built-in RANDOM function in conjunction with LIMIT and ORDER BY:

	1 select ename,job
	2   from emp
	3  order byrandom() limit 5

Oracle

Use the built-in function VALUE, found in the built-in package DBMS_RANDOM, in conjunction with ORDER BY and the built-in function ROWNUM:

	1 select *
	2   from (
	3  select ename, job
	4    from emp
	6   order by dbms_random.value()
	7        )
	8   where rownum <= 5

SQL Server

Use the built-in function NEWID in conjunction with TOP and ORDER BY to return a random result set:

	1 select top 5 ename,job
	2   from emp
	3  order by newid()

The ORDER BY clause can accept a function's return value and use it to change the order of the result set. The solution queries all restrict the number of rows to return after the function in the ORDER BY clause is executed. Non-Oracle users may find it helpful to look at the Oracle solution as it shows (conceptually) what is happening under the covers of the other solutions.

It is important that you don't confuse using a function in the ORDER BY clause with using a numeric constant. When specifying a numeric constant in the ORDER BY clause, you are requesting that the sort be done according the column in that ordinal position in the SELECT list. When you specify a function in the ORDER BY clause, the sort is performed on the result from the function as it is evaluated for each row.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Finding Null Values

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to find all rows that are null for a particular column.

To determine whether a value is null, you must use IS NULL:

	1 select *
	2   from emp
	3  where comm is null

NULL is never equal/not equal to anything, not even itself, therefore you cannot use = or != for testing whether a column is NULL. To determine whether or not a row has NULL values you must use IS NULL. You can also use IS NOT NULL to find rows without a null in a given column.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Transforming Nulls into Real Values

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have rows that contain nulls and would like to return non-null values in place of those nulls.

Use the function COALESCE to substitute real values for nulls:

	1 select coalesce(comm,0)
	2   from emp

The COALESCE function takes one or more values as arguments. The function returns the first non-null value in the list. In the solution, the value of COMM is returned whenever COMM is not null. Otherwise, a zero is returned.

When working with nulls, it's best to take advantage of the built-in functionality provided by your DBMS; in many cases you'll find several functions work equally as well for this task. COALESCE happens to work for all DBMSs. Additionally, CASE can be used for all DBMSs as well:

	select case
	       when comm is null then 0
	       else comm
	       end
	  from emp

While you can use CASE to translate nulls into values, you can see that it's much easier and more succinct to use COALESCE.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Searching for Patterns

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to return rows that match a particular substring or pattern. Consider the following query and result set:

	select ename, job
	  from emp
	 where deptno in (10,20)

	ENAME       JOB
	----------  ---------
	SMITH       CLERK
	JONES       MANAGER
	CLARK       MANAGER
	SCOTT       ANALYST
	KING        PRESIDENT
	ADAMS       CLERK
	FORD        ANALYST
	MILLER      CLERK

Of the employees in departments 10 and 20, you want to return only those that have either an "I" somewhere in their name or a job title ending with "ER":

	ENAME       JOB
	----------  ---------
	SMITH       CLERK
	JONES       MANAGER
	CLARK       MANAGER
	KING        PRESIDENT
	MILLER      CLERK

Use the LIKE operator in conjunction with the SQL wildcard operator ("%"):

	1 select ename, job
	2   from emp
	3  where deptno in (10,20)
	4    and (ename like '%I%' or job like '%ER')

When used in a LIKE pattern-match operation, the percent ("%") operator matches any sequence of characters. Most SQL implementations also provide the underscore ("_") operator to match a single character. By enclosing the search pattern "I" with "%" operators, any string that contains an "I" (at any position) will be returned. If you do not enclose the search pattern with "%", then where you place the operator will affect the results of the query. For example, to find job titles that end in "ER", prefix the "%" operator to "ER"; if the requirement is to search for all job titles beginning with "ER", then append the "%" operator to "ER".

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Sorting Query Results

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter focuses on customizing how your query results look. By understanding how you can control and modify your result sets, you can provide more readable and meaningful data.

You want to display the names, job, and salaries of employees in department 10 in order based on their salary (from lowest to highest). You want to return the following result set:

	ENAME       JOB               SAL
	----------  ---------  ----------
	MILLER      CLERK            1300
	CLARK       MANAGER          2450
	KING        PRESIDENT        5000

Use the ORDER BY clause:

	1 select ename,job,sal
	2   from emp
	3  where deptno = 10
	4  order by sal asc

The ORDER BY clause allows you to order the rows of your result set. The solution sorts the rows based on SAL in ascending order. By default, ORDER BY will sort in ascending order, and the ASC clause is therefore optional. Alternatively, specify DESC to sort in descending order:

	select ename,job,sal
	  from emp
	 where deptno = 10
	 order by sal desc

	ENAME       JOB               SAL
	----------  ---------  ----------
	KING        PRESIDENT        5000
	CLARK       MANAGER          2450
	MILLER      CLERK            1300

You need not specify the name of the column on which to sort. You can instead specify a number representing the column. The number starts at 1 and matches the items in the SELECT list from left to right. For example:

	select ename,job,sal
	  from emp
	 where deptno = 10
	 order by 3 desc

	ENAME       JOB               SAL
	----------  ---------  ----------
	KING        PRESIDENT        5000
	CLARK       MANAGER          2450
	MILLER      CLERK            1300

The number 3 in this example's ORDER BY clause corresponds to the third column in the SELECT list, which is SAL.

You want to sort the rows from EMP first by DEPTNO ascending, then by salary descending. You want to return the following result set:

	     EMPNO      DEPTNO         SAL  ENAME       JOB
	----------  ----------  ----------  ----------  ---------
	      7839          10        5000  KING        PRESIDENT
	      7782          10        2450  CLARK       MANAGER
	      7934          10        1300  MILLER      CLERK
	      7788          20        3000  SCOTT       ANALYST
	      7902          20        3000  FORD        ANALYST
	      7566          20        2975  JONES       MANAGER
	      7876          20        1100  ADAMS       CLERK
	      7369          20         800  SMITH       CLERK
	      7698          30        2850  BLAKE       MANAGER
	      7499          30        1600  ALLEN       SALESMAN
	      7844          30        1500  TURNER      SALESMAN
	      7521          30        1250  WARD        SALESMAN
	      7654          30        1250  MARTIN      SALESMAN
	      7900          30         950  JAMES       CLERK

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Returning Query Results in a Specified Order

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to display the names, job, and salaries of employees in department 10 in order based on their salary (from lowest to highest). You want to return the following result set:

	ENAME       JOB               SAL
	----------  ---------  ----------
	MILLER      CLERK            1300
	CLARK       MANAGER          2450
	KING        PRESIDENT        5000

Use the ORDER BY clause:

	1 select ename,job,sal
	2   from emp
	3  where deptno = 10
	4  order by sal asc

The ORDER BY clause allows you to order the rows of your result set. The solution sorts the rows based on SAL in ascending order. By default, ORDER BY will sort in ascending order, and the ASC clause is therefore optional. Alternatively, specify DESC to sort in descending order:

	select ename,job,sal
	  from emp
	 where deptno = 10
	 order by sal desc

	ENAME       JOB               SAL
	----------  ---------  ----------
	KING        PRESIDENT        5000
	CLARK       MANAGER          2450
	MILLER      CLERK            1300

You need not specify the name of the column on which to sort. You can instead specify a number representing the column. The number starts at 1 and matches the items in the SELECT list from left to right. For example:

	select ename,job,sal
	  from emp
	 where deptno = 10
	 order by 3 desc

	ENAME       JOB               SAL
	----------  ---------  ----------
	KING        PRESIDENT        5000
	CLARK       MANAGER          2450
	MILLER      CLERK            1300

The number 3 in this example's ORDER BY clause corresponds to the third column in the SELECT list, which is SAL.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Sorting by Multiple Fields

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to sort the rows from EMP first by DEPTNO ascending, then by salary descending. You want to return the following result set:

	     EMPNO      DEPTNO         SAL  ENAME       JOB
	----------  ----------  ----------  ----------  ---------
	      7839          10        5000  KING        PRESIDENT
	      7782          10        2450  CLARK       MANAGER
	      7934          10        1300  MILLER      CLERK
	      7788          20        3000  SCOTT       ANALYST
	      7902          20        3000  FORD        ANALYST
	      7566          20        2975  JONES       MANAGER
	      7876          20        1100  ADAMS       CLERK
	      7369          20         800  SMITH       CLERK
	      7698          30        2850  BLAKE       MANAGER
	      7499          30        1600  ALLEN       SALESMAN
	      7844          30        1500  TURNER      SALESMAN
	      7521          30        1250  WARD        SALESMAN
	      7654          30        1250  MARTIN      SALESMAN
	      7900          30         950  JAMES       CLERK

List the different sort columns in the ORDER BY clause, separated by commas:

	1 select empno,deptno,sal,ename,job
	2   from emp
	3  order by deptno, sal desc

The order of precedence in ORDER BY is from left to right. If you are ordering using the numeric position of a column in the SELECT list, then that number must not be greater than the number of items in the SELECT list. You are generally permitted to order by a column not in the SELECT list, but to do so you must explicitly name the column. However, if you are using GROUP BY or DISTINCT in your query, you cannot order by columns that are not in the SELECT list.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Sorting by Substrings

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to sort the results of a query by specific parts of a string. For example, you want to return employee names and jobs from table EMP and sort by the last two characters in the job field. The result set should look like the following:

	ENAME       JOB
	----------  ---------
	KING        PRESIDENT
	SMITH       CLERK
	ADAMS       CLERK
	JAMES       CLERK
	MILLER      CLERK
	JONES       MANAGER
	CLARK       MANAGER
	BLAKE       MANAGER
	ALLEN       SALESMAN
	MARTIN      SALESMAN
	WARD        SALESMAN
	TURNER      SALESMAN
	SCOTT       ANALYST
	FORD        ANALYST

DB2, MySQL, Oracle, and PostgreSQL

Use the SUBSTR function in the ORDER BY clause:

	select ename,job
	  from emp
	 order by substr(job,length(job)-2)

SQL Server

Use the SUBSTRING function in the ORDER BY clause:

	select ename,job
	  from emp
	 order by substring(job,len(job)-2,2)

Using your DBMS's substring function, you can easily sort by any part of a string. To sort by the last two characters of a string, find the end of the string (which is the length of the string) and subtract 2. The start position will be the second to last character in the string. You then take all characters after that start position. Because SQL Server requires a third parameter in SUBSTRING to specify the number of characters to take. In this example, any number greater than or equal to 2 will work.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Sorting Mixed Alphanumeric Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have mixed alphanumeric data and want to sort by either the numeric or character portion of the data. Consider this view:

	create view V
	as
	select ename||' '||deptno as data
	  from emp

	select * from V

	DATA
	-------------
	SMITH 20
	ALLEN 30
	WARD 30
	JONES 20
	MARTIN 30
	BLAKE 30
	CLARK 10
	SCOTT 20
	KING 10
	TURNER 30
	ADAMS 20
	JAMES 30
	FORD 20
	MILLER 10

You want to sort the results by DEPTNO or ENAME. Sorting by DEPTNO produces the following result set:

	DATA
	----------
	CLARK 10
	KING 10
	MILLER 10
	SMITH 20
	ADAMS 20
	FORD 20
	SCOTT 20
	JONES 20
	ALLEN 30
	BLAKE 30
	MARTIN 30
	JAMES 30
	TURNER 30
	WARD 30

Sorting by ENAME produces the following result set:

	DATA
	---------
	ADAMS 20
	ALLEN 30
	BLAKE 30
	CLARK 10
	FORD 20
	JAMES 30
	JONES 20
	KING 10
	MARTIN 30
	MILLER 10
	SCOTT 20
	SMITH 20
	TURNER 30
	WARD 30

Oracle and PostgreSQL

Use the functions REPLACE and TRANSLATE to modify the string for sorting:

	/* ORDER BY DEPTNO */

	1 select data
	2   from V
	3  order by replace(data,
	4           replace(
	5         translate(data,'0123456789','##########'),'#',''),'')

	/* ORDER BY ENAME */

	1 select data
	2   from emp
	3  order by replace(
	4           translate(data,'0123456789','##########'),'#','')

DB2

Implicit type conversion is more strict in DB2 than in Oracle or PostgreSQL, so you will need to cast DEPTNO to a CHAR for view V to be valid. Rather than recreate view V, this solution will simply use an inline view. The solution uses REPLACE and TRANSLATE in the same way as the Oracle and PostrgreSQL solution, but the order of arguments for TRANSLATE is slightly different for DB2:

	/* ORDER BY DEPTNO */

	1  select *
	2    from (
	3  select ename||' '||cast(deptno as char(2)) as data
	4    from emp
	5         ) v
	6   order by replace(data,
	7             replace(
	8           translate(data,'##########','0123456789'),'#',''),'')

	/* ORDER BY ENAME */

	1  select *
	2    from (
	3  select ename||' '||cast(deptno as char(2)) as data
	4    from emp
	5         ) v
	6   order by replace(
	7            translate(data,'##########','0123456789'),'#','')

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Dealing with Nulls when Sorting

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to sort results from EMP by COMM, but the field is nullable. You need a way to specify whether nulls sort last:

	ENAME              SAL        COMM
	----------  ----------  ----------
	TURNER            1500           0
	ALLEN             1600         300
	WARD              1250         500
	MARTIN            1250        1400
	SMITH              800
	JONES             2975
	JAMES              950
	MILLER            1300
	FORD              3000
	ADAMS             1100
	BLAKE             2850
	CLARK             2450
	SCOTT             3000
	KING              5000

or whether they sort first:

	ENAME              SAL        COMM
	----------  ----------  ----------
	SMITH              800
	JONES             2975
	CLARK             2450
	BLAKE             2850
	SCOTT             3000
	KING              5000
	JAMES              950
	MILLER            1300
	FORD              3000
	ADAMS             1100
	MARTIN            1250        1400
	WARD              1250         500
	ALLEN             1600         300
	TURNER            1500           0

Depending on how you want the data to look (and how your particular RDBMS sorts NULL values), you can sort the nullable column in ascending or descending order:

	1 select ename,sal,comm
	2   from emp
	3  order by 3

	1 select ename,sal,comm
	2   from emp
	3  order by 3 desc

This solution puts you in a position such that if the nullable column contains non-NULL values, they will be sorted in ascending or descending order as well, according to what you ask for; this may or may not what you have in mind. If instead you would like to sort NULL values differently than non-NULL values, for example, you want to sort non-NULL values in ascending or descending order and all NULL values last, you can use a CASE expression to conditionally sort the column.

DB2, MySQL, PostgreSQL, and SQL Server

Use a CASE expression to "flag" when a value is NULL. The idea is to have a flag with two values: one to represent NULLs, the other to represent non-NULLs. Once you have that, simply add this flag column to the ORDER BY clause. You'll easily be able to control whether NULL values are sorted first or last without interfering with non-NULL values:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Sorting on a Data Dependent Key

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to sort based on some conditional logic. For example: if JOB is "SALESMAN" you want to sort on COMM; otherwise, you want to sort by SAL. You want to return the following result set:

	ENAME             SAL JOB             COMM
	---------- ---------- --------- ----------
	TURNER           1500  SALESMAN          0
	ALLEN            1600  SALESMAN        300
	WARD             1250  SALESMAN        500
	SMITH             800  CLERK
	JAMES             950  CLERK
	ADAMS            1100  CLERK
	MARTIN           1250  SALESMAN       1300
	MILLER           1300  CLERK
	CLARK            2450  MANAGER
	BLAKE            2850  MANAGER
	JONES            2975  MANAGER
	SCOTT            3000  ANALYST
	FORD             3000  ANALYST
	KING             5000  PRESIDENT

Use a CASE expression in the ORDER BY clause:

	1 select ename,sal,job,comm
	2   from emp
	3  order by case when job = 'SALESMAN' then comm else sal end

You can use the CASE expression to dynamically change how results are sorted. The values passed to the ORDER BY look as follows:

	select ename,sal,job,comm,
	       case when job = 'SALESMAN' then comm else sal end as ordered
	  from emp
	 order by 5

	ENAME             SAL JOB             COMM    ORDERED
	---------- ---------- --------- ---------- ----------
	TURNER           1500 SALESMAN           0          0
	ALLEN            1600 SALESMAN         300        300
	WARD1             250 SALESMAN         500        500
	SMITH             800 CLERK                       800
	JAMES             950 CLERK                       950
	ADAMS            1100 CLERK                      1100
	MARTIN           1250 SALESMAN        1300       1300
	MILLER           1300 CLERK                      1300
	CLARK2            450 MANAGER                    2450
	BLAKE2            850 MANAGER                    2850
	JONES2            975 MANAGER                    2975
	SCOTT            3000 ANALYST                    3000
	FORD             3000 ANALYST                    3000
	KING             5000 PRESIDENT                  5000

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Working with Multiple Tables

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter introduces the use of joins and set operations to combine data from multiple tables. Joins are the foundation of SQL. Set operations are also very important. If you want to master the complex queries found in the later chapters of this book, you must start here, with joins and set operations.

You want to return data stored in more than one table, conceptually stacking one result set atop the other. The tables do not necessarily have a common key, but their columns do have the same data types. For example, you want to display the name and department number of the employees in department 10 in table EMP, along with the name and department number of each department in table DEPT. You want the result set to look like the following:

	ENAME_AND_DNAME      DEPTNO
	---------------  ----------
	CLARK                    10
	KING                     10
	MILLER                   10
	----------
	ACCOUNTING               10
	RESEARCH                 20
	SALES                    30
	OPERATIONS               40

Use the set operation UNION ALL to combine rows from multiple tables:

	1  select ename as ename_and_dname, deptno
	2    from emp
	3   where deptno = 10
	4   union all
	5  select '----------', null
	6    from t1
	7union all
	8  select dname, deptno
	9    from dept

UNION ALL combines rows from multiple row sources into one result set. As with all set operations, the items in all the SELECT lists must match in number and data type. For example, both of the following queries will fail:

	select deptno   |  select deptno, dname
	  from dept     |    from dept
	 union all      |   union
	select ename    |  select deptno
	  from emp      |    from emp

It is important to note, UNION ALL will include duplicates if they exist. If you wish to filter out duplicates, use the UNION operator. For example, a UNION between EMP.DEPTNO and DEPT.DEPTNO returns only four rows:

	select deptno
	  from emp
	 union
	select deptno
	  from dept

	   DEPTNO
	---------
	       10
	       20
	       30
	       40

Specifying UNION rather than UNION ALL will most likely result in a sort operation in order to eliminate duplicates. Keep this in mind when working with large result sets. Using UNION is roughly equivalent to the following query, which applies DISTINCT to the output from a UNION ALL:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Stacking One Rowset atop Another

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to return data stored in more than one table, conceptually stacking one result set atop the other. The tables do not necessarily have a common key, but their columns do have the same data types. For example, you want to display the name and department number of the employees in department 10 in table EMP, along with the name and department number of each department in table DEPT. You want the result set to look like the following:

	ENAME_AND_DNAME      DEPTNO
	---------------  ----------
	CLARK                    10
	KING                     10
	MILLER                   10
	----------
	ACCOUNTING               10
	RESEARCH                 20
	SALES                    30
	OPERATIONS               40

Use the set operation UNION ALL to combine rows from multiple tables:

	1  select ename as ename_and_dname, deptno
	2    from emp
	3   where deptno = 10
	4   union all
	5  select '----------', null
	6    from t1
	7union all
	8  select dname, deptno
	9    from dept

UNION ALL combines rows from multiple row sources into one result set. As with all set operations, the items in all the SELECT lists must match in number and data type. For example, both of the following queries will fail:

	select deptno   |  select deptno, dname
	  from dept     |    from dept
	 union all      |   union
	select ename    |  select deptno
	  from emp      |    from emp

It is important to note, UNION ALL will include duplicates if they exist. If you wish to filter out duplicates, use the UNION operator. For example, a UNION between EMP.DEPTNO and DEPT.DEPTNO returns only four rows:

	select deptno
	  from emp
	 union
	select deptno
	  from dept

	   DEPTNO
	---------
	       10
	       20
	       30
	       40

Specifying UNION rather than UNION ALL will most likely result in a sort operation in order to eliminate duplicates. Keep this in mind when working with large result sets. Using UNION is roughly equivalent to the following query, which applies DISTINCT to the output from a UNION ALL:

	select distinct deptno
	  from (
	select deptno
	  from emp
	 union all
	select deptno
	  from dept
	         )

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Combining Related Rows

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to return rows from multiple tables by joining on a known common column or joining on columns that share common values. For example, you want to display the names of all employees in department 10 along with the location of each employee's department, but that data is stored in two separate tables. You want the result set to be the following:

	ENAME       LOC
	----------  ----------
	CLARK       NEW YORK
	KING        NEW YORK
	MILLER      NEW YORK

Join table EMP to table DEPT on DEPTNO:

	1 select e.ename, d.loc
	2   from emp e, dept d
	3  where e.deptno = d.deptno
	4    and e.deptno = 10

The solution is an example of a join, or more accurately an equi-join, which is a type of inner join. A join is an operation that combines rows from two tables into one. An equi-join is one in which the join condition is based on an equality condition (e.g., where one department number equals another). An inner join is the original type of join; each row returned contains data from each table.

Conceptually, the result set from a join is produced by first creating a Cartesian product (all possible combinations of rows) from the tables listed in the FROM clause, as seen below:

	select e.ename, d.loc,
	       e.deptno as emp_deptno,
	       d.deptno as dept_deptno
	  from emp e, dept d
	 where e.deptno = 10

	ENAME      LOC            EMP_DEPTNO DEPT_DEPTNO
	---------- -------------  ---------- -----------
	CLARK      NEW YORK               10          10
	KING       NEW YORK               10          10
	MILLER     NEW YORK               10          10
	CLARK      DALLAS                 10          20
	 
	KING       DALLAS                 10          20
	MILLER     DALLAS                 10          20
	CLARK      CHICAGO                10          30
	KING       CHICAGO                10          30
	MILLER     CHICAGO                10          30
	CLARK      BOSTON                 10          40
	KING       BOSTON                 10          40
	MILLER     BOSTON                 10          40

Every employee in table EMP (in department 10) is returned along with every department in the table DEPT. Then, the expression in the WHERE clause involving e.deptno and d.deptno (the join) restricts the result set such that the only rows returned are the ones where EMP.DEPTNO and DEPT.DEPTNO are equal:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Finding Rows in Common Between Two Tables

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to find common rows between two tables but there are multiple columns on which you can join. For example, consider the following view V:

	create view V
	as
	select ename,job,sal
	  from emp
	 where job = 'CLERK'

	select * from V

	ENAME       JOB              SAL
	----------  --------- ----------
	SMITH       CLERK            800
	ADAMS       CLERK           1100
	JAMES       CLERK            950
	MILLER      CLERK           1300

Only clerks are returned from view V. However, the view does not show all possible EMP columns. You want to return the EMPNO, ENAME, JOB, SAL, and DEPTNO of all employees in EMP that match the rows from view V. You want the result set to be the following:

	   EMPNO  ENAME       JOB             SAL     DEPTNO
	--------  ----------  --------- ---------- ---------
	   7369   SMITH       CLERK           800         20
	   7876   ADAMS       CLERK          1100         20
	   7900   JAMES       CLERK           950         30
	   7934   MILLER      CLERK          1300         10

Join the tables on all the columns necessary to return the correct result. Alternatively, use the set operation INTERSECT to avoid performing a join and instead return the intersection (common rows) of the two tables.

MySQL and SQL Server

Join table EMP to view V using multiple join conditions:

	1 select e.empno,e.ename,e.job,e.sal,e.deptno
	2   from emp e, V
	3  where e.ename = v.ename
	4    and e.job   = v.job
	5    and e.sal   = v.sal

Alternatively, you can perform the same join via the JOIN clause:

	1 select e.empno,e.ename,e.job,e.sal,e.deptno
	2   from emp e join V
	3     on (    e.ename   = v.ename
	4        and e.job     = v.job
	5        and e.sal     = v.sal )

DB2, Oracle, and PostgreSQL

The MySQL and SQL Server solution also works for DB2, Oracle, and PostgreSQL. It's the solution you should use if you need to return values from view V.

If you do not actually need to return columns from view V, you may use the set operation INTERSECT along with an IN predicate:

	1 select empno,ename,job,sal,deptno
	2   from emp
	3  where (ename,job,sal) in (
	4   select ename,job,sal from emp
	5intersect
	6   select ename,job,sal from V
	7  )

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Retrieving Values from One Table That Do Not Exist in Another

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You wish to find those values in one table, call it the source table, that do not also exist in some target table. For example, you want to find which departments (if any) in table DEPT do not exist in table EMP. In the example data, DEPTNO 40 from table DEPT does not exist in table EMP, so the result set should be the following:

	      DEPTNO
	  ----------
	          40

Having functions that perform set difference is particularly useful for this problem. DB2, PostgreSQL, and Oracle support set difference operations. If your DBMS does not support a set difference function, use a subquery as shown for MySQL and SQL Server.

DB2 and PostgreSQL

Use the set operation EXCEPT:

	1 select deptno from dept
	2 except
	3 select deptno from emp

Oracle

Use the set operation MINUS:

	1 select deptno from dept
	2 minus
	3 select deptno from emp

MySQL and SQL Server

Use a subquery to return all DEPTNOs from table EMP into an outer query that searches table DEPT for rows that are not amongst the rows returned from the subquery:

	1 select deptno
	2   from dept
	3  where deptno not in (select deptno from emp)

DB2 and PostgreSQL

The built-in functions provided by DB2 and PostgreSQL make this operation quite easy. The EXCEPT operator takes the first result set and removes from it all rows found in the second result set. The operation is very much like a subtraction.

There are restrictions on the use of set operators, including EXCEPT. Data types and number of values to compare must match in both SELECT lists. Additionally, EXCEPT will not return duplicates and, unlike a subquery using NOT IN, NULLs do not present a problem (see the discussion for MySQL and SQL Server). The EXCEPT operator will return rows from the upper query (the query before the EXCEPT) that do not exist in the lower query (the query after the EXCEPT).

Oracle

The Oracle solution is identical to that for DB2 and PostgreSQL, except that Oracle calls its set difference operator MINUS rather than EXCEPT. Otherwise, the preceding explanation applies to Oracle as well.

MySQL and SQL Server

The subquery will return all DEPTNOs from table EMP. The outer query returns all DEPTNOs from table DEPT that are "not in" or "not included in" the result set returned from the subquery.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Retrieving Rows from One Table That Do Not Correspond to Rows in Another

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to find rows that are in one table that do not have a match in another table, for two tables that have common keys. For example, you want to find which departments have no employees. The result set should be the following:

	      DEPTNO  DNAME           LOC
	  ----------  --------------  -------------
	          40  OPERATIONS      BOSTON

Finding the department each employee works in requires an equi-join on DEPTNO from EMP to DEPT. The DEPTNO column represents the common value between tables. Unfortunately, an equi-join will not show you which department has no employees. That's because by equi-joining EMP and DEPT you are returning all rows that satisfy the join condition. Instead you want only those rows from DEPT that do not satisfy the join condition.

This is a subtly different problem than in the preceding recipe, though at first glance they may seem the same. The difference is that the preceding recipe yields only a list of department numbers not represented in table EMP. Using this recipe, however, you can easily return other columns from the DEPT table; you can return more than just department numbers.

Return all rows from one table along with rows from another that may or may not have a match on the common column. Then, keep only those rows with no match.

DB2, MySQL, PostgreSQL, SQL Server

Use an outer join and filter for NULLs (keyword OUTER is optional):

	1 select d.*
	2   from dept d left outer join emp e
	3     on (d.deptno = e.deptno)
	4  where e.deptno is null

Oracle

For users on Oracle9i Database and later, the preceding solution will work. Alternatively, you can use the proprietary Oracle outer-join syntax:

	1 select d.*
	2   from dept d, emp e
	3  where d.deptno = e.deptno (+)
	4    and e.deptno is null

This proprietary syntax (note the use of the "+" in parens) is the only outer-join syntax available in Oracle8i Database and earlier.

This solution works by outer joining and then keeping only rows that have no match. This sort of operation is sometimes called an anti-join. To get a better idea of how an anti-join works, first examine the result set without filtering for NULLs:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Adding Joins to a Query Without Interfering with Other Joins

Content preview·Buy PDF of this chapter|