How to Use Join?

Combining tables side by side is a join. Tables are combined by matching data in a column — the column that they have in common. The combined results table produced by a join contains all the columns from both tables. For instance, if one table has two columns (memberID and height), and the second table has two columns (memberID and weight), a join results in a table with four columns: memberID (from the first table), height, memberID (from the second table), and weight.

The two common types of joins are an inner join and an outer join. The difference between an inner and outer join is in the number of rows included in the results table. The results table produced by an inner join contains only rows that existed in both tables. The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table. For instance, if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows — a row for Joe and a row for Sally — even though the row for Joe would have a blank field for weight. The results table for the outer join contains all the rows for one table. If any of the rows for that table don’t exist in the second table, the columns for the second table are empty. Clearly, the contents of the results table are determined by which table contributes all its rows, requiring the second table to match it. Two kinds of outer joins control which table sets the rows and which match: a LEFT JOIN and a RIGHT JOIN. You use different SELECT queries for an inner join and the two types of outer joins. The following query is an inner join:

SELECT columnnamelist FROM table1,table2
WHERE table1.col2 = table2.col2
And these queries are outer joins:
SELECT columnnamelist FROM table1 LEFT JOIN table2
ON table1.col1=table2.col2
SELECT columnnamelist FROM table1 RIGHT JOIN table2
ON table1.col1=table2.col2

In all three queries, table1 and table2 are the tables to be joined. You can join more than two tables. In both queries, col1 and col2 are the names of the columns being matched to join the tables. The tables are matched based on the data in these columns. These two columns can have the same name or different names. The two columns must contain the same type of data.

How to Use UNION

UNION is used to combine the results from two or more select queries. The results from each query are added to the result set following the results of the previous query. The format of the UNION query is as follows:

SELECT query UNION ALL SELECT query ...

You can combine as many SELECT queries as you need. A SELECT query can include any valid SELECT format, including WHERE clauses, LIMIT clauses, and so on. The rules for the queries are

• All the select queries must select the same number of columns.
• The columns selected in the queries must contain the same type of data.

The result set will contain all the rows from the first query followed by all the rows from the second query and so on. The column names used in the result set are the column names from the first SELECT query. The series of SELECT queries can select different columns from the same table, but situations in which you want a new table with one column in a table followed by another column from the same table are unusual. It’s much more likely that you want to combine columns from different tables. For example, you might have a table of members who have resigned from the club and a separate table of current members. You can get a list of all members, both current and resigned, with the following query:

SELECT lastName,firstName FROM Member UNION ALL SELECT lastName,firstName FROM OldMember

The result of this query is the last and first names of all current members, followed by the last and first names of all the members who have resigned. Depending on how you organized your data, you might have duplicate names. For instance, perhaps a member resigned, and his name is in the OldMember table — but he joined again, so his name is added to the Member table. If you don’t want duplicates, don’t include the word ALL. If ALL is not included, duplicate lines are not added to the result.
You can use ORDER BY with each SELECT query, as I discuss in the previous section, or you can use ORDER BY with a UNION query to sort all the rows in the result set. If you want ORDER BY to apply to the entire result set, rather than just to the query that it follows, use parentheses as follows:

