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,

lastname,

addressLine1

 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
Deleting Objects You can use the DELETE statement to eradicate objects from an object table. To eradicate objects selectively, you use the WHERE clause, as shown below: BEG

Accessing Attributes: You can refer to an attribute only by its name not by its position in the object type. To access or modify the value of an attribute, you can use the dot

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

heap sort program in pl/sql

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN