Inner Join at Database Tutorial

Inner Join

The INNER JOIN matches every row in one table against every row in another based on common values in one or a set of columns found in both tables. This type of join operation is a filtered join. In its most basic approach an INNER JOIN acts like a nested loop where you match a key from the outer loop against a key from the inner loop.

Another way to describe an INNER JOIN is as an intersection between two rows of data. The formula uses a ∀ to indicate given any sets A and B, then A intersects (∩) B. The intersection works by linking a row of data in one table with a row of data in another table based on the values in one or more columns found in each table. In some implementations you get two copies of the column or set of column values that match, and in others you get a single copy of the matching column or set of column values.

SQL INNER JOIN Example →

Rule(s):

An inner join must have a join statement.
An inner join that uses the INNER JOIN clause must include a join statement in the FROM clause.
An inner join that uses comma delimited tables must include a join statement in the WHERE clause.
An inner join returns the intersection of two sets.

INNER JOIN Syntax

ANSI SQL:89 ANSI SQL:92

ANSI SQL:89

ANSI SQL:89 syntax effects a INNER JOIN by putting the table names in the FROM clause as a comma delimited list. There’s no requirement to implicitly or explicitly order these table names. The join occurs in the WHERE clause. You can compare the equality of values between two columns, or a set of columns. In the case of a set of columns, you use the AND operator to broaden the comparison of values.

The following syntax lets you inner join two tables:

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english, ordinal_french
WHERE    ordinal_english_id = ordinal_french_id;

This is the older syntax and sometimes people get confused and call it the Oracle Proprietary SQL, but it is really ANSI SQL:89. Oracle Proprietary SQL is the set of behaviors they implemented that go beyond the ANSI SQL:89 specification, and their SQL functions, like the NVL() function.

As the number of tables grow in a large query, you may find this syntax the easier. The reason it appears easier is you don’t have to manually organize which table joins should occur in what order, which is required in the ANSI SQL:92 specification.

ANSI SQL:92

ANSI SQL:92 syntax is generally implemented by vendors such that the INNER JOIN keywords are equivalent to the JOIN keyword. This makes things simpler but we should recall that a JOIN is an INNER JOIN.

ANSI SQL:92 syntax effects an INNER JOIN by placing the join operation in the FROM clause. Inside the FROM clause, you place the join condition in an ON subclause or a USING subclause. The ON subclause tests the equality of values between two columns where the column names may be the same or different. The USING sibclause tests the equality of two columns when they have the same name.

You can use an AND keyword is a logical operator that lets you test equality of a set of column values against an equivalent set of column values. The OR keyword is another logical operator that lets you test the equality of one or a set of column values against another column of set of column values.

You may also provide multiple column names as call parameters to the USING subclause because it works more like a function than a clause. The USING subclause works like the AND logical operator and has no equivalent syntax to an OR logical operator.

The following syntax lets you INNER JOIN two tables where the column name is different:

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english INNER JOIN ordinal_french
ON       ordinal_english_id = ordinal_french_id;

This returns only rows with only the two selected columns, where they are the result of a join between to tables:

ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------- --------------------
One                  Un
Three                Trois
Four                 Quatre
Five                 Cinq
Six                  Six

In the prior example, the ORDINAL_ENGLISH table’s primary key column name differs from the ORDINAL_FRENCH table’s primary key column name. The difference required that you use the ON subclause.

You can use the USING subclause by changing the second table to the ORDINAL_OLD_ENGLISH table because its primary key column name is the same as the ORDINAL_ENGLISH table’s primary key. That’s what is done in the next sample query:

1
2
3

SELECT   ordinal_english_text
,        ordinal_old_english_text
FROM     ordinal_english INNER JOIN ordinal_old_english USING(ordinal_english_id);

This returns the following rows from the tables, where the primary keys match each other:

ORDINAL_ENGLISH_TEXT ORDINAL_OLD_ENGLISH_TEXT
-------------------- ------------------------
One                  Fyrmest
Three                Pridda
Four                 Feorda
Five                 Fifta
Six                  Siexta

As mentioned, you can put more than one column name in a USING subclause. There may be an implementation limit to the number of columns but I’ve never bothered to discover it. It appears that the parser may treat the list recursively. Generally, we don’t care how the parser handles this because it doesn’t affect behavior or throughput. The syntax you would use for multiple column is the following:

USING(column_name1, column_name2, ...)

Nested Loop Inner Join Product →

This is an example of how nested loops can produce an intersection of two sets, or an inner join of two sets. This employs a merged join. Merged joins require that you pre-order the arrays that you want to join. These base sets are already organized as ascending ordered sets.

You should notice that a INNER JOIN uses a nested loop to match every return of the inner loop with a return of the outer loop, like the Cartesian example. It differs inside the nested loop by only assigning those values that match, which is the equivalent of an INNER JOIN. Line 22 contains an if statement that matches the column values.

<?php
  // Declare two arrays, two with data and one for the combined data.
  $english = array(array("ENGLISH_ID"=>"1","ENGLISH_TEXT"=>"One")
                  ,array("ENGLISH_ID"=>"2","ENGLISH_TEXT"=>"Two")
                  ,array("ENGLISH_ID"=>"3","ENGLISH_TEXT"=>"Three")
                  ,array("ENGLISH_ID"=>"4","ENGLISH_TEXT"=>"Four")
                  ,array("ENGLISH_ID"=>"5","ENGLISH_TEXT"=>"Five")
                  ,array("ENGLISH_ID"=>"6","ENGLISH_TEXT"=>"Six"));
  $french = array(array("FRENCH_ID"=>"1","FRENCH_TEXT"=>"Un")
                 ,array("FRENCH_ID"=>"3","FRENCH_TEXT"=>"Trois")
                 ,array("FRENCH_ID"=>"4","FRENCH_TEXT"=>"Quatre")
                 ,array("FRENCH_ID"=>"5","FRENCH_TEXT"=>"Cinque")
                 ,array("FRENCH_ID"=>"6","FRENCH_TEXT"=>"Six")
                 ,array("FRENCH_ID"=>"7","FRENCH_TEXT"=>"Sept")
                 ,array("FRENCH_ID"=>"8","FRENCH_TEXT"=>"Huit"));
  $result_set = array();
 
  // The larger array is always the outer loop and inner loop the smaller array.
  for ($i = 0;$i < count($french);$i++)
    for ($j = $i;$j < count($english);$j++)
      // Perform a match for an intersection or inner join of two array sets.
      if ($english[$j]["ENGLISH_ID"] == $french[$i]["FRENCH_ID"])
        // This selects the elements from the cross joined set, like a SELECT clause in SQL.
        $result_set[] = array("ENGLISH_ID" => $english[$j]["ENGLISH_ID"]
                             ,"ENGLISH_TEXT" => $english[$j]["ENGLISH_TEXT"]
                             ,"FRENCH_ID" => $french[$i]["FRENCH_ID"]
                             ,"FRENCH_TEXT" => $french[$i]["FRENCH_TEXT"]);
 
  // Print the result set of the join.
  print_r($result_set);
?>

Assuming you save the program above as leftjoin.php in your file system, you can run it from the command-line interface with the following syntax:

php leftjoin.php

It would print the following 5 array elements. The elements are the result of the intersection or INNER JOIN.

Array
(
    [0] => Array
        (
            [ENGLISH_ID] => 1
            [ENGLISH_TEXT] => One
            [FRENCH_ID] => 1
            [FRENCH_TEXT] => Un
        )
    [1] => Array
        (
            [ENGLISH_ID] => 3
            [ENGLISH_TEXT] => Three
            [FRENCH_ID] => 3
            [FRENCH_TEXT] => Trois
        )
    [2] => Array
        (
            [ENGLISH_ID] => 4
            [ENGLISH_TEXT] => Four
            [FRENCH_ID] => 4
            [FRENCH_TEXT] => Quatre
        )
    [3] => Array
        (
            [ENGLISH_ID] => 5
            [ENGLISH_TEXT] => Five
            [FRENCH_ID] => 5
            [FRENCH_TEXT] => Cinque
        )
    [4] => Array
        (
            [ENGLISH_ID] => 6
            [ENGLISH_TEXT] => Six
            [FRENCH_ID] => 6
            [FRENCH_TEXT] => Six
        )
)

You could improve the match by changing the logic from a nested loop to a Blocked Nested Loop (BNL), like the following program unit. There are also many more robust algorithms, that improve search and match operations.

This particular example uses a resolution between three copies of the same set to illustrate how the outer loop only navigates once while each inner loop processes one-time for each element in the outer loop.

<?php
  // Declare two arrays, two with data and one for the combined data.
  $su = array(array("SYSTEM_USER_ID"=>1,"SYSTEM_USER_NAME"=>"SYSADMIN","CREATED_BY"=>1,"LAST_UPDATED_BY"=>1)
             ,array("SYSTEM_USER_ID"=>2,"SYSTEM_USER_NAME"=>"DBA 1","CREATED_BY"=>1,"LAST_UPDATED_BY"=>2)
             ,array("SYSTEM_USER_ID"=>3,"SYSTEM_USER_NAME"=>"DBA 2","CREATED_BY"=>1,"LAST_UPDATED_BY"=>1)
             ,array("SYSTEM_USER_ID"=>4,"SYSTEM_USER_NAME"=>"DBA 3","CREATED_BY"=>1,"LAST_UPDATED_BY"=>3)
             ,array("SYSTEM_USER_ID"=>5,"SYSTEM_USER_NAME"=>"APPS DBA","CREATED_BY"=>4,"LAST_UPDATED_BY"=>4));
 
  // Assign aliases to the single array set.
  $su1 = $su;
  $su2 = $su;
  $su3 = $su;
 
  // In self join they're all equal.
  for ($i = 0;$i < count($su);$i++) {
    for ($j = 0;$j < count($su);$j++)
      // Perform a match for an intersection or inner join of the two sets on a primary to foreign key match.
      if ($su2[$j]["SYSTEM_USER_ID"] == $su1[$i]["CREATED_BY"])
        // This selects the elements from the joined set, like the SELECT clause in SQL.
        $result_set[$i] = "[".$su1[$i]["SYSTEM_USER_ID"]."] [".$su1[$i]["SYSTEM_USER_NAME"]."] [".$su2[$j]["SYSTEM_USER_NAME"]."]";
    for ($j = 0;$j < count($su);$j++)
      // Perform a match for an intersection or inner join of the two sets on a primary to foreign key match.
      if ($su3[$j]["SYSTEM_USER_ID"] == $su1[$i]["LAST_UPDATED_BY"])
        // This selects the elements from the joined set, like the SELECT clause in SQL.
        $result_set[$i] .= " [".$su3[$j]["SYSTEM_USER_NAME"]."]"; }
 
  // Print the result set of the join.
  print_r($result_set);
  ?>

The results are not shown but you can test it on your own to see how it works.

Written by michaelmclaughlin

January 17th, 2010 at 10:24 pm

Posted in

Database Tutorial

Inner Join

ANSI SQL:89

ANSI SQL:92

Leave a Reply

My Technical Blog

Tutorial Pages

Blogroll