(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName

The UNION statement was introduced in MySQL 4.0. It is not available in MySQL 3.

Microsoft to Offer SQL Server 2005 SP3

Microsoft will release a third service pack for SQL Server 2005, just before the next version of the server software comes out.

Service Pack 3 is expected to come out after the release to manufacturing of SQL Server 2008, which is scheduled to happen in the third quarter this year.

Microsoft didn't reveal much about what the service pack would include, except to say in a Tuesday blog post that it will contain all cumulative updates to the software plus some additional fixes to bugs that customers have reported on MS Connect, a Microsoft Web site for customer feedback.

The development of a third service pack does not change the Incremental Servicing Model that Microsoft introduced last year. The model, unveiled last July, introduced a regular update process to SQL Server 2005. Since then, every two months Microsoft issues a cumulative update for SQL Server 2005 that includes all critical fixes that had been released during that time as well as updates for less urgent issues.

Customers like the predictability of the model, so Microsoft will continue using it, said Francois Ajenstat, director of SQL Server marketing for Microsoft, in a separate blog post. However, users also say that there's a need for a third service pack, so Microsoft plans to release one, he said. The company is announcing its plans now so that customers can plan for deployment, he said.

CodeGear delivers Delphi for PHP 2.0.

CodeGear, the developer tools arm of Borland Software, has upgraded its tool set for PHP developers.

The company announced Delphi for PHP 2.0 on April 14. Delphi for PHP is an IDE (integrated development environment) for rapidly building interactive Web applications using visual drag-and-drop design capabilities. The product also features a PHP component framework.

To read about the debugger CodeGear contributed to Eclipse, click here.

CodeGear officials said Delphi for PHP 2.0 features several enhancements, including HTML templates with embedded dynamic PHP; database support for MySQL, CodeGear InterBase, Microsoft SQL Server, Oracle, PostgreSQL, IBM Informix and DB2, and Sybase's SQL Anywhere, plus the ability to build rich data-driven Web applications without requiring database connectivity coding; an expanded and faster VCL (Visual Component Library) for PHP with support for popular PHP packages and libraries including the Zend Framework from Zend Technologies; productivity and performance enhancements including new error insight and source code formatting; and new PHP debugging features and integrated PHP performance profiling and tuning.

According to Michael Swindell, CodeGear's vice president of products, Version 2.0 of Delphi for PHP is a major advancement over the initial version, which introduced PHP developers to the productivity of a specialized IDE.

Yet, "perhaps the most significant advance is the new HTML template system, which enables PHP developers to seamlessly build rich PHP applications against HTML templates for design flexibility and easy customization with popular HTML tools," Swindell said.

Delphi for PHP 2.0 is available at an introductory price of $249 through the end of June 2008. Upgrades from Delphi for PHP 1.0 are currently $179.

Combining information from tables

In previous sections of this chapter, I assume that all the information you want is in a single table. However, you might want to combine information from different tables. You can do this easily in a single query. Two words can be used in a SELECT query to combine information from two or more tables:

• UNION: Rows are retrieved from one or more tables and stored together, one after the other, in a single result. For example, if your query selected 6 rows from one table and 5 rows from another table, the result would contain 11 rows.
• JOIN: The tables are combined side by side, and the information is retrieved from both tables.

Retrieving data from a specific source

Frequently, you don’t want all the information from a table. You want information from selected database objects, that is, rows. Three SQL words are frequently used to specify the source of the information:

• WHERE: Allows you to request information from database objects with certain characteristics. For instance, you can request the names of members who live in California, or you can list only pets that are cats.
• LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request all the information from the first three rows in the table.
• DISTINCT: Allows you to request information from only one row of identical rows. For instance, in the Login table, you can request loginName but specify no duplicate names, thus limiting the response to one record for each member. This would answer the question, “Has the member ever logged in?” rather than the question “How many times has the member logged in?”

The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss asks for a list of all the members whose last names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number. I’m sure there are many uses for such a list. You can get this list for your boss with a SELECT query by using a WHERE clause.
The basic format of the WHERE clause is

WHERE expression AND|OR expression AND|OR expression ...

expression specifies a value to compare with the values stored in the database. Only the rows containing a match for the expression are selected. You can use as many expressions as needed, each one separated by AND or OR. When you use AND, both of the expressions connected by the AND (that is, both the expression before the AND and the expression after the AND) must be true in order for the row to be selected. When you use OR, only one of the expressions connected by the OR must be true for the row to be selected. You can combine any of the expressions with ANDs and ORs. In some cases, you need to use parentheses to clarify the selection criteria. For instance, you can use the following query to answer your boss’s urgent need to find all people in the Member Directory whose names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Member
WHERE lastName LIKE “B%”
AND city = “Santa Barbara”

AND (phone LIKE “%8%” OR fax LIKE “%8%”) Notice the parentheses in the last line. You would not get the results that your boss asked for without the parentheses. Without the parentheses, each connector would be processed in order from the first to the last, resulting in a list that includes all members whose names begin with B and who live in Santa Barbara and whose phone numbers have an 8 in them and all members whose fax numbers have an 8 in them, whether they live in Santa Barbara or not and whether their name begins with a B or not. When the last OR is processed, members are selected whose characteristics match the expression before the OR or the expression after the OR. The expression before the OR is connected to previous expressions by the previous ANDs and so does not stand alone, but the expression after the OR does stand alone, resulting in the selection of all members with an 8 in their fax number.

LIMIT specifies how many rows can be returned. The form for LIMIT is
LIMIT startnumber,numberofrows

The first row that you want to retrieve is startnumber, and the number of rows to retrieve is numberofrows. If startnumber is not specified, 1 is assumed. To select only the first three members who live in Texas, use this query:

SELECT * FROM Member WHERE state=”TX” LIMIT 3

Some SELECT queries will find identical records, but in this example you want to see only one — not all — of the identical records. To prevent the query from returning all identical records, add the word DISTINCT immediately after SELECT.

Retrieving data in a specific order

You might want to retrieve data in a particular order. For instance, in the Member table, you might want members organized in alphabetical order by last name. Or, in the Pet table, you might want the pets grouped by type of pet. In a SELECT query, ORDER BY and GROUP BY affect the order in which the data is delivered to you:

• ORDER BY: To sort information, use the phrase ORDER BY columnname The data is sorted by columnname in ascending order. For instance, if columnname is lastName, the data is delivered to you in alphabetical order by the last name. You can sort in descending order by adding the word DESC before the column name. For example: SELECT * FROM Member ORDER BY DESC lastName
  
• GROUP BY: To group information, use the following phrase: GROUP BY columnname The rows that have the same value of columnname are grouped together. For example, use this query to group the rows that have the same value as petType:
  
• SELECT * FROM Pet GROUP BY petType
  
• You can use GROUP BY and ORDER BY in the same query.