Thursday, 5 November 2015

CROSS JOIN vs INNER JOIN in SQL Server

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
 
 
 
Cross join does not combine the rows, if you have 100 rows in each table with 1 to 1 match, you get 10.000 results, Innerjoin will only return 100 rows in the same situation.
These 2 examples will return the same result:

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

select * from table1 join table2 on table1.id = table2.fk_id
Use the last method


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
enter image description here
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.
enter image description here
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

 

 

No comments:

Post a Comment

Row based styling for Primeng DataTable using Angualr 2,4

------------------------------------Component.Html---------------- < p -dataTable id = "Tbl" [ rowStyleClass ]= " Fu...