Using inner join, PL-SQL Programming

Using INNER JOIN

INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows are not returned. For example, see the following tables in our classicmodels sample database. We have employees table and offices table. Two tables are linked together through the keyed column officeCode. OfficeCode is the primary key of the offices table and is a foreign key in the employees table. To find out what employees work in which countries and states we can use INNER JOIN to join the offices and employees table. We return columns from both tables (note the fully qualified column names like "employees.firstname"), but only the rows where the officeCode in each of the tables match. Here is the SQL code:

SELECT employees.firstname,

employees.lastname,

offices.country,

offices.state

FROM employees INNER JOIN offices

  ON offices.officeCode = employees.officeCode

And we will get the data like this:

+-----------+-----------+-----------+------------+

| firstname | lastname  | country   | state      |

+-----------+-----------+-----------+------------+

| Diane     | Murphy    | USA       | CA         |

| Mary      | Patterson | USA       | CA         |

| Jeff      | Firrelli  | USA       | CA         |

| William   | Patterson | Australia | NULL       |

| Gerard    | Bondur    | France    | NULL       |

| Anthony   | Bow       | USA       | CA         |

| Leslie    | Jennings  | USA       | CA         |

| Leslie    | Thompson  | USA       | CA         |

| Julie     | Firrelli  | USA       | MA         |

| Steve     | Patterson | USA       | MA         |

| FoonYue  | Tseng     | USA       | NY         |

| George    | Vanauf    | USA       | NY         |

| Loui      | Bondur    | France    | NULL       |

| Gerard    | Hernandez | France    | NULL       |

| Pamela    | Castillo  | France    | NULL       |

| Larry     | Bott      | UK        | NULL       |

| Barry     | Jones     | UK        | NULL       |

| Andy      | Fixter    | Australia | NULL       |

| Peter     | Marsh     | Australia | NULL       |

| Tom       | King      | Australia | NULL       |

| Mami      | Nishi     | Japan     | Chiyoda-Ku |

| Yoshimi   | Kato      | Japan     | Chiyoda-Ku |

| Martin    | Gerard    | France    | NULL       |

+-----------+-----------+-----------+------------+

23 rows in set (0.02 sec)

We could make the above query return more specific results. What if we want to find only employees in USA? The following query adds an AND clause to narrow down the country:

SELECT e.firstname,

e.lastname,

state

FROM employees e INNER JOIN offices o

  ON o.officeCode = e.officeCode

WHERE  country = 'USA' ;

Here is the resulting data

+-----------+-----------+-------+

| firstname | lastname  | state |

+-----------+-----------+-------+

| Diane     | Murphy    | CA    |

| Mary      | Patterson | CA    |

| Jeff      | Firrelli  | CA    |

| Anthony   | Bow       | CA    |

| Leslie    | Jennings  | CA    |

| Leslie    | Thompson  | CA    |

| Julie     | Firrelli  | MA    |

| Steve     | Patterson | MA    |

| FoonYue  | Tseng     | NY    |

| George    | Vanauf    | NY    |

+-----------+-----------+-------+

10 rows in set (0.00 sec)

Posted Date: 2/16/2013 8:21:32 AM | Location : United States







Related Discussions:- Using inner join, Assignment Help, Ask Question on Using inner join, Get Answer, Expert's Help, Using inner join Discussions

Write discussion on Using inner join
Your posts are moderated
Related Questions
EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th