StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Yes, I do use all three of those JOINs, although I tend to stick to using just <code>LEFT (OUTER) JOIN</code>s instead of inter-mixing LEFT and RIGHT JOINs. I also use <code>FULL OUTER JOIN</code>s and <code>CROSS JOIN</code>s.</p> <p>In summary, an <code>INNER JOIN</code> restricts the resultset only to those records satisfied by the JOIN condition. Consider the following tables</p> <p><strong>EDIT:</strong> I've renamed the Table names and prefix them with <code>@</code> so that Table Variables can be used for anyone reading this answer and wanting to experiment.</p> <p>If you'd also like to experiment with this in the browser, <strong><a href="http://www.sqlfiddle.com/#!3/167be/1/0" rel="nofollow noreferrer">I've set this all up on SQL Fiddle</a></strong> too;</p> <pre><code>@Table1 id | name --------- 1 | One 2 | Two 3 | Three 4 | Four @Table2 id | name --------- 1 | Partridge 2 | Turtle Doves 3 | French Hens 5 | Gold Rings </code></pre> <p>SQL code</p> <pre><code>DECLARE @Table1 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25)) INSERT INTO @Table1 VALUES(1, 'One'); INSERT INTO @Table1 VALUES(2, 'Two'); INSERT INTO @Table1 VALUES(3, 'Three'); INSERT INTO @Table1 VALUES(4, 'Four'); DECLARE @Table2 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25)) INSERT INTO @Table2 VALUES(1, 'Partridge'); INSERT INTO @Table2 VALUES(2, 'Turtle Doves'); INSERT INTO @Table2 VALUES(3, 'French Hens'); INSERT INTO @Table2 VALUES(5, 'Gold Rings'); </code></pre> <p>An <code>INNER JOIN</code> SQL Statement, joined on the <code>id</code> field </p> <pre><code>SELECT t1.id, t1.name, t2.name FROM @Table1 t1 INNER JOIN @Table2 t2 ON t1.id = t2.id </code></pre> <p>Results in </p> <pre><code>id | name | name ---------------- 1 | One | Partridge 2 | Two | Turtle Doves 3 | Three| French Hens </code></pre> <p>A <code>LEFT JOIN</code> will return a resultset with all records from the table on the left hand side of the join (if you were to write out the statement as a one liner, the table that appears first) and fields from the table on the right side of the join that match the join expression and are included in the <code>SELECT</code> clause. <em>Missing</em> details will be populated with NULL</p> <pre><code>SELECT t1.id, t1.name, t2.name FROM @Table1 t1 LEFT JOIN @Table2 t2 ON t1.id = t2.id </code></pre> <p>Results in</p> <pre><code>id | name | name ---------------- 1 | One | Partridge 2 | Two | Turtle Doves 3 | Three| French Hens 4 | Four | NULL </code></pre> <p>A <code>RIGHT JOIN</code> is the same logic as a <code>LEFT JOIN</code> but will return all records from the right-hand side of the join and fields from the left side that match the join expression and are included in the <code>SELECT</code> clause.</p> <pre><code>SELECT t1.id, t1.name, t2.name FROM @Table1 t1 RIGHT JOIN @Table2 t2 ON t1.id = t2.id </code></pre> <p>Results in</p> <pre><code>id | name | name ---------------- 1 | One | Partridge 2 | Two | Turtle Doves 3 | Three| French Hens NULL| NULL| Gold Rings </code></pre> <p>Of course, there is also the <code>FULL OUTER JOIN</code>, which includes records from both joined tables and populates any <em>missing</em> details with NULL.</p> <pre><code>SELECT t1.id, t1.name, t2.name FROM @Table1 t1 FULL OUTER JOIN @Table2 t2 ON t1.id = t2.id </code></pre> <p>Results in</p> <pre><code>id | name | name ---------------- 1 | One | Partridge 2 | Two | Turtle Doves 3 | Three| French Hens 4 | Four | NULL NULL| NULL| Gold Rings </code></pre> <p>And a <code>CROSS JOIN</code> (also known as a <code>CARTESIAN PRODUCT</code>), which is simply the product of cross applying fields in the <code>SELECT</code> statement from one table with the fields in the <code>SELECT</code> statement from the other table. Notice that there is no join expression in a <code>CROSS JOIN</code></p> <pre><code>SELECT t1.id, t1.name, t2.name FROM @Table1 t1 CROSS JOIN @Table2 t2 </code></pre> <p>Results in </p> <pre><code>id | name | name ------------------ 1 | One | Partridge 2 | Two | Partridge 3 | Three | Partridge 4 | Four | Partridge 1 | One | Turtle Doves 2 | Two | Turtle Doves 3 | Three | Turtle Doves 4 | Four | Turtle Doves 1 | One | French Hens 2 | Two | French Hens 3 | Three | French Hens 4 | Four | French Hens 1 | One | Gold Rings 2 | Two | Gold Rings 3 | Three | Gold Rings 4 | Four | Gold Rings </code></pre> <p><strong>EDIT:</strong></p> <p>Imagine there is now a Table3 </p> <pre><code>@Table3 id | name --------- 2 | Prime 1 3 | Prime 2 5 | Prime 3 </code></pre> <p>The SQL code</p> <pre><code>DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25)) INSERT INTO @Table3 VALUES(2, 'Prime 1'); INSERT INTO @Table3 VALUES(3, 'Prime 2'); INSERT INTO @Table3 VALUES(5, 'Prime 3'); </code></pre> <p>Now all three tables joined with <code>INNER JOINS</code></p> <pre><code>SELECT t1.id, t1.name, t2.name, t3.name FROM @Table1 t1 INNER JOIN @Table2 t2 ON t1.id = t2.id INNER JOIN @Table3 t3 ON t1.id = t3.id </code></pre> <p>Results in</p> <pre><code>id | name | name | name ------------------------------- 2 | Two | Turtle Doves | Prime 1 3 | Three| French Hens | Prime 2 </code></pre> <p>It might help to understand this result by thinking that records with id 2 and 3 are the only ones common to all 3 tables <em>and</em> are also the field we are joining each table on.</p> <p>Now all three with <code>LEFT JOINS</code></p> <pre><code>SELECT t1.id, t1.name, t2.name, t3.name FROM @Table1 t1 LEFT JOIN @Table2 t2 ON t1.id = t2.id LEFT JOIN @Table3 t3 ON t1.id = t3.id </code></pre> <p>Results in</p> <pre><code>id | name | name | name ------------------------------- 1 | One | Partridge | NULL 2 | Two | Turtle Doves | Prime 1 3 | Three| French Hens | Prime 2 4 | Four | NULL | NULL </code></pre> <p><a href="https://stackoverflow.com/questions/1262148/three-table-join-with-joins-other-than-inner-join/1262196#1262196">Joel's answer</a> is a good explanation for explaining this resultset (Table1 is the base/origin table).</p> <p>Now with a <code>INNER JOIN</code> and a <code>LEFT JOIN</code></p> <pre><code>SELECT t1.id, t1.name, t2.name, t3.name FROM @Table1 t1 INNER JOIN @Table2 t2 ON t1.id = t2.id LEFT JOIN @Table3 t3 ON t1.id = t3.id </code></pre> <p>Results in</p> <pre><code>id | name | name | name ------------------------------- 1 | One | Partridge | NULL 2 | Two | Turtle Doves | Prime 1 3 | Three| French Hens | Prime 2 </code></pre> <p>Although we do not know the order in which the query optimiser will perform the operations, we will look at this query from top to bottom to understand the resultset. The <code>INNER JOIN</code> on ids between Table1 and Table2 will restrict the resultset to only those records satisfied by the join condition i.e. the three rows that we saw in the very first example. This <em>temporary</em> resultset will then be <code>LEFT JOIN</code>ed to Table3 on ids between Table1 and Tables; There are records in Table3 with id 2 and 3, but not id 1, so t3.name field will have details in for 2 and 3 but not 1.</p>

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