StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Assuming you're joining on columns with no duplicates, which is a very common case:</p> <ul> <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></li> <li><p>An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.</p></li> </ul> <p><strong>Examples</strong></p> <p>Suppose you have two tables, with a single column each, and data as follows:</p> <pre><code>A B - - 1 3 2 4 3 5 4 6 </code></pre> <p>Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.</p> <p><strong>Inner join</strong></p> <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> <pre><code>select * from a INNER JOIN b on a.a = b.b; select a.*, b.* from a,b where a.a = b.b; a | b --+-- 3 | 3 4 | 4 </code></pre> <p><strong>Left outer join</strong></p> <p>A left outer join will give all rows in A, plus any common rows in B.</p> <pre><code>select * from a LEFT OUTER JOIN b on a.a = b.b; select a.*, b.* from a,b where a.a = b.b(+); a | b --+----- 1 | null 2 | null 3 | 3 4 | 4 </code></pre> <p><strong>Right outer join</strong></p> <p>A right outer join will give all rows in B, plus any common rows in A.</p> <pre><code>select * from a RIGHT OUTER JOIN b on a.a = b.b; select a.*, b.* from a,b where a.a(+) = b.b; a | b -----+---- 3 | 3 4 | 4 null | 5 null | 6 </code></pre> <p><strong>Full outer join</strong></p> <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> <pre><code>select * from a FULL OUTER JOIN b on a.a = b.b; a | b -----+----- 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload