How to Work with Inner and Outer Joins

  • google plus

In Brief...

Joins are the most common forms of SQL selects. Inner joins connect two or more tables together by comparing rows and produce a row of output only if the comparison of the rows returns true. Outer joins can produce a row of output even if the comparison does not return true.

Take our Advanced Oracle SQL Queries course for free.

See the Course Outline and Register

Instructions

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

  1. You'll need to download and install Oracle 12c. The instructions for the setup can be found in the topic . Follow steps 1 through 4 in this topic before proceeding to the next step.
  2. We will start with constructing an inner join to display employee name from the Employees table and department name from the Departments table. Execute the following SQL select statement:
    Display employees and department name with inner join
    Note that I have displayed the first part of the output that contains 106 employees overall. The inner join is used to specify we are joining Employees to Departments. An inner join operation stipulates that the on condition must return true in order for a row to be displayed. Since I am using the equality operator (= sign) the join condition is referred to as an "equijoin" and a match between a row in the Employees table and the Departments table must occur before the column data is displayed.
  3. Next, we will build a select statement that displays department name and employee name for the "IT" and "Payroll" departments. Execute the following SQL select statement:
    Display department name and employee name for specific departments with inner join
    Again, this statement is an inner join. I have added a where clause to limit the output to the IT department (department ID = 60) and the Payroll department (department ID = 270). Note that no employees currently work in the Payroll department and therefore the Payroll department does not appear in the report.
  4. Now we will take the same select statement and modify it to perform an outer join. Execute the following SQL select statement:
    Display department name and employee name for specific departments with inner join
    Notice that the Payroll department now appears on the report. The left outer join is used to specify that column data from the table to the left of the join condition, i.e., Departments will display even if the on condition does not return true.
,

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