How to Work with Inner Joins

  • google plus

In Brief...

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. The inner join syntax is preferable when you code the join as compared to the older technique of listing the table names without an explicit reference to a join operation.

Take our Introduction to SQL Training course for free.

See the Course Outline and Register

Instructions

To learn how work with inner joins, follow these steps:

  1. You'll need to setup the MySQL database tables. The instructions for the setup can be found in the topic How to add comments in simple SQL selects. Follow steps 1 through 7 before proceeding to the next step.
  2. Now let's write an inner join.
  3. 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:
    Join to display city name and state name
    Notice that I've included two tables on the from clause by using the inner join keywords. 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., 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 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 on condition then each city would be joined to each state, producing a much larger (and incorrect) result!

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, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.

Categories

Courses

Author: Stephen Withrow

Stephen has over 30 years' experience in training, development, and consulting in a variety of technology areas including Java, C, C++, XML, JavaScript, AJAX, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen is a published writer in both technical and non-technical endeavors. Stephen received an undergraduate degree in Computer Science and Physics from Florida State University.

Discuss