In the last Jump Start column, you learned that the SELECT statement is the core of almost all database applications. In its most basic form, the SELECT statement retrieves specified (or "selected") rows and columns from a given table. However, the SELECT statement can be much more powerful. In fact, one of the most potent uses of the SELECT statement, and one of the central abilities of relational database systems, is its ability to join data from multiple tables. The data that the SELECT statement returns is referred to as the result set.
If you look at various code examples that are widely available on the Internet or from various SQL Server or database development books, you might notice that a couple of different methods are used to join tables. The older T-SQL syntax used an equals sign (=) with the WHERE clause to perform joins. That method, although still workable, is somewhat dated. Now it’s preferable to use the ANSI JOIN syntax, which utilizes the INNER JOIN keywords. The ANSI method is less ambiguous because the WHERE clause is reserved for row selection rather than specifying join conditions.
The following example shows a basic two-table join for the Customers and Orders tables in the Northwind database.
SELECT * FROM customers c INNER JOIN orders o ON c.customerID = o.CustomerID
All the rows from the Customers table and the Orders table are retrieved, and the two tables are joined on the common column of CustomerID. Note that to join two tables there must be a common column that the database uses to "relate" the tables. That common column name is usually identical in well designed databases, but it doesn’t actually have to be the same. In this example, each table is supplied an alias ("c" for customers and "o" for orders) to make identifying the CustomerID column unambiguous.
If you want to see all the orders for customers with the name ANTON, you could change the SELECT statement as follows:
SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate FROM customers c INNER JOIN orders o ON c.customerID = o.CustomerID WHERE c.customerID = 'ANTON'
The SELECT statement returns the CustomerID, CompanyName, OrderID, and OrderDate columns only where the CustomerID is equal to ANTON.