SQL Server 2008의 CROSS JOIN과 INNER JOIN

SQL Server 2008의 CROSS JOIN과 INNER JOIN

---

차이점은 무엇이며 CROSS JOIN그리고 INNER JOIN?

크로스 가입 :

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status], 
FROM   
    Customers 
CROSS JOIN 
    Movies

내부 가입 :

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status]
FROM   
    Customers 
INNER JOIN 
    Movies ON Customers.CustomerID = Movies.CustomerID

어느 것이 더 좋으며 왜 두 가지를 사용합니까?

---

교차 조인은 행을 결합하지 않습니다. 각 테이블에 100 개의 행이 있고 1 대 1이 일치하면 10.000 개의 결과가 표시되며 동일한 상황에서 Innerjoin은 100 개의 행만 반환합니다.

이 두 예제는 동일한 결과를 반환합니다.

크로스 조인

select * from table1 cross join table2 where table1.id = table2.fk_id

내부 가입

select * from table1 join table2 on table1.id = table2.fk_id

마지막 방법을 사용하십시오

---

Cross Join 및 Inner Join의 가장 좋은 예는 다음과 같습니다.

다음 표를 고려하십시오

표 : Teacher

x------------------------x
| TchrId   | TeacherName | 
x----------|-------------x
|    T1    |    Mary     |
|    T2    |    Jim      |
x------------------------x

표 : Student

x--------------------------------------x
|  StudId  |    TchrId   | StudentName | 
x----------|-------------|-------------x            
|    S1    |     T1      |    Vineeth  |
|    S2    |     T1      |    Unni     |
x--------------------------------------x

1. 내부 가입

내부 조인은 테이블을 모두 만족시키는 행을 선택합니다 .

우리는 수업 교사 인 교사와 해당 학생을 찾아야한다고 생각하십시오. 그 상태에서, 우리는 적용 할 필요가 JOIN또는 INNER JOIN과 의지

질문

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId

• SQL FIDDLE

결과

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

2. 크로스 가입

교차 조인은 첫 번째 테이블의 모든 행과 두 번째 테이블의 모든 행을 선택하고 직교 제품으로 표시합니다. 즉, 모든 가능성과 함께

학교의 모든 교사를 찾아야하며 수업 교사에 관계없이 학생들을 찾아야합니다. CROSS JOIN.

질문

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
CROSS JOIN #Student S 

• SQL FIDDLE

결과

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T2    |     Jim     |    Vineeth  |
|    T2    |     Jim     |    Unni     |
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

---

CROSS JOIN = (INNER) JOIN = 쉼표 ( ",")

TL; DR SQL CROSS JOIN, (INNER) JOIN과 쉼표 ( ",") (평가 순서의 우선 순위가 낮은 쉼표 제외)의 유일한 차이점은 CRINS JOIN과 쉼표가 아닌 (INNER) JOIN은 ON입니다.

---

재 중간 제품

세 가지 모두는 각 테이블에서 가능한 모든 행 조합의 중간 개념 SQL 스타일 관계형 "카테 시안"제품 (일명 크로스 조인)을 생성합니다. 행 수를 줄이는 것은 ON 및 / 또는 WHERE입니다. SQL 바이올린

SQL 표준 은 제품 (7.5 1.b.ii)을 통한 <comma>, <comma> (7.7 1.a)를 통한 <cross join> 및 <comma> + WHERE (7.7 1.b)를 통한 <search condition>에 대해 정의합니다. ).

Wikipedia는 다음과 같이 말합니다.

크로스 조인

CROSS JOIN은 조인의 테이블에서 행의 카티 전 곱을 반환합니다. 즉, 첫 번째 테이블의 각 행과 두 번째 테이블의 각 행을 결합하는 행을 생성합니다.

내부 가입

[...] The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B) and then returning all records which satisfy the join predicate.

The "implicit join notation" simply lists the tables for joining, in the FROM clause of the SELECT statement, using commas to separate them. Thus it specifies a cross join

Re OUTER JOINs and using ON vs WHERE in them see Conditions in LEFT JOIN (OUTER JOIN) vs INNER JOIN.

Why compare columns between tables?

When there are no duplicate rows:

Every table holds the rows that make a true statement from a certain fill-in-the-[named-]blanks statement template. (It makes a true proposition from--satisfies--a certain (characteristic) predicate.)

• A base table holds the rows that make a true statement from some DBA-given statement template:

  /* rows where
  customer C.CustomerID has age C.Age and ...
  */
  FROM Customers C
  

• A join's intermediate product holds the rows that make a true statement from the AND of its operands' templates:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  */
  FROM Customers C CROSS JOIN Movies M
  

• ON & WHERE conditions are ANDed in to give a further template. The value is again the rows that satisfy that template:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  AND C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  AND C.Age = 18
  */
  FROM Customers C INNER JOIN Movies M
  ON C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  WHERE C.Age = 18
  

In particular, comparing columns for (SQL) equality between tables means that the rows kept from the product from the joined tables' parts of the template have the same (non-NULL) value for those columns. It's just coincidental that a lot of rows are typically removed by equality comparisons between tables--what is necessary and sufficient is to characterize the rows you want.

Just write SQL for the template for the rows you want!

Re the meaning of queries (and tables vs conditions) see:
How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
Is there any rule of thumb to construct SQL query from a human-readable description?

Overloading "cross join"

Unfortunately the term "cross join" gets used for:

• The intermediate product.
• CROSS JOIN.
• (INNER) JOIN with an ON or WHERE that doesn't compare any columns from one table to any columns of another. (Since that tends to return so many of the intermediate product rows.)

These various meanings get confounded. (Eg as in other answers and comments here.)

Using CROSS JOIN vs (INNER) JOIN vs comma

The common convention is:

• Use CROSS JOIN when and only when you don't compare columns between tables. That is to show that the lack of comparisons was intentional.
• Use (INNER) JOIN with ON when and only when you compare columns between tables. (Plus possibly other conditions.)
• Don't use comma.

Typically also conditions not on pairs of tables are kept for a WHERE. But they may have to be put in a(n INNER) JOIN ON to get appropriate rows for the argument to a RIGHT, LEFT or FULL (OUTER) JOIN.

Re "Don't use comma" Mixing comma with explicit JOIN can mislead because comma has lower precedence. But given the role of the intermediate product in the meaning of CROSS JOIN, (INNER) JOIN and comma, arguments for the convention above of not using it at all are shaky. A CROSS JOIN or comma is just like an (INNER) JOIN that's ON a TRUE condition. An intermediate product, ON and WHERE all introduce an AND in the corresponding predicate. However else INNER JOIN ON can be thought of--say, generating an output row only when finding a pair of input rows that satisfies the ON condition--it nevertheless returns the cross join rows that satisfy the condition. The only reason ON had to supplement comma in SQL was to write OUTER JOINs. Of course, an expression should make its meaning clear; but what is clear depends on what things are taken to mean.

Re Venn diagrams A Venn diagram with two intersecting circles can illustrate the difference between output rows for INNER, LEFT, RIGHT & FULL JOINs for the same input. And when the ON is unconditionally TRUE, the INNER JOIN result is the same as CROSS JOIN. Also it can illustrate the input and output rows for INTERSECT, UNION & EXCEPT. And when both inputs have the same columns, the INTERSECT result is the same as for standard SQL NATURAL JOIN, and the EXCEPT result is the same as for certain idioms involving LEFT & RIGHT JOIN. But it does not illustrate how (INNER) JOIN works in general. That just seems plausible at first glance. It can identify parts of input and/or output for special cases of ON, PKs (primary keys), FKs (foreign keys) and/or SELECT. All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles. (Which muddled presentations never make clear.) (Remember that in general for joins output rows have different headings from input rows. And SQL tables are bags not sets of rows with NULLs.)

---

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.

Syntax for Inner Join

SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name

Cross Join

A cross join that produces Cartesian product of the tables that involved in the join. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

Syntax for Cross Join

SELECT * FROM table_name1
CROSS JOIN table_name2

Or we can write it in another way also

SELECT * FROM table_name1,table_name2

Now check the query below for Cross join

Example

SELECT * FROM UserDetails
CROSS JOIN OrderDetails

Or

SELECT * FROM UserDetails, OrderDetails

---

Please remember, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set. Please refer to http://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

---

SQL Server also accepts the simpler notation of:

SELECT A.F, 
       B.G, 
       C.H 
  FROM TABLE_A A, 
       TABLE_B B, 
       TABLE_C C
 WHERE A.X = B.X 
   AND B.Y = C.Y

Using this simpler notation, one does not need to bother about the difference between inner and cross joins. Instead of two "ON" clauses, there is a single "WHERE" clause that does the job. If you have any difficulty in figuring out which "JOIN" "ON" clauses go where, abandon the "JOIN" notation and use the simpler one above.

It is not cheating.

---

While writing queries using inner joins the records will fetches from both tables if the condition satisfied on both tables, i.e. exact match of the common column in both tables.

While writing query using cross join the result is like cartesian product of the no of records in both tables. example if table1 contains 2 records and table2 contains 3 records then result of the query is 2*3 = 6 records.

So dont go for cross join until you need that.

---

Cross join and inner join are the same with the only difference that in inner join we booleanly filter some of the outcomes of the cartesian product

table1
x--------------------------------------x
|  fieldA  |    fieldB   |    fieldC   | 
x----------|-------------|-------------x            
|    A     |      B      |    option1  |
|    A     |      B1     |    option2  |
x--------------------------------------x

table2
x--------------------------------------x
|  fieldA  |    fieldB   |    fieldC   | 
x----------|-------------|-------------x            
|    A     |      B      |    optionB1 |
|    A1    |      B1     |    optionB2 |
x--------------------------------------x

 cross join
  A,B,option1,A,B,optionB1
  A,B,option1,A1,B1,optionB2
  A,B1,option2,A,B,optionB1
  A,B1,option2,A1,B1,optionB2

 inner join on field1 (only with the value is the same in both tables)
  A,B,option1,A,B,optionB1
  A,B1,option2,A,B,optionB1

 inner join on field1
  A,B,option1,A,B,optionB1

It is on design of our data where we decide that there is only one case of the field we are using for the join. Join only cross join both tables and get only the lines accomplishing special boolean expression.

Note that if the fields we are doing our Joins on would be null in both tables we would pass the filter. It is up to us or the database manufacturer to add extra rules to avoid or permit nulls. Adhering to the basics it is just a cross join followed by a filter.

---

The inner join will give the result of matched records between two tables where as the cross join gives you the possible combinations between two tables.

참고 URL : https://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008