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
Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin

Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM