SQL: WHERE Clause

In SQL the WHERE clause is the most common join you will see, it relates one or more tables together. For example you want to get the employeeis territory information in the Northwind database but you there are all in different tables.

As you can see from the above diagram the employee information is in the Employees table, while a linking table is used to link the employee to the territory in the EmployeeTerritories, and then there's the Territories table which contains the actual name of the territory in the Territory. How do we proceed to retrieve this information? With a WHERE clause of course. The WHERE clause allows us to retrieve information from all these tables and combine them into one result set. Here is the query that you would write with the WHERE clause:

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e,Territories t, EmployeeTerritories et
WHERE e.EmployeeID = et.EmployeeID
  AND et.TerritoryID = t.TerritoryID

The query above joins the Employees table to the EmployeesTerritories table matching the two tables by the column EmployeeID, then after we get the records with matching records between those two tables, we and the word "AND" to add additional joins based on our first join. This time we want to match the TerritoryID in the EmployeesTerritories table with the TerritoryID column in the Territories which contains the TerritoryDescription field that we wanted. So with the WHERE clause we were able to work with three tables at once in one query.

You can filter the results even more by looking adding more filtering conditions in the WHERE clause.
Let's say you want to get only employees who belongs to the Boston territory, from the first query you know that Boston has a TerritoryID of 02116. So to get the employees who belongs to the Boston territory you would write the query like the one below:

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e,Territories t, EmployeeTerritories et
WHERE e.EmployeeID = et.EmployeeID
      AND et.TerritoryID = t.TerritoryID
      AND t.TerritoryID = 02116

Here are the results: