INNER and OUTER JOIN Statements

INNER and OUTER JOIN Statements

You can use the SELECT statement to retrieve data from more than one table

at a time. SQL statements referencing more than one table typically (but not

necessarily) use a JOIN statement to connect the tables on a common field or

value.

For example:

SELECT StudentID

FROM TeacherStudent INNER JOIN Teachers ON

TeacherStudent.TeacherID=Teachers.TeacherID

WHERE Teachers.LastName='Franklin' AND

Teachers.FirstName='Marsha'

When you use two tables, you can't use the asterisk shorthand to retrieve all

the fields from only one of the tables (although you can use it to retrieve all

the fields in both tables). In such cases, the tablename.* syntax selects all

the fields from the named table.

The INNER JOIN statement requires that you specify which tables and fields

the database should join to produce the query. Also, when you work with

more than one table you must specify the table name as well as the column

name for each field where the field name appears in more than one table. In

other words, if the column name is not unique among all fields in all tables in

the FROM clause, the server will raise an error, because it can't distinguish

the table from which to extract the data.

When you know that a foreign key may not exist, or may not match a key

value in the joined table, you can perform a LEFT (OUTER) JOIN or a

RIGHT (OUTER) JOIN. The OUTER keyword is optional. Outer joins return

all the values from one of the tables even if there's no matching key.