CROSS JOIN vs INNER JOIN in SQL

by

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

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  4. How to generate a range of numbers between two numbers?
  5. SET versus SELECT when assigning variables?
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Split function equivalent in T-SQL?
  8. Generate Dates between date ranges
  9. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  10. How to print VARCHAR(MAX) using Print Statement?
  11. Where value in column containing comma delimited values
  12. Check if a row exists, otherwise insert
  13. How to update Identity Column in SQL Server?
  14. SQL query to split column data into rows
  15. Common Table Expression, why semicolon?
  16. T-SQL split string based on delimiter
  17. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  18. Concatenate values based on ID
  19. Splitting delimited values in a SQL column into multiple rows
  20. SQL “between” not inclusive
  21. Can I set ignore_dup_key on for a primary key?
  22. Using PIVOT in SQL Server 2008
  23. Stored procedure that exports data into csv files only exports to one file
  24. How do I prevent a database trigger from recursing?
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. How to combine results of two queries into a single dataset
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. SQL take just the numeric values from a varchar
  30. Cumulative Total in MS Sql server [duplicate]

Leave a Comment