What is Join?
An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.
Following is join query that shows names of students enrolled in different courseIDs.
SELECT StudentCourse.CourseID,Student.StudentName FROM Student INNER JOIN StudentCourse ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID
Note: INNER is optional above. Simple JOIN is also considered as INNER JOIN
The above query would produce following result.
What is the difference between inner join and outer join?
Outer Join is of 3 types
1) Left outer join
2) Right outer join
3) Full Join
1) Left outer join returns all rows of table on left side of join. The rows for which there is no matching row on right side, result contains NULL in the right side.
SELECT Student.StudentName, StudentCourse.CourseID FROM Student LEFT OUTER JOIN StudentCourse ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID
Note: OUTER is optional above. Simple LEFT JOIN is also considered as LEFT OUTER JOIN
2) Right Outer Join is similar to Left Outer Join (Right replaces Left everywhere)
3) Full Outer Join Contains results of both Left and Right outer joins.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Difference between Inner Join and Outer Join in SQL
- Difference between Natural join and Inner Join in SQL
- Full join and Inner join in MS SQL Server
- Difference between Left, Right and Full Outer Join
- SQL | Join (Cartesian Join & Self Join)
- SQL | Join (Inner, Left, Right and Full Joins)
- Difference between Natural join and Cross join in SQL
- Left join and Right join in MS SQL Server
- Difference between Nested Loop Join and Hash Join
- Difference between Nested Loop join and Sort Merge Join
- Difference between Hash Join and Sort Merge Join
- Self Join and Cross Join in MS SQL Server
- SQL | EQUI Join and NON EQUI JOIN
- Lossless Join and Dependency Preserving Decomposition
- Join operation Vs Nested query in DBMS
- Join algorithms in Database
- Difference between JOIN and UNION in SQL
- Difference between Lossless and Lossy Join Decomposition
- What is PJNF(Project-Join Normal Form)?
- Join statement in JCL