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
Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Seeking a programmer to design a legal document with pre-existing fields that could allow the auto-population of client(s) information (i.e. Name, Account Number, Address etc.) int

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Data Types in SQL - Decimal DECIMAL, NUMERIC, REAL, FLOAT and various other terms for various sets of rational numbers. When these key words are specified for the declared typ

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate so

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke