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:

  1. 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.
  2. We can now write an outer join.
  3. 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:
    Outer join to display state name and city
    Notice that I've included two tables on the from clause by using the outer join keywords. 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., name that appears in both tables). I use the alias in the select list of columns and in the on clause. Furthermore, note that in the on condition 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.
Author: Stephen Withrow

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.