Added line is this color
Deleted line is this color
- -<p>SQL JOINS</p> -<hr> -<br> -<ul> -<li> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><strong><span style="font-size: 10pt">SQL -JOINS</span></strong></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">A join combines records from two tables -in a relational database and results in a new (temporary) table, also called -joined table. In the Structured Query Language (SQL), there are two types of -joins: <strong>inner and outer.</strong></font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">As a special case, a table (base table, -view, or joined table) can be joined with itself again. This is called -<strong>self-join</strong>.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Mathematically, join is relation -composition, the fundamental operation in relational algebra, and generalizing -function composition.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">We will use these two tables to show the -join examples</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Table: -<strong>Department</strong></font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<table style="width: 259px; height: 129px" cellspacing="1" cellpadding="1" -width="259" border="1"> -<caption><font face="Verdana" size="2">Department</font></caption> -<tbody> -<tr> -<td><font face="Verdana" size="2"><strong>Department</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -<tr> -<td></td> -<td></td> -</tr> -</tbody> -</table> -</div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Table: -<strong>Employee</strong></font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<table style="width: 256px; height: 164px" cellspacing="1" cellpadding="1" -width="256" border="1"> -<caption><font face="Verdana" size="2">Employee</font></caption> -<tbody> -<tr> -<td><font face="Verdana" size="2"><strong>LastName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -</tr> -</tbody> -</table> -<strong><span style="font-size: 10pt"><font face="Verdana">INNER -JOIN</font></span></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><font face= -"Verdana"><span style="font-size: 10pt">An inner join essentially finds the -intersection between the two tables. This is the most common type of join used, -and is considered the default join type.</span> <font size="2"><span style= -"font-size: 9pt">Inner joins return all rows from multiple tables where the -join condition is met.</span></font></font></div> -<div style="margin: 0in 0in 0pt"><span style="font-size: 10pt"><font face= -"Verdana">The join example below takes all the records from table A (in this -case, employee) and finds the matching record(s) in table B (department) based -on the join predicate. If no match is found, the record from A is not included -in the joined table. If multiple results are found in B that match the -predicate (condition given), then one row will be returned for each (the values -from A will be repeated).</font></span></div> -<div style="margin: 0in 0in 0pt"></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">FROM</span> <font size="2"><span style= -"font-size: 10pt">Employee <span style="color: gray">INNER</span> <span style= -"color: gray">JOIN</span> Department</span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">ON</span> <font size="2"><span style= -"font-size: 10pt">Employee<span style="color: gray">.</span>DepartmentID -<span style="color: gray">=</span> Department<span style= -"color: gray">.</span>DepartmentID</span></font></font></div> -<div style="margin: 0in 0in 0pt"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><em><span style="font-size: 10pt"><font face= -"Verdana">Result:</font></span></em></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<table style="width: 554px; height: 141px" cellspacing="1" cellpadding="1" -width="554" border="1"> -<tbody> -<tr> -<td><font face="Verdana" size="2"><strong>LastName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentId</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -</tbody> -</table> -</div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Notice that employee -<strong>Jasper</strong> and department Marketing do not appear. Neither of -these records have accompanying rows in their associative tables, and are thus -omitted from the inner join result.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Example of an implicit inner -join:</font></span></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">FROM</span> <font size="2"><span style= -"font-size: 10pt">Employee<span style="color: gray">,</span> -Department</span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">WHERE</span> <font size="2"><span style= -"font-size: 10pt">Employee<span style="color: gray">.</span>DepartmentID -<span style="color: gray">=</span> Department<span style= -"color: gray">.</span>DepartmentID</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><span style="font-size: 10pt"><font face="Verdana">CROSS -JOIN</font></span></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">While not used very commonly, a cross -join is the foundation upon which inner joins are built. A cross join returns -the cartesian product of the sets of rows from the joined tables. Thus, it is -an inner join where the join condition always evaluates to -True.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">If A and B are two sets then cross join -= A X B.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">The SQL code for a cross join lists the -tables to be joined (FROM), but does not include any filtering join predicate -(WHERE).</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Example of an explicit cross -join:</font></span></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">FROM</span> <font size="2"><span style= -"font-size: 10pt">Employee <span style="color: gray">CROSS</span> <span style= -"color: gray">JOIN</span> department</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Example of an implicit corss -join:</font></span></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><font face= -"Verdana"><span style="font-size: 10pt; color: blue">FROM</span> <font size= -"2"><span style="font-size: 10pt">employee<span style="color: gray">,</span> -department</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><em><span style="font-size: 10pt"><font face= -"Verdana">Resule:</font></span></em></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<table style="width: 545px; height: 578px" cellspacing="1" cellpadding="1" -width="545" border="1"> -<tbody> -<tr> -<td><font face="Verdana" size="2"><strong>LastName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentId</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -</tbody> -</table> -</div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">As you can see the cross join does not -apply any predicate when matching records for the joined table. These joins are -almost never used, except to generate all possible combinations of records from -tables that do not share a common element. Still, the results of a cross joins -can be further filtered.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><span style="font-size: 10pt"><font face="Verdana">OUTER -JOINS</font></span></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Outer joins are subdivided further into -left outer joins, right outer joins, and full outer joins.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><span style="font-size: 10pt"><font face="Verdana">LEFT OUTER -JOIN</font></span></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">A left outer join is very different from -an inner join. Instead of limiting results to those in both tables, it limits -results to those in the "left" table (A). This means that if the ON clause -matches 0 records in B, a row in the result will still be returned but with -NULL in each column from B.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><font face= -"Verdana"><span style="font-size: 10pt">This type of join returns all rows from -one table and <strong>only</strong> those rows from a secondary table where the -joined fields are equal (join condition is met)</span><span style= -"font-size: 9pt"><font size="2">.</font></span></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">A left outer join returns all the values -from left table + matched values from right table (or NULL in case of no -matching value).</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">For example, this allows us to find the -employee's departments, but still show the employee even when their department -is NULL or does not exist (contrary to the inner join example above, where -employees in non-existent departments were ignored).</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Example of a left outer -join:</font></span></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">FROM</span> <font size="2"><span style= -"font-size: 10pt">Employee <span style="color: gray">LEFT</span> <span style= -"color: gray">OUTER</span> <span style="color: gray">JOIN</span> -Department</span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">ON</span> <font size="2"><span style= -"font-size: 10pt">Employee<span style="color: gray">.</span>DepartmentID -<span style="color: gray">=</span> Department<span style= -"color: gray">.</span>DepartmentID</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><em><span style="font-size: 10pt"><font face= -"Verdana">Result:</font></span></em></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<table style="width: 511px; height: 150px" cellspacing="1" cellpadding="1" -width="511" border="1"> -<tbody> -<tr> -<td><font face="Verdana" size="2">LastName</font></td> -<td><font face="Verdana" size="2">DepartmentID</font></td> -<td><font face="Verdana" size="2">Departmentname</font></td> -<td><font face="Verdana" size="2">DepartmentID</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -<td><font face="Verdana" size="2">NULL</font></td> -<td><font face="Verdana" size="2">NULL</font></td> -</tr> -</tbody> -</table> -</div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Notice that for <strong>Jasper</strong> -DepartmentName and DepartmentId are NULL.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><span style="font-size: 10pt"><font face="Verdana">RIGHT OUTER -JOIN</font></span></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">A right outer join is much like a left -outer join, except that the tables are reversed. Every record from the right -side, B, will be returned, and NULL will be returned for columns from A for -those rows that have no matching record in A.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">A right outer join returns all the -values from right table + matched values from left table (or NULL in case of no -matching value).</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><br> -<font face="Verdana">Example right outer join:</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">FROM</span> <font size="2"><span style= -"font-size: 10pt">Employee <span style="color: gray">RIGHT</span> <span style= -"color: gray">OUTER</span> <span style="color: gray">JOIN</span> -Department</span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">ON</span> <font size="2"><span style= -"font-size: 10pt">Employee<span style="color: gray">.</span>DepartmentID -<span style="color: gray">=</span> Department<span style= -"color: gray">.</span>DepartmentID</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><em><span style="font-size: 10pt"><font face= -"Verdana">Result:</font></span></em></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<table style="width: 508px; height: 150px" cellspacing="1" cellpadding="1" -width="508" border="1"> -<tbody> -<tr> -<td><font face="Verdana" size="2"><strong>LastName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -<td><font face="Verdana" size="2"><strong>Departmentname</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">NULL</font></td> -<td><font face="Verdana" size="2">NULL</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -</tbody> -</table> -</div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><font face= -"Verdana"><span style="font-size: 10pt">Notice</span> <font size= -"2"><span style="font-size: 10pt">that LastName and DepartmentID are NULL for -<strong>Marketing</strong> Department</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><span style="font-size: 10pt"><font face="Verdana">FULL OUTER -JOIN</font></span></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">A full outer join combines the results -of both left and right outer joins. These joins will show records from both -tables, and fill in NULLs for missing matches on either -side.</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Some database systems do not support -this functionality, but it can be emulated through the use of left and right -outer joins and unions (see below).</font></span></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><span style= -"font-size: 10pt"><font face="Verdana">Example full outer -join:</font></span></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">SELECT</span> <font size="2"><span style= -"font-size: 10pt"><span style="color: gray">*</span></span></font></font></div> -<div style="margin: 0in 0in 0pt"><font face="Verdana"><span style= -"font-size: 10pt; color: blue">FROM</span> <font size="2"><span style= -"font-size: 10pt">Employee <span style="color: blue">FULL</span> <span style= -"color: gray">OUTER</span> <span style="color: gray">JOIN</span> -Department</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"><font face= -"Verdana"><span style="font-size: 10pt; color: blue">ON</span> <font size= -"2"><span style="font-size: 10pt">Employee<span style= -"color: gray">.</span>DepartmentID <span style="color: gray">=</span> -Department<span style= -"color: gray">.</span>DepartmentID</span></font></font></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<strong><em><span style="font-size: 10pt"><font face= -"Verdana">Result:</font></span></em></strong></div> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"> -<div style="margin: 0in 0.05in 7.5pt 0in; vertical-align: top"></div> -<div> -<table style="width: 507px; height: 171px" cellspacing="1" cellpadding="1" -width="507" border="1"> -<tbody> -<tr> -<td><font face="Verdana" size="2"><strong>LastName</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -<td><font face="Verdana" size="2"><strong>Departmentname</strong></font></td> -<td><font face="Verdana" size="2"><strong>DepartmentID</strong></font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Rafferty</font></td> -<td><font face="Verdana" size="2">1</font></td> -<td><font face="Verdana" size="2">Sales</font></td> -<td><font face="Verdana" size="2">1</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jones</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Steinberg</font></td> -<td><font face="Verdana" size="2">2</font></td> -<td><font face="Verdana" size="2">Engineering</font></td> -<td><font face="Verdana" size="2">2</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Robinson</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Smith</font></td> -<td><font face="Verdana" size="2">3</font></td> -<td><font face="Verdana" size="2">Clerical</font></td> -<td><font face="Verdana" size="2">3</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">Jasper</font></td> -<td><font face="Verdana" size="2">7</font></td> -<td><font face="Verdana" size="2">NULL</font></td> -<td><font face="Verdana" size="2">NULL</font></td> -</tr> -<tr> -<td><font face="Verdana" size="2">NULL</font></td> -<td><font face="Verdana" size="2">NULL</font></td> -<td><font face="Verdana" size="2">Marketing</font></td> -<td><font face="Verdana" size="2">4</font></td> -</tr> -</tbody> -</table> -</div> -<div><span style="font-size: 10pt"><font face="Verdana">Notice the NULL Values -for <strong>Jasper</strong> LastName and <strong>Marketing</strong> -DepartmentName.</font></span></div> -</div> -</div> -</div> -</div> -</li> -</ul> - +Hi webmaster!