The SQL JOIN clause selects data from two or more tables tied together by matching table columns. To illustrate how to use the SQL JOIN clause we will use the already familiar Weather table and we will introduce a new one called State.

Weather table

New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
New York18 C10/09/2005
Seattle20 C10/09/2005
Washington17 C10/09/2005

State table

District of ColumbiaWashington
New YorkNew York

If we want to select all the data from the Weather table, and to add one more additional column called State to the result, we can do it the following way:

SELECT Weather.City, Weather.AverageTemperature, Weather.Date, State.State FROM Weather, State WHERE Weather.City = State.City

In the SQL SELECT list we see 4 column names prefixed with their respective table names, so there is no confusion which column from which table we are referencing.

The SQL FROM clause has comma separated list of the two tables we are selecting data from. Finally the SQL WHERE clause defines that the City column from the weather table has to match the City column from the State table.

We can re-write the above SQL statement using SQL JOIN and the result will be the same, but the performance in general will be much better: SELECT Weather.City, Weather.AverageTemperature, Weather.Date, State.State FROM Weather JOIN State ON Weather.City = State.City

The first line of this SQL JOIN statement is identical to the first line of the previous SQL expression. The second line uses the SQL JOIN clause instead of comma, and the third line uses the ON keyword to define the columns we are joining on.

The result of this SQL JOIN will be:

New York22 C10/10/2005New York
Seattle21 C10/10/2005Washington
Washington20 C10/10/2005District of Columbia
New York18 C10/09/2005New York
Seattle20 C10/09/2005Washington
Washington17 C10/09/2005District of Columbia