# CROSS JOIN vs INNER JOIN in SQL

### The Solution to CROSS JOIN vs INNER JOIN in SQL is

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
``````

## 1. INNER JOIN

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
``````

## 2. CROSS JOIN

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
``````

~ Answered on 2014-02-15 06:37:42