SQL JOIN

By this chapter you should already have a decent understanding of SQL, and it's time now to learn how to select data from more than one table. The JOIN SQL clause is used to retrieve data from 2 or more tables. We are already familiar with the Cars table, however we will introduce a new table CountryOfOrigin. The two tables are below:

Cars

CarMakeModelYearColor
ToyotaCamry XLE2005Gray
HondaAccord EX2002Black
LexusES 3502008Gray
BMW3 Series Coupe2008Red

CountryOfOrigin

CarMakeCountry
ToyotaJapan
HondaJapan
LexusJapan
BMWGermany

There is a common field between the two tables and this is the CarMake column. Because of this relation between the two table we can do many different SELECT queries retrieving data from both tables at the same time. Consider the following SQL JOIIN statement:

SELECT Cars.Model, CountryOfOrigin FROM Cars JOIN CountryOfOrigin ON Cars.CarMake = CountryOfOrigin.CarMake;

We are selecting two columns one from the Cars table (Model) and the second one from the CountryOfOrigin table (Country). The "Cars.Model" simply means that we want to retrieve the Model column from the Cars table, and this is done to avoid any ambiguity if the second table has a column with the same name. The FROM clause is followed by the JOIN clause, which specifies the two table we are joining. Next comes the ON keyword, which specifies which column in each table has relation to the other (CarMake for both tables in this case). The result of this statement will be:

ModelCountry
Camry XLEJapan
Accord EXJapan
ES 350Japan
3 Series CoupeGermany

There is no need to select columns from both tables, when using JOIN. For example if you want to get all models from cars that have a country of origin Japan, you can do it as follows:

SELECT Cars.Model FROM Cars JOIN CountryOfOrigin ON Cars.CarMake = CountryOfOrigin.CarMake WHERE CountryOfOrigin.Country = 'Japan';

The result will look like this:

Model
Camry XLE
Accord EX
ES 350