Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The following was taken from the article "<a href="http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html">MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN</a>" by Graham Ellis on his blog Horse's Mouth.</p> <p>In a database such as MySQL, data is divided into a number of tables which are then connected (<code>Joined</code>) together by <code>JOIN</code> in <code>SELECT</code> commands to read records from multiple tables. Read this example to see how it works.</p> <p>First, some sample data:</p> <pre><code>people mysql&gt; select * from people; +------------+--------------+------+ | name | phone | pid | +------------+--------------+------+ | Mr Brown | 01225 708225 | 1 | | Miss Smith | 01225 899360 | 2 | | Mr Pullen | 01380 724040 | 3 | +------------+--------------+------+ 3 rows in set (0.00 sec) property mysql&gt; select * from property; +------+------+----------------------+ | pid | spid | selling | +------+------+----------------------+ | 1 | 1 | Old House Farm | | 3 | 2 | The Willows | | 3 | 3 | Tall Trees | | 3 | 4 | The Melksham Florist | | 4 | 5 | Dun Roamin | +------+------+----------------------+ 5 rows in set (0.00 sec) </code></pre> <p><strong>REGULAR JOIN</strong></p> <p>If we do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then we get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:</p> <pre><code>mysql&gt; select name, phone, selling from people join property on people.pid = property.pid; +-----------+--------------+----------------------+ | name | phone | selling | +-----------+--------------+----------------------+ | Mr Brown | 01225 708225 | Old House Farm | | Mr Pullen | 01380 724040 | The Willows | | Mr Pullen | 01380 724040 | Tall Trees | | Mr Pullen | 01380 724040 | The Melksham Florist | +-----------+--------------+----------------------+ 4 rows in set (0.01 sec) </code></pre> <p><strong>LEFT JOIN</strong></p> <p>If we do a LEFT JOIN, we get all records that match in the same way and IN ADDITION we get an extra record for each unmatched record in the left table of the join - thus ensuring (in this example) that every PERSON gets a mention:</p> <pre><code> mysql&gt; select name, phone, selling from people left join property on people.pid = property.pid; +------------+--------------+----------------------+ | name | phone | selling | +------------+--------------+----------------------+ | Mr Brown | 01225 708225 | Old House Farm | | Miss Smith | 01225 899360 | NULL &lt;&lt;-- unmatch | | Mr Pullen | 01380 724040 | The Willows | | Mr Pullen | 01380 724040 | Tall Trees | | Mr Pullen | 01380 724040 | The Melksham Florist | +------------+--------------+----------------------+ 5 rows in set (0.00 sec) </code></pre> <p><strong>RIGHT JOIN</strong></p> <p>If we do a RIGHT JOIN, we get all the records that match and IN ADDITION an extra record for each unmatched record in the right table of the join - in my example, that means that each property gets a mention even if we don't have seller details:</p> <pre><code>mysql&gt; select name, phone, selling from people right join property on people.pid = property.pid; +-----------+--------------+----------------------+ | name | phone | selling | +-----------+--------------+----------------------+ | Mr Brown | 01225 708225 | Old House Farm | | Mr Pullen | 01380 724040 | The Willows | | Mr Pullen | 01380 724040 | Tall Trees | | Mr Pullen | 01380 724040 | The Melksham Florist | | NULL | NULL | Dun Roamin | +-----------+--------------+----------------------+ 5 rows in set (0.00 sec) </code></pre> <p>An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatibility and doesn't add an extra capabilities.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat is the difference between "INNER JOIN" and "OUTER JOIN"?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPhiLho
    1. USgtiwari333
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO`REGULAR JOIN` and `INNER JOIN` are the same thing. What Graham Ellis wanted to say whit `REGULAR` is that INNER JOIN is the "default" JOIN, when none of `LEFT` or `RIGHT` keywords were specified. If you read the whole post, in the end He says "An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatibility and doesn't add an extra capabilities."
      singulars
      1. PO
      1. USvegatripy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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