How to Write Outer Joins
An inner join will display only rows that match between two or more tables. If you want to display rows from a table regardless of a match with another table then you will code an outer join.
To learn how write outer 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 can now write an outer join.
- Imagine we need to list all state names in the States table and any matching cities (i.e., cities with the same state abbreviation) in the Cities table. If a matching
city does not exist, we still want to display the state name. Here is the SQL select statement that will solve this challenge:
Notice that I've included two tables on the from clause by using the
outer joinkeywords. In addition, I've provided a table alias for each table (e.g., "s" for the States 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
onclause. Furthermore, note that in the
oncondition I check to make sure the state abbreviation in the States table is equal to the state abbreviation in the Cities table. This is necessary so that I display the correct city name located in that state. If a matching city is not found in the Cities table then the city name displays as
NULL. I've highlighted the two states (Maine and Texas) that do not have a match in the Cities table.