What is the difference between CROSS JOIN
and INNER JOIN
?
CROSS JOIN:
SELECT
Movies.CustomerID, Movies.Movie, Customers.Age,
Customers.Gender, Customers.[Education Level],
Customers.[Internet Connection], Customers.[Marital Status],
FROM
Customers
CROSS JOIN
Movies
INNER JOIN:
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
Which one is better and why would I use either one?
Here is the best example of Cross Join and Inner Join.
Consider the following tables
TABLE : Teacher
x------------------------x
| TchrId | TeacherName |
x----------|-------------x
| T1 | Mary |
| T2 | Jim |
x------------------------x
TABLE : Student
x--------------------------------------x
| StudId | TchrId | StudentName |
x----------|-------------|-------------x
| S1 | T1 | Vineeth |
| S2 | T1 | Unni |
x--------------------------------------x
Inner join selects the rows that satisfies both the table.
Consider we need to find the teachers who are class teachers and their corresponding students. In that condition, we need to apply JOIN
or INNER JOIN
and will
Query
SELECT T.TchrId,T.TeacherName,S.StudentName
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId
Result
x--------------------------------------x
| TchrId | TeacherName | StudentName |
x----------|-------------|-------------x
| T1 | Mary | Vineeth |
| T1 | Mary | Unni |
x--------------------------------------x
Cross join selects the all the rows from the first table and all the rows from second table and shows as Cartesian product ie, with all possibilities
Consider we need to find all the teachers in the school and students irrespective of class teachers, we need to apply CROSS JOIN
.
Query
SELECT T.TchrId,T.TeacherName,S.StudentName
FROM #Teacher T
CROSS JOIN #Student S
Result
x--------------------------------------x
| TchrId | TeacherName | StudentName |
x----------|-------------|-------------x
| T2 | Jim | Vineeth |
| T2 | Jim | Unni |
| T1 | Mary | Vineeth |
| T1 | Mary | Unni |
x--------------------------------------x