How to Work with Joins in SQL
Many times you will need data from more than one table to display on your report. To fetch data from two or more relational tables you will code a join.
To learn how work with joins, follow these steps:
- You'll need to setup the MySQL database tables. The instructions for the setup can be found in How to add comments in simple SQL selects. Follow steps 1 through 7 before proceeding to the next step.
- We will now turn our attention to writing a join.
- Imagine we are required to display city name and the name of the state. The Cities table contains the city name, but does not store the state name.
The state name is located on the States table. Therefore, we need to join the Cities table to the States table in order to meet the requirement.
Execute the following statement:
Notice that I've included two tables on the from clause. In addition, I've provided a table alias for each table (e.g., "c" for the Cities table) that I use to qualify potentially ambiguous references (e.g.,
namethat appears in both tables). I use the alias in the select list of columns and in the where clause. Furthermore, note that in the where clause I check to make sure the state abbreviation in the Cities table is equal to the state abbreviation in the States table. This is necessary so that I display the correct state name for that city. If we omit the join condition in the where clause then each city would be joined to each state, producing a much larger (and incorrect) result!