Difference between Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql 2013

View previous topic View next topic Go down

Difference between Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql 2013

Post by Admin on Sun Sep 15, 2013 4:02 am

2013 2013 2013 <div dir="ltr" style="text-align: left;" trbidi="on">One of the most common interview questions that is being asked is explain Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql. I will break the question in different meaningful easy to understand concepts.</br></br> <b>What is sql join?</b></br>Join, as the name suggests, is used to combine data from two tables based on some common features(columns). The resulting data is the temporary table created by comparing two tables.</br></br>Consider the below tables</br><u><b>Employee</b></u></br><table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">EmpId</th><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">EmpName</th></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">1</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">John</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">2</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">David</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td></tr></table> <u><b>Area</b></u></br><table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">AreaId</th><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">AreaName</th></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">5</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Australia</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">6</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">England</td></tr></table></br></br><iframe src = 'http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137656142870263007&affid=kumarbhati' frameborder = 0, height=100, width = 580> </iframe></br><u><b>Inner join</b></u></br>An inner join between two tables give the intersection of table 1 and table 2. It returns the rows that gets matched based on the join predicate i.e. it will return the row where the join condition gets satisfied.</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />Inner Join Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr></table><hr /><hr /><hr /><hr /> <u><b>Left outer join</b></u></br>Left outer join gives all the rows that matches between the two tables, plus all the rows from left table with corresponding null values from right table. This is inner join + rows from left table with corresponding null values from right table.</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />LEFT OUTER JOIN Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">1</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">John</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">2</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">David</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr></table><hr /><hr /><hr /><hr /> <u><b>Right outer join</b></u></br>Right outer join gives all the rows that matches between the two tables, plus all the rows from right table with corresponding null values from left table. This is inner join + rows from right table with corresponding null values from left table.</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />RIGHT OUTER JOIN Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">5</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Australia</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">6</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">England</td></tr></table> <hr /><hr /><hr /><hr /> <u><b>Full outer join</b></u></br>Full outer join gives union of two tables i.e. Left outer join + right outer join, and for unmatched rows, all the rows from right table with corresponding null values from left table, and all the rows from left table with corresponding null values from right table</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />FULL OUTER JOIN Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">1</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">John</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">2</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">David</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">5</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Australia</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">6</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">England</td></tr></table> </div><br> 2013 2013 2013 <br><div dir="ltr" style="text-align: left;" trbidi="on">One of the most common interview questions that is being asked is explain Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql. I will break the question in different meaningful easy to understand concepts.</br></br> <b>What is sql join?</b></br>Join, as the name suggests, is used to combine data from two tables based on some common features(columns). The resulting data is the temporary table created by comparing two tables.</br></br>Consider the below tables</br><u><b>Employee</b></u></br><table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">EmpId</th><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">EmpName</th></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">1</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">John</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">2</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">David</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td></tr></table> <u><b>Area</b></u></br><table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">AreaId</th><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">AreaName</th></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">5</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Australia</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">6</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">England</td></tr></table></br></br><iframe src = 'http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137656142870263007&affid=kumarbhati' frameborder = 0, height=100, width = 580> </iframe></br><u><b>Inner join</b></u></br>An inner join between two tables give the intersection of table 1 and table 2. It returns the rows that gets matched based on the join predicate i.e. it will return the row where the join condition gets satisfied.</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />Inner Join Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr></table><hr /><hr /><hr /><hr /> <u><b>Left outer join</b></u></br>Left outer join gives all the rows that matches between the two tables, plus all the rows from left table with corresponding null values from right table. This is inner join + rows from left table with corresponding null values from right table.</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />LEFT OUTER JOIN Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">1</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">John</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">2</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">David</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr></table><hr /><hr /><hr /><hr /> <u><b>Right outer join</b></u></br>Right outer join gives all the rows that matches between the two tables, plus all the rows from right table with corresponding null values from left table. This is inner join + rows from right table with corresponding null values from left table.</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />RIGHT OUTER JOIN Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">5</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Australia</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">6</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">England</td></tr></table> <hr /><hr /><hr /><hr /> <u><b>Full outer join</b></u></br>Full outer join gives union of two tables i.e. Left outer join + right outer join, and for unmatched rows, all the rows from right table with corresponding null values from left table, and all the rows from left table with corresponding null values from right table</br> <pre style="font-family:arial;font-size:12px;border:1px solid #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;">Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp<br />FULL OUTER JOIN Area area on area.areaId = emp.EmpId<br /></code></pre> Result:</br> <table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;"><tr><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">emp.EmpName</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaId</td><th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">area.AreaName</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">1</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">John</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">2</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">David</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Peter</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">3</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">New York</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Eric</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">4</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Canada</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">5</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">Australia</td></tr><tr><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">NULL</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">6</td><td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;">England</td></tr></table> </div><br>2013 2013 2013 <br> <a href="http://www.matrixar.com/" title="Matrix ">المصفوفة : أجمل الخلفيات والصور</a>

Admin
Admin

Posts : 64122
Join date : 2013-02-22

View user profile http://prowebsites1.forumaroc.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum