|
他们彼此深信，是瞬间迸发的热情让他们相遇。这样的确定是美丽的，但变幻无常更为美丽 |
|
Specifies the type of join operation. INNER Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified. LEFT [OUTER] Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join. RIGHT [OUTER] Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns from the left table are set to NULL in addition to all rows returned by the inner join. FULL [OUTER] If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join. JOIN Is a keyword to indicate that an SQL-92 style join be used in the delete operation. ON Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example: FROM Suppliers JOIN Products ON (Suppliers.SupplierID = Products.SupplierID) When the condition specifies columns, they need not have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function. For more information about search conditions and predicates, see Search Condition. CROSS JOIN Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join. I will show some results of the SQL statement ,it 's important that we need to understant the Join ,because the SQL statement is based on the join ,especially the quiery from one more tables. --SQL code IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tb1' AND type = 'U') DROP TABLE tb1 GO create table tb1( id int) insert tb2 select 1 union all select 1 union all select 2 union all select 3 IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tb2' AND type = 'U') DROP TABLE tb2 GO create table tb2 ( id int) insert tb2 select 1 union all select 1 union all select 2 union all select 4 union all select 5 union all select 6 --INNER Join select * from tb1 a inner join tb2 b on a.id=b.id /* id id 1 1 1 1 1 1 1 1 2 2 */ --Full(outer) Join select * from tb1 a full outer join tb2 b on a.id=b.id /* id id 1 1 1 1 1 1 1 1 2 2 NULL 4 NULL 5 NULL 6 3 NULL */ --LEFT(outer ) select * from tb1 a left outer join tb2 b on a.id=b.id /* id id 1 1 1 1 1 1 1 1 2 2 3 NULL */ --RIGHT(OUTER) JOIN select * from tb1 a right outer join tb2 b on a.id=b.id /* id id 1 1 1 1 1 1 1 1 2 2 NULL 4 NULL 5 NULL 6 */ |
