<p>Also, how do <code>LEFT OUTER JOIN</code>, <code>RIGHT OUTER JOIN</code>, and <code>FULL OUTER JOIN</code> fit in?</p>

[{'answer_id': 38552, 'author': '1800 INFORMATION', 'author_id': 3146, 'author_profile': 'https://Stackoverflow.com/users/3146', 'pm_score': 7, 'selected': False, 'text': '<p>A inner join only shows rows if there is a matching record on the other (right) side of the join.</p>\n\n<p>A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.</p>\n'}, {'answer_id': 38565, 'author': 'Brian Boatright', 'author_id': 3747, 'author_profile': 'https://Stackoverflow.com/users/3747', 'pm_score': 7, 'selected': False, 'text': '<p>Inner joins require that a record with a related ID exist in the joined table.</p>\n\n<p>Outer joins will return records for the left side even if nothing exists for the right side.</p>\n\n<p>For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".</p>\n\n<p><strong>Orders</strong></p>\n\n<ul>\n<li>OrderID</li>\n<li>CustomerName</li>\n</ul>\n\n<p><strong>OrderDetails</strong></p>\n\n<ul>\n<li>OrderDetailID</li>\n<li>OrderID</li>\n<li>ProductName</li>\n<li>Qty</li>\n<li>Price</li>\n</ul>\n\n<p>The request</p>\n\n<pre><code>SELECT Orders.OrderID, Orders.CustomerName\n  FROM Orders \n INNER JOIN OrderDetails\n    ON Orders.OrderID = OrderDetails.OrderID\n</code></pre>\n\n<p>will only return Orders that also have something in the OrderDetails table.</p>\n\n<p>If you change it to OUTER LEFT JOIN</p>\n\n<pre><code>SELECT Orders.OrderID, Orders.CustomerName\n  FROM Orders \n  LEFT JOIN OrderDetails\n    ON Orders.OrderID = OrderDetails.OrderID\n</code></pre>\n\n<p>then it will return records from the Orders table even if they have no OrderDetails records.</p>\n\n<p>You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like <code>WHERE OrderDetails.OrderID IS NULL</code>.</p>\n'}, {'answer_id': 38578, 'author': 'Mark Harrison', 'author_id': 116, 'author_profile': 'https://Stackoverflow.com/users/116', 'pm_score': 14, 'selected': True, 'text': '<p>Assuming you\'re joining on columns with no duplicates, which is a very common case:</p>\n<ul>\n<li><p>An inner join of A and B gives the result of A intersect B, i.e. the inner part of a <a href="http://en.wikipedia.org/wiki/Venn_diagram" rel="noreferrer">Venn diagram</a> intersection.</p>\n</li>\n<li><p>An outer join of A and B gives the results of A union B, i.e. the outer parts of a <a href="http://en.wikipedia.org/wiki/Venn_diagram" rel="noreferrer">Venn diagram</a> union.</p>\n</li>\n</ul>\n<p><strong>Examples</strong></p>\n<p>Suppose you have two tables, with a single column each, and data as follows:</p>\n<pre><code>A    B\n-    -\n1    3\n2    4\n3    5\n4    6\n</code></pre>\n<p>Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.</p>\n<p><strong>Inner join</strong></p>\n<p>An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.</p>\n<pre><code>select * from a INNER JOIN b on a.a = b.b;\nselect a.*, b.*  from a,b where a.a = b.b;\n\na | b\n--+--\n3 | 3\n4 | 4\n</code></pre>\n<p><strong>Left outer join</strong></p>\n<p>A left outer join will give all rows in A, plus any common rows in B.</p>\n<pre><code>select * from a LEFT OUTER JOIN b on a.a = b.b;\nselect a.*, b.*  from a,b where a.a = b.b(+);\n\na |  b\n--+-----\n1 | null\n2 | null\n3 |    3\n4 |    4\n</code></pre>\n<p><strong>Right outer join</strong></p>\n<p>A right outer join will give all rows in B, plus any common rows in A.</p>\n<pre><code>select * from a RIGHT OUTER JOIN b on a.a = b.b;\nselect a.*, b.*  from a,b where a.a(+) = b.b;\n\na    |  b\n-----+----\n3    |  3\n4    |  4\nnull |  5\nnull |  6\n</code></pre>\n<p><strong>Full outer join</strong></p>\n<p>A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn\'t have a corresponding datum in B, then the B portion is null, and vice versa.</p>\n<pre><code>select * from a FULL OUTER JOIN b on a.a = b.b;\n\n a   |  b\n-----+-----\n   1 | null\n   2 | null\n   3 |    3\n   4 |    4\nnull |    6\nnull |    5\n</code></pre>\n'}, {'answer_id': 3625912, 'author': 'naga', 'author_id': 437795, 'author_profile': 'https://Stackoverflow.com/users/437795', 'pm_score': 6, 'selected': False, 'text': '<p><code>INNER JOIN</code> requires there is at least a match in comparing the two tables. For example, table A and table B which implies A ٨ B (A intersection B).</p>\n\n<p><code>LEFT OUTER JOIN</code> and <code>LEFT JOIN</code> are the same. It gives all the records matching in both tables and all possibilities of the left table.</p>\n\n<p>Similarly, <code>RIGHT OUTER JOIN</code> and <code>RIGHT JOIN</code> are the same. It gives all the records matching in both tables and all possibilities of the right table.</p>\n\n<p><code>FULL JOIN</code> is the combination of <code>LEFT OUTER JOIN</code> and <code>RIGHT OUTER JOIN</code> without duplication.</p>\n'}, {'answer_id': 12616294, 'author': 'vijikumar', 'author_id': 1126071, 'author_profile': 'https://Stackoverflow.com/users/1126071', 'pm_score': 6, 'selected': False, 'text': '<p>You use <strong><code>INNER JOIN</code></strong> to return all rows from both tables where there is a match. i.e. In the resulting table all the rows and columns will have values.</p>\n\n<p>In <strong><code>OUTER JOIN</code></strong> the resulting table may have empty columns. Outer join may be either <code>LEFT</code> or <code>RIGHT</code>.</p>\n\n<p><strong><code>LEFT OUTER JOIN</code></strong> returns all the rows from the first table, even if there are no matches in the second table.</p>\n\n<p><strong><code>RIGHT OUTER JOIN</code></strong> returns all the rows from the second table, even if there are no matches in the first table.</p>\n'}, {'answer_id': 14292739, 'author': 'vidyadhar', 'author_id': 1885368, 'author_profile': 'https://Stackoverflow.com/users/1885368', 'pm_score': 7, 'selected': False, 'text': "<p>In simple words:</p>\n\n<p>An <strong>inner join</strong> retrieve the matched rows only.</p>\n\n<p>Whereas an <strong>outer join</strong> retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:</p>\n\n<ul>\n<li><p><strong>Left</strong>: Matched rows in the right table and all rows in the left table</p></li>\n<li><p><strong>Right</strong>: Matched rows in the left table and all rows in the right table or </p></li>\n<li><p><strong>Full</strong>: All rows in all tables. It doesn't matter if there is a match or not</p></li>\n</ul>\n"}, {'answer_id': 20030933, 'author': 'Lajos Veres', 'author_id': 1665673, 'author_profile': 'https://Stackoverflow.com/users/1665673', 'pm_score': 5, 'selected': False, 'text': '<p>I don\'t see much details about performance and optimizer in the other answers.</p>\n\n<p>Sometimes it is good to know that only <code>INNER JOIN</code> is associative which means the optimizer has the most option to play with it. It can reorder the join order to make it faster keeping the same result. The optimizer can use the most join modes.</p>\n\n<p>Generally it is a good practice to try to use <code>INNER JOIN</code> instead of the different kind of joins. (Of course if it is possible considering the expected result set.)</p>\n\n<p>There are a couple of good examples and explanation here about this strange associative behavior:</p>\n\n<ul>\n<li><a href="https://stackoverflow.com/questions/20022196/are-left-outer-joins-associative">Are left outer joins associative?</a></li>\n<li><a href="https://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql">Does the join order matter in SQL?</a></li>\n</ul>\n'}, {'answer_id': 21380648, 'author': 'Tushar Gupta - curioustushar', 'author_id': 2224265, 'author_profile': 'https://Stackoverflow.com/users/2224265', 'pm_score': 7, 'selected': False, 'text': '<h3>Inner Join</h3>\n<p>Retrieve the matched rows only, that is, <code>A intersect B</code>.</p>\n<p><img src="https://i.stack.imgur.com/Zkk3I.jpg" alt="Enter image description here" /></p>\n<pre><code>SELECT *\nFROM dbo.Students S\nINNER JOIN dbo.Advisors A\n    ON S.Advisor_ID = A.Advisor_ID\n</code></pre>\n<hr>\n<h3>Left Outer Join</h3>\n<p>Select all records from the first table, and any records in the second\ntable that match the joined keys.</p>\n<p><img src="https://i.stack.imgur.com/Z584b.jpg" alt="Enter image description here" /></p>\n<pre><code>SELECT *\nFROM dbo.Students S\nLEFT JOIN dbo.Advisors A\n    ON S.Advisor_ID = A.Advisor_ID\n</code></pre>\n<hr>\n<h3>Full Outer Join</h3>\n<p>Select all records from the second table, and any records in the first\ntable that match the joined keys.</p>\n<p><img src="https://i.stack.imgur.com/c1QF3.jpg" alt="Enter image description here" /></p>\n<pre><code>SELECT *\nFROM dbo.Students S\nFULL JOIN dbo.Advisors A\n    ON S.Advisor_ID = A.Advisor_ID\n</code></pre>\n<hr>\n<h3>References</h3>\n<ul>\n<li><p><em><a href="http://www.datamartist.com/sql-inner-join-left-outer-join-full-outer-join-examples-with-syntax-for-sql-server" rel="noreferrer">Inner and outer joins SQL examples and the Join block</a></em></p>\n</li>\n<li><p><em><a href="http://www.techonthenet.com/sql/joins.php" rel="noreferrer">SQL: JOINS</a></em></p>\n</li>\n</ul>\n'}, {'answer_id': 23008416, 'author': 'Kanwar Singh ', 'author_id': 1859906, 'author_profile': 'https://Stackoverflow.com/users/1859906', 'pm_score': 5, 'selected': False, 'text': '<p><strong>Inner join.</strong> </p>\n\n<p>A join is combining the rows from two tables. An <em>inner join</em> attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match. If a row from the first table in the join matches two rows in the second table, then two rows will be returned in the results. If there’s a row in the first table that doesn’t match a row in the second, it’s not returned; likewise, if there’s a row in the second table that doesn’t match a row in the first, it’s not returned.</p>\n\n<p><strong>Outer Join.</strong> </p>\n\n<p>A <em>left join</em> attempts to find match up the rows from the first table to rows in the second table. If it can’t find a match, it will return the columns from the first table and leave the columns from the second table blank (null).</p>\n'}, {'answer_id': 27458534, 'author': 'Martin Smith', 'author_id': 73226, 'author_profile': 'https://Stackoverflow.com/users/73226', 'pm_score': 10, 'selected': False, 'text': '<p>The Venn diagrams don\'t really do it for me.</p>\n<p>They don\'t show any distinction between a cross join and an inner join, for example, or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate.</p>\n<p>There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway.</p>\n<ol>\n<li>Imagine a cross join.</li>\n<li>Evaluate the <code>on</code> clause against all rows from step 1 keeping those where the predicate evaluates to <code>true</code></li>\n<li>(For outer joins only) add back in any outer rows that were lost in step 2.</li>\n</ol>\n<p>(NB: In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same)</p>\n<p>I\'ll start off with an animated version of a <strong>full outer join</strong>. Further explanation follows.</p>\n<p><a href="https://i.stack.imgur.com/VUkfU.gif" rel="noreferrer"><img src="https://i.stack.imgur.com/VUkfU.gif" alt="enter image description here" /></a></p>\n<hr />\n<h1>Explanation</h1>\n<p><strong>Source Tables</strong></p>\n<p><img src="https://i.stack.imgur.com/LVYKx.png" alt="enter link description here" /></p>\n<p>First start with a <code>CROSS JOIN</code> (AKA Cartesian Product). This does not have an <code>ON</code> clause and simply returns every combination of rows from the two tables.</p>\n<p><strong>SELECT A.Colour, B.Colour FROM A CROSS JOIN B</strong></p>\n<p><img src="https://i.stack.imgur.com/cv3t6.png" alt="enter link description here" /></p>\n<p>Inner and Outer joins have an &quot;ON&quot; clause predicate.</p>\n<ul>\n<li><strong>Inner Join.</strong> Evaluate the condition in the &quot;ON&quot; clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.</li>\n<li><strong>Left Outer Join.</strong> Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns.</li>\n<li><strong>Right Outer Join.</strong> Same as inner join then for any rows in the right table that did not  match anything output these with NULL values for the left table columns.</li>\n<li><strong>Full Outer Join.</strong> Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join.</li>\n</ul>\n<h1>Some examples</h1>\n<p><strong>SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour</strong></p>\n<p>The above is the classic equi join.</p>\n<p><img src="https://i.stack.imgur.com/a8IHd.png" alt="Inner Join" /></p>\n<h2>Animated Version</h2>\n<p><a href="https://i.stack.imgur.com/kZcvR.gif" rel="noreferrer"><img src="https://i.stack.imgur.com/kZcvR.gif" alt="enter image description here" /></a></p>\n<h3>SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (\'Green\',\'Blue\')</h3>\n<p>The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating <code>A.Colour NOT IN (\'Green\',\'Blue\')</code> on each row of the cross join returns.</p>\n<p><img src="https://i.stack.imgur.com/ZwoCi.png" alt="inner 2" /></p>\n<p><strong>SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1</strong></p>\n<p>The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won\'t repeat the picture of the 16 rows again.</p>\n<h3>SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour</h3>\n<p>Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with <code>NULL</code> values for the right hand columns.</p>\n<p><img src="https://i.stack.imgur.com/4bzv2.png" alt="LOJ" /></p>\n<h3>SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL</h3>\n<p>This simply restricts the previous result to only return the rows where <code>B.Colour IS NULL</code>. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table <code>B</code>. This is known as an anti semi join.</p>\n<p>It is important to select a column for the <code>IS NULL</code> test that is either not nullable or for which the join condition ensures that any <code>NULL</code> values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a <code>NULL</code> value for that column in addition to the un matched rows.</p>\n<p><img src="https://i.stack.imgur.com/d6CVF.png" alt="loj is null" /></p>\n<h3>SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour</h3>\n<p>Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.</p>\n<p><img src="https://i.stack.imgur.com/LIOW4.png" alt="ROJ" /></p>\n<h3>SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour</h3>\n<p>Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.</p>\n<p><img src="https://i.stack.imgur.com/iVoqu.png" alt="FOJ" /></p>\n<h3>SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0</h3>\n<p>No rows in the cross join match the <code>1=0</code> predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.</p>\n<p><img src="https://i.stack.imgur.com/gtIhf.png" alt="FOJ 2" /></p>\n<h3>SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0</h3>\n<p>With a minor amend to the preceding query one could simulate a <code>UNION ALL</code> of the two tables.</p>\n<p><img src="https://i.stack.imgur.com/WPu9W.png" alt="UNION ALL" /></p>\n<h3>SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = \'Green\'</h3>\n<p>Note that the <code>WHERE</code> clause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join...</p>\n<p><img src="https://i.stack.imgur.com/4bzv2.png" alt="LOJ" /></p>\n<p>... And then the &quot;Where&quot; clause runs. <code>NULL= \'Green\'</code> does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.</p>\n<p><img src="https://i.stack.imgur.com/tRHdf.png" alt="LOJtoInner" /></p>\n<p>If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be</p>\n<h3>SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = \'Green\'</h3>\n<p><img src="https://i.stack.imgur.com/cvJ1s.png" alt="enter image description here" /></p>\n<h2>SQL Fiddle</h2>\n<p>See these examples <a href="http://sqlfiddle.com/#!17/10d3d/29" rel="noreferrer">run live at SQLFiddle.com</a>.</p>\n'}, {'answer_id': 27540786, 'author': 'ajitksharma', 'author_id': 2778527, 'author_profile': 'https://Stackoverflow.com/users/2778527', 'pm_score': 8, 'selected': False, 'text': '<p><strong>Joins</strong> are used to combine the data from two tables, with the result being a new, temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join.  The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate.\nFor eg- Lets consider Employee and Location table:</p>\n<h3>Employee</h3>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>EmpID</th>\n<th>EmpName</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>13</td>\n<td>Jason</td>\n</tr>\n<tr>\n<td>8</td>\n<td>Alex</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Ram</td>\n</tr>\n<tr>\n<td>17</td>\n<td>Babu</td>\n</tr>\n<tr>\n<td>25</td>\n<td>Johnson</td>\n</tr>\n</tbody>\n</table>\n</div><h3>Location</h3>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>EmpID</th>\n<th>EmpLoc</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>13</td>\n<td>San Jose</td>\n</tr>\n<tr>\n<td>8</td>\n<td>Los Angeles</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Pune, India</td>\n</tr>\n<tr>\n<td>17</td>\n<td>Chennai, India</td>\n</tr>\n<tr>\n<td>39</td>\n<td>Bangalore, India</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p><em><strong>Inner Join:-</strong></em>\nInner join creates a new result table by combining column values of two tables (<em>Employee</em> and <em>Location</em>) based upon the join-predicate. The query compares each row of <em>Employee</em> with each row of <em>Location</em> to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of <em>Employee</em> and <em>Location</em> are combined into a result row.\nHere’s what the SQL for an inner join will look like:</p>\n<pre><code>select  * from employee inner join location on employee.empID = location.empID\nOR\nselect  * from employee, location where employee.empID = location.empID\n</code></pre>\n<p>Now, here is what the result of running that SQL would look like:</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>Employee.EmpId</th>\n<th>Employee.EmpName</th>\n<th>Location.EmpId</th>\n<th>Location.EmpLoc</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>13</td>\n<td>Jason</td>\n<td>13</td>\n<td>San Jose</td>\n</tr>\n<tr>\n<td>8</td>\n<td>Alex</td>\n<td>8</td>\n<td>Los Angeles</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Ram</td>\n<td>3</td>\n<td>Pune, India</td>\n</tr>\n<tr>\n<td>17</td>\n<td>Babu</td>\n<td>17</td>\n<td>Chennai, India</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p><strong>Outer Join:-</strong>\nAn outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins and right outer joins, depending on which table\'s rows are retained (left or right).</p>\n<p><em><strong>Left Outer Join:-</strong></em>\nThe result of a left outer join (or simply left join) for tables <em>Employee</em> and <em>Location</em> always contains all records of the &quot;left&quot; table (<em>Employee</em>), even if the join-condition does not find any matching record in the &quot;right&quot; table (<em>Location</em>).\nHere is what the SQL for a left outer join would look like, using the tables above:</p>\n<pre><code>select  * from employee left outer join location on employee.empID = location.empID;\n//Use of outer keyword is optional\n</code></pre>\n<p>Now, here is what the result of running this SQL would look like:</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>Employee.EmpId</th>\n<th>Employee.EmpName</th>\n<th>Location.EmpId</th>\n<th>Location.EmpLoc</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>13</td>\n<td>Jason</td>\n<td>13</td>\n<td>San Jose</td>\n</tr>\n<tr>\n<td>8</td>\n<td>Alex</td>\n<td>8</td>\n<td>Los Angeles</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Ram</td>\n<td>3</td>\n<td>Pune, India</td>\n</tr>\n<tr>\n<td>17</td>\n<td>Babu</td>\n<td>17</td>\n<td>Chennai, India</td>\n</tr>\n<tr>\n<td><strong>25</strong></td>\n<td><strong>Johnson</strong></td>\n<td><strong>NULL</strong></td>\n<td><strong>NULL</strong></td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>Note how while Johnson has no entry in the employee location table, he is still included in the results but the location fields are nulled.</p>\n<p><em><strong>Right Outer Join:-</strong></em>\nA right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the &quot;right&quot; table (<em>Location</em>) will appear in the joined table at least once. If no matching row from the &quot;left&quot; table (<em>Employee</em>) exists, NULL will appear in columns from <em>Employee</em> for those records that have no match in <em>Location</em>.\nThis is what the SQL looks like:</p>\n<pre><code>select * from employee right outer join location  on employee.empID = location.empID;\n//Use of outer keyword is optional\n</code></pre>\n<p>Using the tables above, we can show what the result set of a right outer join would look like:</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>Employee.EmpId</th>\n<th>Employee.EmpName</th>\n<th>Location.EmpId</th>\n<th>Location.EmpLoc</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>13</td>\n<td>Jason</td>\n<td>13</td>\n<td>San Jose</td>\n</tr>\n<tr>\n<td>8</td>\n<td>Alex</td>\n<td>8</td>\n<td>Los Angeles</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Ram</td>\n<td>3</td>\n<td>Pune, India</td>\n</tr>\n<tr>\n<td>17</td>\n<td>Babu</td>\n<td>17</td>\n<td>Chennai, India</td>\n</tr>\n<tr>\n<td><strong>NULL</strong></td>\n<td><strong>NULL</strong></td>\n<td><strong>39</strong></td>\n<td><strong>Bangalore, India</strong></td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>Note how while there are no employees listed as working in Bangalore, it is still included in the results with the employee fields nulled out.</p>\n<p><strong>Full Outer Joins:-</strong>\nFull Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>Employee.EmpId</th>\n<th>Employee.EmpName</th>\n<th>Location.EmpId</th>\n<th>Location.EmpLoc</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>13</td>\n<td>Jason</td>\n<td>13</td>\n<td>San Jose</td>\n</tr>\n<tr>\n<td>8</td>\n<td>Alex</td>\n<td>8</td>\n<td>Los Angeles</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Ram</td>\n<td>3</td>\n<td>Pune, India</td>\n</tr>\n<tr>\n<td>17</td>\n<td>Babu</td>\n<td>17</td>\n<td>Chennai, India</td>\n</tr>\n<tr>\n<td><strong>25</strong></td>\n<td><strong>Johnson</strong></td>\n<td><strong>NULL</strong></td>\n<td><strong>NULL</strong></td>\n</tr>\n<tr>\n<td><strong>NULL</strong></td>\n<td><strong>NULL</strong></td>\n<td><strong>39</strong></td>\n<td><strong>Bangalore, India</strong></td>\n</tr>\n</tbody>\n</table>\n</div>\n<p><a href="https://dev.mysql.com/doc/refman/8.0/en/join.html" rel="noreferrer">MySQL 8.0 Reference Manual - Join Syntax</a></p>\n<p><a href="https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj29840.html" rel="noreferrer">Oracle Join operations</a></p>\n'}, {'answer_id': 28598795, 'author': 'Pratik', 'author_id': 3326275, 'author_profile': 'https://Stackoverflow.com/users/3326275', 'pm_score': 6, 'selected': False, 'text': '<p>In simple words :</p>\n<p><strong>Inner join</strong> -&gt; Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table.</p>\n<p><strong>Left join</strong> -&gt;</p>\n<p>pseudo code</p>\n<pre><code>1.Take All records from left Table\n2.for(each record in right table,) {\n    if(Records from left &amp; right table matching on primary &amp; foreign key){\n       use their values as it is as result of join at the right side for 2nd table.\n    } else {\n       put value NULL values in that particular record as result of join at the right side for 2nd table.\n    }\n  }\n</code></pre>\n<p><strong>Right join</strong> : Exactly opposite of left join . Put name of table in LEFT JOIN at right side in Right join , you get same output as LEFT JOIN.</p>\n<p><strong>Outer join</strong> : Show all records in Both tables <code>No matter what</code>. If records in Left table are not matching to right table based on Primary , Forieign key , use NULL value as result of join .</p>\n<p><strong>Example :</strong></p>\n<p><img src="https://i.stack.imgur.com/pCErn.png" alt="Example" /></p>\n<p>Lets assume now for 2 tables</p>\n<p><code>1.employees  , 2.phone_numbers_employees</code></p>\n<pre><code>employees : id , name \n\nphone_numbers_employees : id , phone_num , emp_id   \n</code></pre>\n<p>Here , employees table is Master table , phone_numbers_employees is child table(it contains <code>emp_id</code> as foreign key which connects <code>employee.id</code> so its child table.)</p>\n<p><strong>Inner joins</strong></p>\n<p>Take the records of 2 tables <strong>ONLY IF Primary key of employees table(its id) matches Foreign key of Child table phone_numbers_employees(emp_id)</strong>.</p>\n<p>So query would be :</p>\n<pre><code>SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;\n</code></pre>\n<p>Here take only matching rows on primary key = foreign key as explained above.Here non matching rows on primary key = foreign key are skipped as result of join.</p>\n<p><strong>Left joins</strong> :</p>\n<p>Left join retains all rows of the left table, regardless of whether there is a row that matches on the right table.</p>\n<pre><code>SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;\n</code></pre>\n<p><strong>Outer joins</strong> :</p>\n<pre><code>SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;\n</code></pre>\n<p><strong>Diagramatically it looks like :</strong></p>\n<p><img src="https://i.stack.imgur.com/hMKKt.jpg" alt="Diagram" /></p>\n'}, {'answer_id': 29606109, 'author': 'shA.t', 'author_id': 4519059, 'author_profile': 'https://Stackoverflow.com/users/4519059', 'pm_score': 6, 'selected': False, 'text': '<p>The answer is in the meaning of each one, so in the results.</p>\n\n<blockquote>\n  <p><strong>Note :</strong><br>\n  In <code>SQLite</code> there is no <code>RIGHT OUTER JOIN</code> or <code>FULL OUTER JOIN</code>.<br>\n  And also in <code>MySQL</code> there is no <code>FULL OUTER JOIN</code>.</p>\n</blockquote>\n\n<p>My answer is based on above <strong>Note</strong>.</p>\n\n<p>When you have two tables like these: </p>\n\n<pre><code>--[table1]               --[table2]\nid | name                id | name\n---+-------              ---+-------\n1  | a1                  1  | a2\n2  | b1                  3  | b2\n</code></pre>\n\n<hr>\n\n<p><strong>CROSS JOIN / OUTER JOIN :</strong><br>\nYou can have all of those tables data with <code>CROSS JOIN</code> or just with <code>,</code> like this:</p>\n\n<pre><code>SELECT * FROM table1, table2\n--[OR]\nSELECT * FROM table1 CROSS JOIN table2\n\n--[Results:]\nid | name | id | name \n---+------+----+------\n1  | a1   | 1  | a2\n1  | a1   | 3  | b2\n2  | b1   | 1  | a2\n2  | b1   | 3  | b2\n</code></pre>\n\n<hr>\n\n<p><strong>INNER JOIN :</strong><br>\nWhen you want to add a filter to above results based on a relation like <code>table1.id = table2.id</code> you can use <code>INNER JOIN</code>:</p>\n\n<pre><code>SELECT * FROM table1, table2 WHERE table1.id = table2.id\n--[OR]\nSELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id\n\n--[Results:]\nid | name | id | name \n---+------+----+------\n1  | a1   | 1  | a2\n</code></pre>\n\n<hr>\n\n<p><strong>LEFT [OUTER] JOIN :</strong><br>\nWhen you want to have all rows of one of tables in the above result -with same relation- you can use <code>LEFT JOIN</code>:<br>\n(For <strong>RIGHT JOIN</strong> just change place of tables)</p>\n\n<pre><code>SELECT * FROM table1, table2 WHERE table1.id = table2.id \nUNION ALL\nSELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)\n--[OR]\nSELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id\n\n--[Results:]\nid | name | id   | name \n---+------+------+------\n1  | a1   | 1    | a2\n2  | b1   | Null | Null\n</code></pre>\n\n<hr>\n\n<p><strong>FULL OUTER JOIN :</strong><br>\nWhen you also want to have all rows of the other table in your results you can use <code>FULL OUTER JOIN</code>:</p>\n\n<pre><code>SELECT * FROM table1, table2 WHERE table1.id = table2.id\nUNION ALL\nSELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)\nUNION ALL\nSELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)\n--[OR] (recommended for SQLite)\nSELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id\nUNION ALL\nSELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id\nWHERE table1.id IS NULL\n--[OR]\nSELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id\n\n--[Results:]\nid   | name | id   | name \n-----+------+------+------\n1    | a1   | 1    | a2\n2    | b1   | Null | Null\nNull | Null | 3    | b2\n</code></pre>\n\n<p>Well, as your need you choose each one that covers your need ;).</p>\n'}, {'answer_id': 34950096, 'author': 'onedaywhen', 'author_id': 15354, 'author_profile': 'https://Stackoverflow.com/users/15354', 'pm_score': 5, 'selected': False, 'text': '<p>Having criticized the much-loved red-shaded Venn diagram, I thought it only fair to post my own attempt.</p>\n\n<p>Although @Martin Smith\'s answer is the best of this bunch by a long way, his only shows the key column from each table, whereas I think ideally non-key columns should also be shown.</p>\n\n<p>The best I could do in the half hour allowed, I still don\'t think it adequately shows that the nulls are there due to absence of key values in <code>TableB</code> or that <code>OUTER JOIN</code> is actually a union rather than a join:</p>\n\n<p><a href="https://i.stack.imgur.com/fzwkg.png" rel="noreferrer"><img src="https://i.stack.imgur.com/fzwkg.png" alt="enter image description here"></a></p>\n'}, {'answer_id': 35152521, 'author': 'Sandesh', 'author_id': 1018966, 'author_profile': 'https://Stackoverflow.com/users/1018966', 'pm_score': 2, 'selected': False, 'text': "<ul>\n<li><p><strong>Inner join</strong> - An <strong>inner join</strong> using either of the equivalent queries gives the intersection of the two <em>tables</em>, i.e. the two rows they have in common.</p></li>\n<li><p><strong>Left outer join</strong> - A <strong>left outer join</strong> will give all rows in A, plus any common rows in B.</p></li>\n<li><p><strong>Full outer join</strong> - A <strong>full outer join</strong> will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versay</p></li>\n</ul>\n"}, {'answer_id': 36910139, 'author': 'Akshay Khale', 'author_id': 2541634, 'author_profile': 'https://Stackoverflow.com/users/2541634', 'pm_score': 5, 'selected': False, 'text': '<p><strong>Simplest Definitions</strong></p>\n\n<p>Inner Join: Returns <strong>matched records</strong> from both tables.</p>\n\n<p>Full Outer Join: Returns matched and <strong>unmatched records</strong> from both tables with null for unmatched records from <strong>Both Tables</strong>.</p>\n\n<p>Left Outer Join: Returns matched and unmatched records only from table on <strong>Left Side</strong>.</p>\n\n<p>Right Outer Join: Returns matched and unmatched records only from table on <strong>Right Side</strong>.</p>\n\n<p>In-Short</p>\n\n<p>Matched + Left Unmatched + Right Unmatched = <strong>Full Outer Join</strong></p>\n\n<p>Matched + Left Unmatched = <strong>Left Outer Join</strong></p>\n\n<p>Matched + Right Unmatched = <strong>Right Outer Join</strong></p>\n\n<p>Matched = <strong>Inner Join</strong></p>\n'}, {'answer_id': 40016423, 'author': 'Anands23', 'author_id': 4643764, 'author_profile': 'https://Stackoverflow.com/users/4643764', 'pm_score': 4, 'selected': False, 'text': '<p>In Simple Terms,</p>\n\n<p>1.<strong>INNER JOIN OR EQUI JOIN :</strong> Returns the resultset that matches only the condition in both the tables.</p>\n\n<p>2.<strong>OUTER JOIN :</strong> Returns the resultset of all the values from both the tables even if there is condition match or not. </p>\n\n<p>3.<strong>LEFT JOIN :</strong> Returns the resultset of all the values from left table and only rows that match the condition in right table.</p>\n\n<p>4.<strong>RIGHT JOIN :</strong> Returns the resultset of all the values from right table and only rows that match the condition in left table.</p>\n\n<p>5.<strong>FULL JOIN :</strong> Full Join and Full outer Join are same.</p>\n'}, {'answer_id': 40486913, 'author': 'S.Serpooshan', 'author_id': 2803565, 'author_profile': 'https://Stackoverflow.com/users/2803565', 'pm_score': 5, 'selected': False, 'text': '<p>The precise algorithm for <code>INNER JOIN</code>, <code>LEFT/RIGHT OUTER JOIN</code> are as following: </p>\n\n<ol>\n<li>Take each row from the first table: <code>a</code></li>\n<li>Consider all rows from second table beside it: <code>(a, b[i])</code></li>\n<li>Evaluate the <code>ON ...</code> clause against each pair: <code>ON( a, b[i] ) = true/false?</code>\n\n<ul>\n<li>When the condition evaluates to <code>true</code>, return that combined row  <code>(a, b[i])</code>.</li>\n<li>When reach end of second table without any match, and this is an <code>Outer Join</code> then return a <em>(virtual)</em> pair using <code>Null</code> for all columns of other table: <code>(a, Null)</code> for LEFT outer join or <code>(Null, b)</code> for RIGHT outer join. This is to ensure all rows of first table exists in final results.</li>\n</ul></li>\n</ol>\n\n<p><strong>Note:</strong> the condition specified in <code>ON</code> clause could be anything, it is not required to use <em>Primary Keys</em> (and you don\'t need to always refer to Columns from both tables)! For example:</p>\n\n<ul>\n<li><code>... ON T1.title = T2.title AND T1.version &lt; T2.version</code> ( => see this post as a sample usage: <a href="https://stackoverflow.com/a/7745635/2803565">Select only rows with max value on a column</a>)</li>\n<li><code>... ON T1.y IS NULL</code></li>\n<li><code>... ON 1 = 0</code> (just as sample)</li>\n</ul>\n\n<p><a href="https://i.stack.imgur.com/0dWzY.png" rel="noreferrer"><img src="https://i.stack.imgur.com/0dWzY.png" alt="Inner Join vs. Left Outer Join"></a></p>\n\n<hr>\n\n<p><a href="https://i.stack.imgur.com/Bsocp.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Bsocp.png" alt="enter image description here"></a></p>\n\n<p><strong>Note:</strong> Left Join = Left Outer Join, Right Join = Right Outer Join.</p>\n'}, {'answer_id': 45011393, 'author': 'Laxmi', 'author_id': 6755093, 'author_profile': 'https://Stackoverflow.com/users/6755093', 'pm_score': 3, 'selected': False, 'text': '<p>1.<strong>Inner Join:</strong> Also called as Join. It returns the rows present in both the Left table, and right table only <strong>if there is a match</strong>. Otherwise, it returns zero records.</p>\n\n<p>Example:</p>\n\n<pre><code>SELECT\n  e1.emp_name,\n  e2.emp_salary    \nFROM emp1 e1\nINNER JOIN emp2 e2\n  ON e1.emp_id = e2.emp_id\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/lWzlz.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lWzlz.jpg" alt="output1"></a></p>\n\n<p>2.<strong>Full Outer Join:</strong> Also called as Full Join. It returns <strong>all the rows</strong> present in both the Left table, and right table.</p>\n\n<p>Example:</p>\n\n<pre><code>SELECT\n  e1.emp_name,\n  e2.emp_salary    \nFROM emp1 e1\nFULL OUTER JOIN emp2 e2\n  ON e1.emp_id = e2.emp_id\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/3yY0Y.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3yY0Y.jpg" alt="output2"></a></p>\n\n<p>3.<strong>Left Outer join:</strong> Or simply called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).</p>\n\n<p>4.<strong>Right Outer Join:</strong> Also called as Right Join. It returns matching rows from the left table (if any), and all the rows present in the Right table.</p>\n\n<p><a href="https://i.stack.imgur.com/UIVk7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UIVk7.png" alt="joins"></a></p>\n\n<p><strong>Advantages of Joins</strong></p>\n\n<ol>\n<li>Executes faster. </li>\n</ol>\n'}, {'answer_id': 46091641, 'author': 'philipxy', 'author_id': 3404097, 'author_profile': 'https://Stackoverflow.com/users/3404097', 'pm_score': 3, 'selected': False, 'text': '<p><code>left join on</code> returns <code>inner join on</code> rows <code>union all</code> unmatched left table rows extended by <code>null</code>s.</p>\n<p><code>right join on</code> returns <code>inner join on</code> rows <code>union all</code> unmatched right table rows extended by <code>null</code>s.</p>\n<p><code>full join on</code> returns <code>inner join on</code> rows<code>union all</code> unmatched left table rows extended by <code>null</code>s <code>union all</code> unmatched right table rows extended by <code>null</code>s.</p>\n<p><code>outer</code> is optional &amp; has no effect.</p>\n<p>(SQL Standard 2006 SQL/Foundation 7.7 Syntax Rules 1, General Rules 1 b, 3 c &amp; d, 5 b.)</p>\n<p>So don\'t <code>outer join on</code> until you know what underlying <code>inner join on</code> is involved.</p>\n<hr />\n<p>Find out what rows <code>inner join on</code> returns:\n<a href="https://stackoverflow.com/a/25957600/3404097">CROSS JOIN vs INNER JOIN in SQL</a></p>\n<p>That also explains why Venn(-like) diagrams are not helpful for inner vs outer join.\nFor more on why they  are not helpful for joins generally:\n<a href="https://stackoverflow.com/a/55642928/3404097">Venn Diagram for Natural Join</a></p>\n'}, {'answer_id': 46790107, 'author': 'rashedcs', 'author_id': 6714430, 'author_profile': 'https://Stackoverflow.com/users/6714430', 'pm_score': 2, 'selected': False, 'text': '<p>The difference between <code>inner join</code> and <code>outer join</code> is as follow:</p>\n\n<ol>\n<li><code>Inner join</code> is a join that combined tables based on matching tuples, whereas <code>outer join</code> is a join that combined table based on both matched and unmatched tuple.</li>\n<li><code>Inner join</code> merges matched row from two table in where unmatched row are omitted, whereas <code>outer join</code> merges rows from two tables and unmatched rows fill with null value.</li>\n<li><code>Inner join</code> is like an intersection operation, whereas <code>outer join</code> is like an union operation.</li>\n<li><code>Inner join</code> is two types, whereas <code>outer join</code> are three types.</li>\n<li><code>outer join</code> is faster than <code>inner join</code>.</li>\n</ol>\n'}, {'answer_id': 47981412, 'author': 'Premraj', 'author_id': 1697099, 'author_profile': 'https://Stackoverflow.com/users/1697099', 'pm_score': 5, 'selected': False, 'text': '<p><a href="https://i.stack.imgur.com/TBMzF.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/TBMzF.jpg" alt="enter image description here"></a></p>\n\n<ul>\n<li><code>INNER JOIN</code> most typical join for two or more tables.\nIt returns data match on both table ON primarykey and forignkey relation.</li>\n<li><code>OUTER JOIN</code> is same as <code>INNER JOIN</code>, but it also include <code>NULL</code> data on ResultSet.\n\n<ul>\n<li><code>LEFT JOIN</code> = <code>INNER JOIN</code> + Unmatched data of <strong>left</strong> table with <code>Null</code> match on right table.</li>\n<li><code>RIGHT JOIN</code> = <code>INNER JOIN</code> + Unmatched data of <strong>right</strong> table with <code>Null</code> match on left table.</li>\n<li><code>FULL JOIN</code> = <code>INNER JOIN</code> + Unmatched data on <strong>both right and left</strong> tables with <code>Null</code> matches.</li>\n</ul></li>\n<li>Self join is not a keyword in SQL, when a table references data in itself knows as self join. Using <code>INNER JOIN</code> and <code>OUTER JOIN</code> we can write self join queries.</li>\n</ul>\n\n<p>For example:</p>\n\n<pre><code>SELECT * \nFROM   tablea a \n       INNER JOIN tableb b \n               ON a.primary_key = b.foreign_key \n       INNER JOIN tablec c \n               ON b.primary_key = c.foreign_key \n</code></pre>\n'}, {'answer_id': 53468691, 'author': 'Mayank Porwal', 'author_id': 5820814, 'author_profile': 'https://Stackoverflow.com/users/5820814', 'pm_score': 2, 'selected': False, 'text': '<p>Consider below 2 tables:</p>\n\n<p><strong>EMP</strong></p>\n\n<pre><code>empid   name    dept_id salary\n1       Rob     1       100\n2       Mark    1       300\n3       John    2       100\n4       Mary    2       300\n5       Bill    3       700\n6       Jose    6       400\n</code></pre>\n\n<p><strong>Department</strong></p>\n\n<pre><code>deptid  name\n1       IT\n2       Accounts\n3       Security\n4       HR\n5       R&amp;D\n</code></pre>\n\n<h3>Inner Join:</h3>\n\n<p>Mostly written as just <strong>JOIN</strong> in sql queries. It returns only the matching records between the tables.</p>\n\n<h3>Find out all employees and their department names:</h3>\n\n<pre><code>Select a.empid, a.name, b.name as dept_name\nFROM emp a\nJOIN department b\nON a.dept_id = b.deptid\n;\n\nempid   name    dept_name\n1       Rob     IT\n2       Mark    IT\n3       John    Accounts\n4       Mary    Accounts\n5       Bill    Security\n</code></pre>\n\n<p>As you see above, <code>Jose</code> is not printed from <strong>EMP</strong> in the output as it\'s dept_id <code>6</code> does not find a match in the Department table.  Similarly, <code>HR</code> and <code>R&amp;D</code> rows are not printed from <strong>Department</strong> table as they didn\'t find a match in the Emp table. </p>\n\n<p><strong>So, INNER JOIN or just JOIN, returns only matching rows.</strong></p>\n\n<h3>LEFT JOIN :</h3>\n\n<p>This returns all records from the LEFT table and only matching records from the RIGHT table.</p>\n\n<pre><code>Select a.empid, a.name, b.name as dept_name\nFROM emp a\nLEFT JOIN department b\nON a.dept_id = b.deptid\n;\n\nempid   name    dept_name\n1       Rob     IT\n2       Mark    IT\n3       John    Accounts\n4       Mary    Accounts\n5       Bill    Security\n6       Jose    \n</code></pre>\n\n<p>So, if you observe the above output, all records from the LEFT table(Emp) are printed with just matching records from RIGHT table.</p>\n\n<p><code>HR</code> and <code>R&amp;D</code> rows are not printed from <strong>Department</strong> table as they didn\'t find a match in the Emp table on dept_id.</p>\n\n<p><strong>So, LEFT JOIN returns ALL rows from Left table and only matching rows from RIGHT table.</strong> </p>\n\n<p>Can also check DEMO <a href="https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=9dbdd6cf405ae24c5e2a0798a732d389" rel="nofollow noreferrer">here</a>.</p>\n'}, {'answer_id': 58563070, 'author': 'ForguesR', 'author_id': 1980659, 'author_profile': 'https://Stackoverflow.com/users/1980659', 'pm_score': 2, 'selected': False, 'text': '<p>There are a lot of good answers here with very accurate <a href="https://en.wikipedia.org/wiki/Relational_algebra" rel="nofollow noreferrer">relational algebra</a> examples.  Here is a very simplified answer that might be helpful for amateur or novice coders with SQL coding dilemmas.</p>\n<p>Basically, more often than not, <code>JOIN</code> queries boil down to two cases:</p>\n<p>For a <code>SELECT</code> of a subset of <code>A</code> data:</p>\n<ul>\n<li>use <code>INNER JOIN</code> when the related <code>B</code> data you are looking for <strong>MUST</strong> exist per database design;</li>\n<li>use <code>LEFT JOIN</code> when the related <code>B</code> data you are looking for <strong>MIGHT</strong> or <strong>MIGHT NOT</strong> exist per database design.</li>\n</ul>\n'}, {'answer_id': 61647208, 'author': 'Scratte', 'author_id': 12695027, 'author_profile': 'https://Stackoverflow.com/users/12695027', 'pm_score': 5, 'selected': False, 'text': '<h1>The General Idea</h1>\n<p>Please see the <a href="https://stackoverflow.com/a/27458534/12695027">answer</a> by <a href="https://stackoverflow.com/users/73226">Martin Smith</a> for a better illustations and explanations of the different joins, including and especially differences between <code>FULL OUTER JOIN</code>, <code>RIGHT OUTER JOIN</code> and <code>LEFT OUTER JOIN</code>.</p>\n<p>These two table form a basis for the representation of the <code>JOIN</code>s below:</p>\n<p><img src="https://i.stack.imgur.com/DEAAy.png" alt="Basis" /></p>\n<h3>CROSS JOIN</h3>\n<p><img src="https://i.stack.imgur.com/EqnB9.png" alt="CrossJoin" /></p>\n<pre><code>SELECT *\n  FROM citizen\n CROSS JOIN postalcode\n</code></pre>\n<p>The result will be the Cartesian products of all combinations. No <code>JOIN</code> condition required:</p>\n<p><img src="https://i.stack.imgur.com/eeFIB.png" alt="CrossJoinResult" /></p>\n<h3>INNER JOIN</h3>\n<p><code>INNER JOIN</code> is the same as simply: <code>JOIN</code></p>\n<p><img src="https://i.stack.imgur.com/oXqs0.png" alt="InnerJoin" /></p>\n<pre><code>SELECT *\n  FROM citizen    c\n  JOIN postalcode p ON c.postal = p.postal\n</code></pre>\n<p>The result will be combinations that satisfies the required <code>JOIN</code> condition:</p>\n<p><img src="https://i.stack.imgur.com/aaNvi.png" alt="InnerJoinResult" /></p>\n<h3>LEFT OUTER JOIN</h3>\n<p><code>LEFT OUTER JOIN</code> is the same as <code>LEFT JOIN</code></p>\n<p><img src="https://i.stack.imgur.com/qmy29.png" alt="LeftJoin" /></p>\n<pre><code>SELECT *\n  FROM citizen         c\n  LEFT JOIN postalcode p ON c.postal = p.postal\n</code></pre>\n<p>The result will be everything from <code>citizen</code> even if there are no matches in <code>postalcode</code>. Again a <code>JOIN</code> condition is required:</p>\n<p><img src="https://i.stack.imgur.com/mbaqJ.png" alt="LeftJoinResult" /></p>\n<h3>Data for playing</h3>\n<p>All examples have been run on an Oracle 18c. They\'re available at <a href="https://dbfiddle.uk/?rdbms=oracle_18&amp;fiddle=19be5f6abb0a5987fddf037b5df343bd" rel="noreferrer">dbfiddle.uk</a> which is also where screenshots of tables came from.</p>\n<pre><code>CREATE TABLE citizen (id      NUMBER,\n                      name    VARCHAR2(20),\n                      postal  NUMBER,  -- &lt;-- could do with a redesign to postalcode.id instead.\n                      leader  NUMBER);\n\nCREATE TABLE postalcode (id      NUMBER,\n                         postal  NUMBER,\n                         city    VARCHAR2(20),\n                         area    VARCHAR2(20));\n\nINSERT INTO citizen (id, name, postal, leader)\n              SELECT 1, \'Smith\', 2200,  null FROM DUAL\n        UNION SELECT 2, \'Green\', 31006, 1    FROM DUAL\n        UNION SELECT 3, \'Jensen\', 623,  1    FROM DUAL;\n\nINSERT INTO postalcode (id, postal, city, area)\n                 SELECT 1, 2200,     \'BigCity\',         \'Geancy\'  FROM DUAL\n           UNION SELECT 2, 31006,    \'SmallTown\',       \'Snizkim\' FROM DUAL\n           UNION SELECT 3, 31006,    \'Settlement\',      \'Moon\'    FROM DUAL  -- &lt;-- Uuh-uhh.\n           UNION SELECT 4, 78567390, \'LookoutTowerX89\', \'Space\'   FROM DUAL;\n</code></pre>\n<h1>Blurry boundaries when playing with <code>JOIN</code> and <code>WHERE</code></h1>\n<h3>CROSS JOIN</h3>\n<p><code>CROSS JOIN</code> resulting in rows as The General Idea/<code>INNER JOIN</code>:</p>\n<pre><code>SELECT *\n  FROM citizen          c\n  CROSS JOIN postalcode p\n WHERE c.postal = p.postal -- &lt; -- The WHERE condition is limiting the resulting rows\n</code></pre>\n<p>Using <code>CROSS JOIN</code> to get the result of a <code>LEFT OUTER JOIN</code> requires tricks like adding in a <code>NULL</code> row. It\'s omitted.</p>\n<h2>INNER JOIN</h2>\n<p><code>INNER JOIN</code> becomes a cartesian products. It\'s the same as The General Idea/<code>CROSS JOIN</code>:</p>\n<pre><code>SELECT *\n  FROM citizen    c\n  JOIN postalcode p ON 1 = 1  -- &lt; -- The ON condition makes it a CROSS JOIN\n</code></pre>\n<p>This is where the inner join can really be seen as the cross join with results not matching the condition removed. Here none of the resulting rows are removed.</p>\n<p>Using <code>INNER JOIN</code> to get the result of a <code>LEFT OUTER JOIN</code> also requires tricks. It\'s omitted.</p>\n<h2>LEFT OUTER JOIN</h2>\n<p><code>LEFT JOIN</code> results in rows as The General Idea/<code>CROSS JOIN</code>:</p>\n<pre><code>SELECT *\n  FROM citizen         c\n  LEFT JOIN postalcode p ON 1 = 1 -- &lt; -- The ON condition makes it a CROSS JOIN\n</code></pre>\n<p><code>LEFT JOIN</code> results in rows as The General Idea/<code>INNER JOIN</code>:</p>\n<pre><code>SELECT *\n  FROM citizen         c\n  LEFT JOIN postalcode p ON c.postal = p.postal\n WHERE p.postal IS NOT NULL -- &lt; -- removed the row where there\'s no mathcing result from postalcode\n</code></pre>\n<hr />\n<h1>The troubles with the Venn diagram</h1>\n<p>An image internet search on &quot;sql join cross inner outer&quot; will show a multitude of Venn diagrams. I used to have a printed copy of one on my desk. But there are issues with the representation.</p>\n<p>Venn diagram are excellent for set theory, where an element can be in one or both sets. But for databases, an element in one &quot;set&quot; seem, to me, to be a row in a table, and therefore not also present in any other tables. There is no such thing as one row present in multiple tables. A row is unique to the table.</p>\n<p>Self joins are a corner case where each element is in fact the same in both sets. But it\'s still not free of any of the issues below.</p>\n<p>The set <code>A</code> represents the set on the left (the <code>citizen</code> table) and the set <code>B</code> is the set on the right (the <code>postalcode</code> table) in below discussion.</p>\n<h3>CROSS JOIN</h3>\n<p>Every element in both sets are matched with every element in the other set, meaning we need <code>A</code> amount of every <code>B</code> elements and <code>B</code> amount of every <code>A</code> elements to properly represent this Cartesian product. Set theory isn\'t made for multiple identical elements in a set, so I find Venn diagrams to properly represent it impractical/impossible. It doesn\'t seem that <code>UNION</code> fits at all.</p>\n<p>The rows are distinct. The <code>UNION</code> is 7 rows in total. But they\'re incompatible for a common <code>SQL</code> results set. And this is not how a <code>CROSS JOIN</code> works at all:</p>\n<p><img src="https://i.stack.imgur.com/MqN6E.png" alt="CrossJoinUnion1" /></p>\n<p>Trying to represent it like this:</p>\n<p><img src="https://i.stack.imgur.com/1BhEE.png" alt="CrossJoinUnion2Crossing" /></p>\n<p>..but now it just looks like an <code>INTERSECTION</code>, which it\'s certainly <strong>not</strong>. Furthermore there\'s no element in the <code>INTERSECTION</code> that is actually in any of the two distinct sets. However, it looks very much like the searchable results similar to this:</p>\n<p><img src="https://i.stack.imgur.com/syxYr.png" alt="CrossJoinUnionUnion3" /></p>\n<p>For reference one searchable result for <code>CROSS JOIN</code>s can be seen at <a href="https://www.tutorialgateway.org/sql-joins/" rel="noreferrer">Tutorialgateway</a>. The <code>INTERSECTION</code>, just like this one, is empty.</p>\n<h3>INNER JOIN</h3>\n<p>The value of an element depends on the <code>JOIN</code> condition. It\'s possible to represent this under the condition that every row becomes unique to that condition. Meaning <code>id=x</code> is only true for <strong>one</strong> row. Once a row in table <code>A</code> (<code>citizen</code>) matches multiple rows in table <code>B</code> (<code>postalcode</code>) under the <code>JOIN</code> condition, the result has the same problems as the <code>CROSS JOIN</code>: The row needs to be represented multiple times, and the set theory isn\'t really made for that. Under the condition of uniqueness, the diagram could work though, but keep in mind that the <code>JOIN</code> condition determines the placement of an element in the diagram. Looking only at the values of the <code>JOIN</code> condition with the rest of the row just along for the ride:</p>\n<p><img src="https://i.stack.imgur.com/djpSE.png" alt="InnerJoinIntersection - Filled" /></p>\n<p>This representation falls completely apart when using an <code>INNER JOIN</code> with a <code>ON 1 = 1</code> condition making it into a <code>CROSS JOIN</code>.</p>\n<p>With a self-<code>JOIN</code>, the rows are in fact idential elements in both tables, but representing the tables as both <code>A</code> and <code>B</code> isn\'t very suitable. For example a common self-<code>JOIN</code> condition that makes an element in <code>A</code> to be matching a <strong>different</strong> element in B is <code>ON A.parent = B.child</code>, making the match from <code>A</code> to <code>B</code> on seperate elements. From the examples that would be a <code>SQL</code> like this:</p>\n<pre><code>SELECT *\n  FROM citizen c1\n  JOIN citizen c2 ON c1.id = c2.leader\n</code></pre>\n<p><img src="https://i.stack.imgur.com/p5r7D.png" alt="SelfJoinResult" /></p>\n<p>Meaning Smith is the leader of both Green and Jensen.</p>\n<h3>OUTER JOIN</h3>\n<p>Again the troubles begin when one row has multiple matches to rows in the other table. This is further complicated because the <code>OUTER JOIN</code> can be though of as to match the empty set. But in set theory the union of any set <code>C</code> and an empty set, is always just <code>C</code>. The empty set adds nothing. The representation of this <code>LEFT OUTER JOIN</code> is usually just showing all of <code>A</code> to illustrate that rows in <code>A</code> are selected regardless of whether there is a match or not from <code>B</code>. The &quot;matching elements&quot; however has the same problems as the illustration above. They depend on the condition. And the empty set seems to have wandered over to <code>A</code>:</p>\n<p><img src="https://i.stack.imgur.com/Q2yFW.png" alt="LeftJoinIntersection - Filled" /></p>\n<h3>WHERE clause - making sense</h3>\n<p>Finding all rows from a <code>CROSS JOIN</code> with Smith and postalcode on the Moon:</p>\n<pre><code>SELECT *\n  FROM citizen          c\n CROSS JOIN postalcode  p\n WHERE c.name = \'Smith\'\n   AND p.area = \'Moon\';\n</code></pre>\n<p><img src="https://i.stack.imgur.com/Iy4Z3.png" alt="Where - result" /></p>\n<p>Now the Venn diagram isn\'t used to reflect the <code>JOIN</code>. It\'s used <strong>only</strong> for the <code>WHERE</code> clause:</p>\n<p><img src="https://i.stack.imgur.com/fmxEr.png" alt="Where" /></p>\n<p>..and that makes sense.</p>\n<h1>When INTERSECT and UNION makes sense</h1>\n<h3>INTERSECT</h3>\n<p>As explained an <code>INNER JOIN</code> is not really an <code>INTERSECT</code>. However <code>INTERSECT</code>s can be used on results of seperate queries. Here a Venn diagram makes sense, because the elements from the seperate queries are in fact rows that either belonging to just one of the results or both. Intersect will obviously only return results where the row is present in both queries. This <code>SQL</code> will result in the same row as the one above <code>WHERE</code>, and the Venn diagram will also be the same:</p>\n<pre><code>SELECT *\n  FROM citizen          c\n CROSS JOIN postalcode  p\n WHERE c.name = \'Smith\'\nINTERSECT\nSELECT *\n  FROM citizen          c\n CROSS JOIN postalcode  p\n WHERE p.area = \'Moon\';\n</code></pre>\n<h3>UNION</h3>\n<p>An <code>OUTER JOIN</code> is not a <code>UNION</code>. However <code>UNION</code> work under the same conditions as <code>INTERSECT</code>, resulting in a return of all results combining both <code>SELECT</code>s:</p>\n<pre><code>SELECT *\n  FROM citizen          c\n CROSS JOIN postalcode  p\n WHERE c.name = \'Smith\'\nUNION\nSELECT *\n  FROM citizen          c\n CROSS JOIN postalcode  p\n WHERE p.area = \'Moon\';\n</code></pre>\n<p>which is equivalent to:</p>\n<pre><code>SELECT *\n  FROM citizen          c\n CROSS JOIN postalcode  p\n WHERE c.name = \'Smith\'\n   OR p.area = \'Moon\';\n</code></pre>\n<p>..and gives the result:</p>\n<p><img src="https://i.stack.imgur.com/FTPRr.png" alt="Union - Result" /></p>\n<p>Also here a Venn diagram makes sense:</p>\n<p><img src="https://i.stack.imgur.com/GW69a.png" alt="UNION" /></p>\n<h3>When it doesn\'t apply</h3>\n<p>An <strong>important note</strong> is that these only work when the structure of the results from the two SELECT\'s are the same, enabling a comparison or union. The results of these two will not enable that:</p>\n<pre><code>SELECT *\n  FROM citizen\n WHERE name = \'Smith\'\n</code></pre>\n<pre><code>SELECT *\n  FROM postalcode\n WHERE area = \'Moon\';\n</code></pre>\n<p>..trying to combine the results with <code>UNION</code> gives a</p>\n<pre><code>ORA-01790: expression must have same datatype as corresponding expression\n</code></pre>\n<hr />\n<p>For further interest read <a href="https://blog.jooq.org/say-no-to-venn-diagrams-when-explaining-joins/" rel="noreferrer">Say NO to Venn Diagrams When Explaining JOINs</a> and <a href="https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram">sql joins as venn diagram</a>. Both also cover <code>EXCEPT</code>.</p>\n'}, {'answer_id': 63190387, 'author': 'HUGO-DEV', 'author_id': 11786005, 'author_profile': 'https://Stackoverflow.com/users/11786005', 'pm_score': 2, 'selected': False, 'text': '<p><strong>Joins are more easily explained with an example:</strong></p>\n<p><a href="https://i.stack.imgur.com/DxeqP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DxeqP.png" alt="enter image description here" /></a></p>\n<p>To simulate persons and emails stored in separate tables,</p>\n<p>Table A and Table B are joined by Table_A.<strong>id</strong> = Table_B.<strong>name_id</strong></p>\n<p><strong>Inner Join</strong></p>\n<p><a href="https://i.stack.imgur.com/l4sbv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l4sbv.png" alt="enter image description here" /></a></p>\n<p>Only matched ids\' rows are shown.</p>\n<p><strong>Outer Joins</strong></p>\n<p><a href="https://i.stack.imgur.com/gL8gT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gL8gT.png" alt="enter image description here" /></a></p>\n<p>Matched ids and not matched rows for <strong>Table A</strong> are shown.</p>\n<p><a href="https://i.stack.imgur.com/kXk4J.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kXk4J.png" alt="enter image description here" /></a></p>\n<p>Matched ids and not matched rows for <strong>Table B</strong> are shown.</p>\n<p><a href="https://i.stack.imgur.com/736nT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/736nT.png" alt="enter image description here" /></a>\nMatched ids and not matched rows from both Tables are shown.</p>\n<p><em>Note: Full outer join is not available on MySQL</em></p>\n'}, {'answer_id': 67235945, 'author': 'Nathan Long', 'author_id': 4376, 'author_profile': 'https://Stackoverflow.com/users/4376', 'pm_score': 1, 'selected': False, 'text': '<h1>A Demonstration</h1>\n<h2>Setup</h2>\n<p>Hop into <code>psql</code> and create a tiny database of cats and humans.\nYou can just copy-paste this whole section.</p>\n<pre class="lang-sql prettyprint-override"><code>CREATE DATABASE catdb;\n\\c catdb;\n\\pset null \'[NULL]\' -- how to display null values\n\nCREATE TABLE humans (\n  name text primary key\n);\nCREATE TABLE cats (\n  human_name text references humans(name),\n  name text\n);\n\nINSERT INTO humans (name)\nVALUES (\'Abe\'), (\'Ann\'), (\'Ben\'), (\'Jen\');\n\nINSERT INTO cats (human_name, name)\nVALUES\n(\'Abe\', \'Axel\'),\n(NULL, \'Bitty\'),\n(\'Jen\', \'Jellybean\'),\n(\'Jen\', \'Juniper\');\n</code></pre>\n<h2>Querying</h2>\n<p>Here\'s a query we\'ll run several times, changing <code>[SOMETHING JOIN]</code> to the various types to see the results.</p>\n<pre class="lang-sql prettyprint-override"><code>SELECT\nhumans.name AS human_name,\ncats.name AS cat_name\nFROM humans\n[SOMETHING JOIN] cats ON humans.name = cats.human_name\nORDER BY humans.name;\n</code></pre>\n<p>An <code>INNER JOIN</code> returns all human-cat pairs.\nAny human without a cat or cat without a human is excluded.</p>\n<pre><code> human_name | cat_name\n------------+-----------\n Abe        | Axel\n Jen        | Jellybean\n Jen        | Juniper\n</code></pre>\n<p>A <code>FULL OUTER JOIN</code> returns all humans and all cats, with <code>NULL</code> if there is no match on either side.</p>\n<pre><code> human_name | cat_name\n------------+-----------\n Abe        | Axel\n Ann        | [NULL]\n Ben        | [NULL]\n Jen        | Jellybean\n Jen        | Juniper\n [NULL]     | Bitty\n</code></pre>\n<p>A <code>LEFT OUTER JOIN</code> returns all humans (the left table).\nAny human without a cat gets a <code>NULL</code> in the <code>cat_name</code> column.\nAny cat without a human is excluded.</p>\n<pre><code> human_name | cat_name\n------------+-----------\n Abe        | Axel\n Ann        | [NULL]\n Ben        | [NULL]\n Jen        | Jellybean\n Jen        | Juniper\n</code></pre>\n<p>A <code>RIGHT OUTER JOIN</code> returns all cats (the right table).\nAny cat without a human gets a <code>NULL</code> in the <code>human_name</code> column.\nAny human without a cat is excluded.</p>\n<pre><code> human_name | cat_name\n------------+-----------\n Abe        | Axel\n Jen        | Jellybean\n Jen        | Juniper\n [NULL]     | Bitty\n</code></pre>\n<h2>INNER vs OUTER</h2>\n<p>You can see that while an <code>INNER JOIN</code> gets only matching pairs, each kind of <code>OUTER</code> join includes some items without a match.</p>\n<p>However, the actual words <code>INNER</code> and <code>OUTER</code> do not need to appear in queries:</p>\n<ul>\n<li><code>JOIN</code> by itself implies <code>INNER</code></li>\n<li><code>LEFT JOIN</code>, <code>RIGHT JOIN</code> and <code>OUTER JOIN</code> all imply <code>OUTER</code></li>\n</ul>\n'}, {'answer_id': 73510726, 'author': 'Braiam', 'author_id': 792066, 'author_profile': 'https://Stackoverflow.com/users/792066', 'pm_score': 0, 'selected': False, 'text': '<p>The &quot;outer&quot; and &quot;inner&quot; are just optional elements, you are just dealing with two (three) kinds of joins. Inner joins (or what is the default when using only &quot;join&quot;) is a join where only the elements that match the criteria are present on both tables.</p>\n<p>The &quot;outer&quot; joins are the same as the inner join plus the elements of the left or right table that didn\'t match, adding nulls on all columns for the other table.</p>\n<p>The full join is the inner plus the right and left joins.</p>\n<p>In summary, if we have table A like this</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>idA</th>\n<th>ColumnTableA</th>\n<th>idB</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n<td>Jonh</td>\n<td>1</td>\n</tr>\n<tr>\n<td>2</td>\n<td>Sarah</td>\n<td>1</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Clark</td>\n<td>2</td>\n</tr>\n<tr>\n<td>4</td>\n<td>Barbie</td>\n<td>NULL</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>And table B like this:</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>idB</th>\n<th>ColumnTableB</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>2</td>\n<td>Kent</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Spock</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>The inner join:</p>\n<pre><code>from tableA join tableB on tableA.idB = tableB.idB\n</code></pre>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>idA</th>\n<th>ColumnTableA</th>\n<th>idB</th>\n<th>ColumnTableB</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n<td>Jonh</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>2</td>\n<td>Sarah</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Clark</td>\n<td>2</td>\n<td>Kent</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>Left outer join:</p>\n<pre><code>from tableA left join tableB on tableA.idB = tableB.idB\n</code></pre>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>idA</th>\n<th>ColumnTableA</th>\n<th>idB</th>\n<th>ColumnTableB</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n<td>Jonh</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>2</td>\n<td>Sarah</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Clark</td>\n<td>2</td>\n<td>Kent</td>\n</tr>\n<tr>\n<td>4</td>\n<td>Barbie</td>\n<td>NULL</td>\n<td>NULL</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>Right outer join:</p>\n<pre><code>from tableA right join tableB on tableA.idB = tableB.idB\n</code></pre>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>idA</th>\n<th>ColumnTableA</th>\n<th>idB</th>\n<th>ColumnTableB</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n<td>Jonh</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>2</td>\n<td>Sarah</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Clark</td>\n<td>2</td>\n<td>Kent</td>\n</tr>\n<tr>\n<td>NULL</td>\n<td>NULL</td>\n<td>3</td>\n<td>Spock</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>Full outer join:</p>\n<pre><code>from tableA full join tableB on tableA.idB = tableB.idB\n</code></pre>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>idA</th>\n<th>ColumnTableA</th>\n<th>idB</th>\n<th>ColumnTableB</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n<td>Jonh</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>2</td>\n<td>Sarah</td>\n<td>1</td>\n<td>Connor</td>\n</tr>\n<tr>\n<td>3</td>\n<td>Clark</td>\n<td>2</td>\n<td>Kent</td>\n</tr>\n<tr>\n<td>4</td>\n<td>Barbie</td>\n<td>NULL</td>\n<td>NULL</td>\n</tr>\n<tr>\n<td>NULL</td>\n<td>NULL</td>\n<td>3</td>\n<td>Spock</td>\n</tr>\n</tbody>\n</table>\n</div>'}, {'answer_id': 73939639, 'author': 'uthra', 'author_id': 20113153, 'author_profile': 'https://Stackoverflow.com/users/20113153', 'pm_score': -1, 'selected': False, 'text': '<p><strong>in Inner join</strong>, We can retrieve the data where we have the same/related data in different tables</p>\n<p>SELECT Sname, Tname\nFROM student s\nJOIN  teacher t\nON s.id = t.id;</p>\n<p>if you also want to get other data information from a different table in that case, we can go for outer join</p>\n<p>we have 3 types of outer join:</p>\n<p><strong>LEFT JOIN, RIGHT JOIN, and FULL JOIN</strong></p>\n<p>We will also retrieve the unrelated data from the left (the first) table for the <strong>LEFT OUTER JOIN.</strong></p>\n<p>SELECT Sname, Tname\nFROM student s\nLEFT JOIN  teacher t\nON s.id = t.id;</p>\n<p>The <strong>RIGHT OUTER JOIN</strong>, we also  retrieve the unrelated data from the right table (the second table)</p>\n<p>SELECT Sname, Tname\nFROM student s\nRIGHT JOIN teacher t\nON s.id = t.id;</p>\n<p>We can use the <strong>FULL OUTER JOIN</strong> when we want to retrieve all/complete data from both tables and the missing data will be filled in with NULL.</p>\n<p>SELECT Sname, Tname\nFROM student s\nFULL JOIN teacher t\nON s.id = t.id;</p>\n'}]
['https://Stackoverflow.com/questions/38549', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3836/']