sql server cross join,full join

http://msdn.microsoft.com/en-us/library/ms190690.aspx

 

没有where条件（针对两表关联的条件），A表 10条记录。B表 17条记录。结果是170记录。 

当有where条件的时候, 则cross join 等于 inner join

 

所有join的参考：(inner join, left join,right join,self join,cross join,full join)

Before we jump into code, let's provide some baseline information on the joins options in SQL Server:

• INNER JOIN - Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data.  Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.
  • Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and/or queries.  Although that is the case in some environments, it is really dependent on the database design, referential integrity and data needed for the application.  As such, please take the time to understand the data being requested then select the proper join option.
  • Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.
• LEFT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the left table.  On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table.
  • Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite of one another.  So you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.
• RIGHT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the right table.  On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.
• Self -Join - In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.
• CROSS JOIN - Based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows.  The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table.  Please heed caution when using a CROSS JOIN.
• FULL JOIN - Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

Let's walk through examples from the AdventureWorks sample database that is available for SQL Server to provide examples of each type of join then provide some insight into the usage and sample result sets.

 

cross join vs full join:

Yes, there is a definite difference between them. A cross join is a Cartesian join, for sets of A and B rows, you'll get a result of A * B rows. A full join will match all possible rows, meaning it will return AT MOST A + B rows. For large values of A and B, the difference can be huge.