Oracle Inner JOIN

This SQL tutorial focuses on the Oracle Inner Join statement, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts describing how to use the JOIN statement in Oracle. To read additional posts regarding this subject, please use the following links:

• Oracle Inner JOIN – Joining data items from tables, based on values common to both tables.
• Oracle Outer JOIN– Joining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table.
• Oracle Self JOIN– Join a table to itself.

Oracle Inner JOIN

Sometimes you may need to use data from more than one table. In this illustration, the result set displays information from two separate tables: product name from Products table, supplier name from Suppliers table.

• Multiple tables (two or more tables) can be linked only if they have common values (in this case, supplier number) or a logical connection of some kind.
• Relating between two tables requires you to determine the join condition. In the example shown above, the join condition was based on the equality operator (=).

Joining Two Tables

SELECT table_a.column_name , table_b.column_name, table_a.column_name ..
FROM   table_a , table_b
WHERE  table_a.column_name = table_b.column_name

For example, we would like to retrieve data from two tables – Products and Suppliers. We would like to retrieve the product’s name and price from the Products table and the supplier’s name from the Suppliers table:

SELECT products.product_name , products.price,
       suppliers.supplier_name
FROM   products , suppliers
WHERE  products.supplier_id = suppliers.supplier_id

• In the Oracle SELECT clause, precede the column name with the table name for clarity.
• When a column is common to both tables, it must be prefixed with the table name.
• In the Oracle FROM clause, you need to specify the tables from which you would like to retrieve the data. These tables are specified with comma (,) between them.
• After the WHERE keyword, specify the join condition.
• To determine the relation between Products and Suppliers tables – values in the supplier_id column on both tables must be equal. This type of relation is referred as an Equi Join.
• Equi joins are also called Simple Joins or Inner Joins.
• Frequently, this relation involves primary key and foreign key complements.

Oracle Table Aliases

Qualifying column names with table names can be time consuming, and may result in a very long, unreadable query. In Oracle, Instead of writing a full table name after each column, use Table Aliases. Just as Column Alias gives a column another name, a table alias gives a table another name.

Let us repeat the task shown in the previous example, this time by using Table Aliases:

SELECT prd.product_name , prd.price , sup.supplier_name
FROM   products prd ,suppliers sup
WHERE  prd.supplier_id = sup.supplier_id

• Use the Oracle FROM clause to define the table aliases.
• Write an alias after each table name.
• You can assign any alias to a table (for example, you can assign the letter A to the Products table); however, it is advisable to assign meaningful aliases.
• After defining an alias to a table, that alias must be substituted for the table name throughout the Oracle SELECT statement. All explicit references to the table must use the table alias, not the table name.
• The table alias is valid only for the current Oracle SELECT statement.

Adding Additional Clauses

you can carry on adding additional clauses to your Oracle SELECT statement (restricting the rows returned, adding aggregations, defining the sorting order and so on).

Below are several Oracle examples:

This Oracle example would retrieve all products whose price is greater than 60 and their supplier number equals 90:

SELECT prd.product_name , prd.price , sup.supplier_name
FROM   products prd , suppliers sup
WHERE  prd.supplier_id = sup.supplier_id
AND prd.supplier_id = 90
AND prd.price > 60

You can change the sorting order, thereby displaying the data items sorted by the product’s price:

SELECT  prd.product_name , prd.price , sup.supplier_name
FROM   products prd JOIN suppliers sup
WHERE  prd.supplier_id = sup.supplier_id
AND prd.supplier_id = 90
AND prd.price > 60 
ORDER BY prd.price DESC

Joining an additional table requires us to:

• Specify the table name in the Oracle FROM clause (by separating one table from another by comma).
• Determine the additional join condition, and specify additional join condition in the Oracle WHERE clause.

SELECT prd.product_name , prd.price , sup.supplier_name, reg.region_name
FROM  products prd , suppliers sup , regions reg
WHERE prd.supplier_id = sup.supplier_id
AND
      sup.region_id = reg.region_id

• The same concept applies to joining four tables or more – adding the table name at the Oracle FROM clause, and specifying additional join condition at the Oracle WHERE clause.

None Equi Join

A Oracle None Equi Join is a join condition containing something other than equality operator. For example: sometimes you would like to join two tables that do not have a shared column, and seemingly have no join condition:

What is product number 4 (Sour Patch Marshmallows) price grade? in this case there is no shared column, but you can tell that it belongs to price grade: D , because when comparing its price to the data found in the Price Grades table, it turned out that its price is between Min price (31) and Max price (40) of grade D.

This relation is obtained using the Oracle BETWEEN operator :

SELECT prd.product_name , prd.price , pg.grade_level
FROM   products prd , price_grades pg
WHERE prd.price BETWEEN pg.lowest_sal AND pg.highest_sal

You can join multiple tables (two or more) as long as you can find any logical condition that can be used for joining these tables.
 

Oracle Cartesian Product

In Oracle Cartesian Product is a join that retrieves back all of the possible combinations between the tables; each row in the first table is paired with each of the rows in the second table. A cartesian product tends to generate a large number of rows, and it’s results are rarely useful.

SELECT prd.product_name, sup.supplier_name
FROM  products prd , suppliers sup