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.
|New York||22 C||10/10/2005|
|New York||18 C||10/09/2005|
|District of Columbia||Washington|
|New York||New 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:
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 York||22 C||10/10/2005||New York|
|Washington||20 C||10/10/2005||District of Columbia|
|New York||18 C||10/09/2005||New York|
|Washington||17 C||10/09/2005||District of Columbia|