Understanding of SQL JOIN with Examples

 Understanding of SQL JOIN with Examples

SQL JOIN :-

SQL Join is used to combine data or row from two or more tables based upon common field between them.

Different Type of Joins:- Below are the list of Joins in SQL

1) Inner Join

2) Left Join

3) Right Join

4) Full Outer join

5) Self Join

6) Cross Join

1) Inner Join : 

Inner join Joins two or more table and returns only matching data from table.

Syntax :       SELECT table1.column1,table1.column2,table2.column1  

FROM table1 

INNER JOIN table2

ON table1.matching_column = table2.matching_column

Consider the below two table as follow, 

1) When you join both the table using Inner Join as per the below query.

  SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE 

                            FROM Student

                            INNER JOIN StudentCourse

                            ON Student.ROLL_NO = StudentCourse.ROLL_NO;

2) Left Join :

This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join.  For the rows for which there is no matching row on the right side, the result-set will contain null. 

LEFT JOIN is also known as LEFT OUTER JOIN

Example Queries(LEFT JOIN)

SELECT Student.NAME,StudentCourse.COURSE_ID 

FROM Student

LEFT JOIN StudentCourse 

ON StudentCourse.ROLL_NO = Student.ROLL_NO;

 3) Right Join (Right Outer Join) :

 This join 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. 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.

Example Queries(Right JOIN)

SELECT Student.NAME,StudentCourse.COURSE_ID 

FROM Student

RIGHT JOIN StudentCourse 

ON StudentCourse.ROLL_NO = Student.ROLL_NO;

  4) Full Outer Join(Full Join) 

 FULL JOIN 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.

Example Queries(Full JOIN)

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

5) Self Join :

• A self join allows you to join a table to itself.
•  It helps query hierarchical data or compare rows within the same table

SQL Server self join examples

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

Output as Below -

6) Cross Join :
Cross join will return all the rows from both the tables and it is also called Cartesian join 

Example If one table has 10 records and another table contain 5 records, then cross join will return total 50 row from cross join.