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

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

Example of GROUPBY Operator Example: How many students sat each exam, using GROUP BY, NATURAL LEFT JOIN, and COALESCE SELECT CourseId, COALESCE (n, 0) AS n FROM COURS

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla