SQL JOINS

 

Introduction: Hello Guys, “Welcome back” Today i am here with another one new great article. In this article I will explain about SQL JOINS.  SQL JOINS are used to combine rows from two or more tables in a relational database based on a related column between them. This allows for the retrieval of data that is distributed across multiple tables, enabling comprehensive analysis and reporting. SQL joins are fundamental tools for combining data from multiple tables in relational databases. For example, consider two tables where one table (say Student_details) has student information with id as a key and other table (say course) has information about marks of every student roll number. Now to display the data of every student with name, we need to join the two tables.

The primary types of SQL JOINs include:

INNER JOIN:

Returns only the rows that have matching values in both tables based on the specified join condition. The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;

Here i am going to create two tables. First one is student_details Table and second one is course_student Table
student_details

roll_no	Name	Mobile	Address	Age
101	Vikram	9601234567	#45 Sector 45, Batala	17
102	Raman	9603265748	#445 Sector 43, Amritsar	20
103	Kamal	9603265749	#489 Sector 48, Gurdaspur	20
104	Sushil	9603265750	#445 Sector 49, Dhariwal	24
105	Amit	9603265758	#432 Sector 45, Dinanagar	25
106	Vikrant	9603265760	#204 Sector 45, Dinanagar	25
107	Vishnu	9603212345	#2 Sector 45, Dinanagar	19
108	Micheal	9603265758	#445 Sector 47, Dinanagar	20

course_student

couser_id	roll_number
1	101
2	102
2	103
3	104
1	105
4	109
5	110
4	111

 

Check in the table named student_details there is column named “roll_number”. Same name of column I.e. “roll_number” is also available in the second table named course_student. The main relation in between both tables is one column that is “roll_number”. Now we need to create the below mentioned SQL Statement by using inner join that will select records that have matching values in both tables.

Query:

SELECT student_details.course_id, student.name, student.age FROM Student_details
INNER JOIN course_student
ON Student.roll_number = course_student.roll_number;

Output for the above sql statement will be as follows:

Course_id	name	age
1	Vikram	17
2	Raman	20
2	Kamal	20
3	Sushil	24
1	Amit	25

 

LEFT (OUTER)  JOIN:

Returns all rows from the left table and the matching rows from the right table. If there is no match in the right table, NULL values are returned for columns from the right table. In this example, the LEFT JOIN retrieves all rows from the Student table and the matching rows from the student_course table based on the roll_number column.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output for the above sql statement will be as follows:

Course_id	name
1	Vikram
2	Raman
2	Kamal
3	Sushil
1	Amit
Null	Vikrant
Null	Vishnu
Null	Micheal
4	Null
5	Null
4	Null

RIGHT (OUTER)  JOIN:

Right Join returns all rows from the right table and the matching rows from the left table. If there is no match in the left table, NULL values are returned for columns from the left table. It returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. It is very similar to LEFT JOIN for the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN. 

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Query:

SELECT student_details.name,student_course.course_id
FROM student_details
RIGHT JOIN student_course
ON student_course.roll_number = student_details.roll_number;

Output for the above sql statement will be as follows:

 

Course_id	name
1	Vikram
2	Raman
2	Kamal
3	Sushil
1	Amit
4	Null
5	Null
4	Null

FULL (OUTER) JOIN:

Returns all rows when there is a match in either the left or the right table, including unmatched rows from both tables, with NULL values where no match exists. creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Query:

SELECT student_details.name, student_course.course_id
FROM Studentdetails
FULL JOIN student_course
ON student_course.roll_number = student_details.roll_number;

Output for the above sql statement will be as follows:

 

Course_id	name
1	Vikram
2	Raman
2	Kamal
3	Sushil
1	Amit
Null	Vikrant
Null	Vishnu
Null	Micheal
4	Null
5	Null
4	Null

 

CROSS JOIN:

Returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table. This type of join does not require a join condition. Cross Joins a type of INNER JOIN that automatically joins two tables based on columns with the same name and data type. It returns only the rows where the values in the common columns match

SELF JOIN:

A regular join (like an INNER, LEFT, etc.) where a table is joined with itself. This is useful for comparing rows within the same table.

Conclusion: In above code, I explained that about the  SQL Join. This code is very helpful for every developer. Bye Bye and take care of you all Developers. We will come back shortly with the new attractive articles.

 

Regards

Using Asp.net