SQL Hacks

Chapter 2. Joins, Unions, and Views

You can use a join to associate the rows of one table with the rows of another. Often you do this to follow a foreign key reference. For example, consider an employee table that contains a column with the id of the department for each employee. If you need to see the name of the department for each employee, you can use a JOIN, as in:

SELECT employee.name, department.name
  FROM employee JOIN department ON (employee.department=department.id)

The default JOIN is an INNER JOIN, as shown in the preceding code. There are other kinds of JOINs, such as the LEFT OUTER JOIN, the FULL OUTER JOIN, and the CROSS JOIN. You can find examples of each in this chapter.

You also can use a UNION to combine two tables, but unlike with a JOIN, a UNION appends the rows of two tables into one result. In a UNION, the two tables must have the same number of columns, and the corresponding columns must have compatible types.

You can use a VIEW to name a query. If you have a SELECT statement (possibly using a JOIN or a UNION) you can save it as a named VIEW. As much as possible the system will treat the view as though it were a base table; you can SELECT from it, or JOIN it to other tables or views. It is generally possible to UPDATE, DELETE from, and INSERT into a view (with some restrictions).

Modify a Schema Without Breaking Existing Queries

When your software requirements change and you require a different database design, you don’t have to throw out all your code. You ...