Detailed JOIN

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Using the operator according to the different connection, connection condition can be divided into: Connection, unequal connection

The connection itself is divided into:

Internal connection(INNER JOIN)

(1)INNER JOIN (INNER)

Image

image

The intersection of two table.

(2)And table query

Image(1)[4]

External connection(OUTER JOIN)

       Connected with the left and right outer join will be the base table to a table, the table of contents will show all, then add two tables matching content. If the base table data in another table no record. Then the associated result set row column shows the null(NULL).

       For external connection, can also use the &ldquo (+); ” to represent. On the use of (+) some matters needing attention: 1 (+) operator can only appear in the where clause, and not with the outer join syntax and usage. 2 when using (+) operator when the connection, if in the where clause contains a number of conditions, it must be included in all conditions (+ 3) operator (+) operator only applies to the column, but not used in the expression. 4.(+)Operators shall not be used with the or and in operators. 5.(+)Operator can only be used to realize the left outer join and right outer join, and cannot be used to achieve full outer join.

       - left outer join(LEFT OUTER JOIN)

             Left join is to the left table record based, example Dave can be regarded as the left table, BL can be regarded as the right table, its result set is the data in the Dave table, plus the Dave and BL tables matching data.

           In other words, the left table (Dave) records will be expressed, while the right table (BL) will only show that match your search records. BL table records the insufficient place is NULL.

Image(2)

image

After adding TableB.id is null conditions

image

           -- right outer join(RIGHT OUTER JOIN)

              And left join the result is just the opposite, is the right table (BL) based, display so record table BL, plus Dave and BL matching results. Dave deficiencies with the NULL filling.

Image(3)

image

After adding TableA.id is null condition, becomes

image

Full outer join(FULL OUTER JOIN)

        The left and right tables do not limit, all records are displayed in table two, the insufficient place using the null filling. The external connections are not supported (+) this kind of writing.

Image(4)

image

Since the connection(SELF JOIN)

      Since the connection (self join) is connected with the often in the SQL statement, using self connection can be a mirror image of their table as another table to treat, so as to be able to get some special data.

Image(5)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Bradley at December 01, 2013 - 2:46 PM