Left and right joins, PL-SQL Programming

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....). While RIGHT OUTER JOIN is used to retrieve the data the from all other tables (table_2, table_3...) even if there is no match in the main table. As an example, in our classicalmodels sample database, when the company wants to establish a new office, the SQL script to insert a new office to the databse as follows:

INSERT INTO classicmodels.offices

(officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory)

VALUES  ('8', 'Boston', '+1 215 837 0825', '1550 dummy street', 'dummy address', 'MA', 'USA', '02107', 'NA')

At this time, the company hasn't hired any new employees yet, so the new office does not have any employees. If we want to know which employees belong to what offices and all the offices of the company, we can use RIGHT JOIN as follows:

SELECT firstname,



 FROM employees as e  RIGHT OUTER JOIN offices as o

 ON o.officeCode = e.officeCode

The Right join on offices will cause all records from the offices table (the table on the right in the join stmt) to display, even if there is not a corresponding record in the employees table. Notice the last record contains null values for the first and last name values pulled from employees table. This is because there are no records in the employees table with an office code of 8 (the boston office we just added to the office table.)


| firstname | lastname  | addressLine1             |


| Mary      | Patterson | 100 Market Street        |

| Diane     | Murphy    | 100 Market Street        |

| Jeff      | Firrelli  | 100 Market Street        |

| Anthony   | Bow       | 100 Market Street        |

| Leslie    | Jennings  | 100 Market Street        |

| Leslie    | Thompson  | 100 Market Street        |

| Julie     | Firrelli  | 1550 Court Place         |

| Steve     | Patterson | 1550 Court Place         |

| FoonYue  | Tseng     | 523 East 53rd Street     |

| George    | Vanauf    | 523 East 53rd Street     |

| Gerard    | Bondur    | 43 Rue JouffroyD'abbans |

| Loui      | Bondur    | 43 Rue JouffroyD'abbans |

| Gerard    | Hernandez | 43 Rue JouffroyD'abbans |

| Pamela    | Castillo  | 43 Rue JouffroyD'abbans |

| Martin    | Gerard    | 43 Rue JouffroyD'abbans |

| Mami      | Nishi     | 4-1 Kioicho              |

| Yoshimi   | Kato      | 4-1 Kioicho              |

| William   | Patterson | 5-11 Wentworth Avenue    |

| Andy      | Fixter    | 5-11 Wentworth Avenue    |

| Peter     | Marsh     | 5-11 Wentworth Avenu    |

| Tom       | King      | 5-11 Wentworth Avenue    |

| Larry     | Bott      | 25 Old Broad Street      |

| Barry     | Jones     | 25 Old Broad Street      |

| NULL      | NULL      | 1550 dummy street        |


24 rows in set (0.00 sec)

As you can see, the RIGHT JOIN get the all the data from second table (offices) and data from the first table even the condition does not match.

Posted Date: 2/16/2013 8:17:36 AM | Location : United States

Related Discussions:- Left and right joins, Assignment Help, Ask Question on Left and right joins, Get Answer, Expert's Help, Left and right joins Discussions

Write discussion on Left and right joins
Your posts are moderated
Related Questions
Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

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

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

which operation is used if we are interested in only certain columns of a table?

Write a cursor to open an employee database and fetch the employee record whose age is greater than 45

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

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal