SQL Joins Tutorial with Queries and Examples

SQL Join Tutorial – In this instructional exercise, we will find out about the SQL gets together with Queries and Examples. There are two joins that we will talk about here – 1) Inner Join and 2) Outer Join and there sub parts.

A SQL Join explanation is utilized to join pushes just as information from at least two than two tables. This blend depends on a typical field between them.

Various sorts of Joins are as per the following:

1. Inward JOIN
2. External JOIN
   1. LEFT JOIN
   2. RIGHT JOIN
   3. FULL JOIN

Tables, which we are going to use in this instructional exercise

1) SQL INNER JOIN

Inward JOIN joins both the tables. This chooses all columns from both the tables. This watchword will consolidate segments values of both the tables dependent on join predicate. Join predicate typically we can call a similar segment information in the two tables like above the two tables are having ‘Roll No.’ segment in same. This will be join predicate. Join will proceed as long as the join predicate fulfills.

Syntax:

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

table1: Student_table
table2: Course_table
join predicate: Roll No.

Code to Join

SELECT Course_table.COURSE_ID, Student_table.NAME, Student_table.AGE 
FROM Student_table
INNER JOIN Student_table
ON Student_table.ROLL_NO = Course_table.ROLL_NO;

Output:

2) SQL OUTER JOIN

This is of three kinds.

2 – A) Left Outer Join

LEFT OUTER JOIN plays out a get beginning together with the principal (left-generally) table and afterward any coordinating second (right-most) table records. This join gives an outcome set of the considerable number of columns of the left table and coordinating lines from the correct table. On the off chance that there are no coordinating lines on right side, this gives invalid. This is likewise called left join.

Syntax:

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

table1: Student_table
table2: Course_table
join predicate: Roll No.

Code to Join

SELECT Student_table.NAME, Course_table.COURSE_ID 
FROM Student_table
LEFT JOIN Course_table 
ON Course_table.ROLL_NO = Student_table.ROLL_NO;

Output:

2 – B) Right Outer Join

Right Outer JOIN plays out a get beginning together with the second/right table and afterward any coordinating second from first/left table. This join gives an outcome set of the considerable number of lines of the second/right table and coordinating lines from the main/left table. In the event that there are no coordinating columns on left first/left side, this gives invalid. This is additionally called right join.

Syntax:

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

table1: Student_table
table2: Course_table
join predicate: Roll No.

Code to Join

SELECT Student_table.NAME, Course_table.COURSE_ID 
FROM Student_table
RIGHT JOIN Course_table 
ON Course_table.ROLL_NO = Student_table.ROLL_NO;

Output:

2 – C) Full Join

FULL JOIN joins both LEFT JOIN and RIGHT JOIN. The outcome will contain all the records from both the tables. On the off chance that there is no coordinating record, there will be NULL values.

Syntax:

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

table1: Student_table
table2: Course_table
join predicate: Roll No.

Code to Join

SELECT Student_table.NAME, Course_table.COURSE_ID 
FROM Student_table
FULL JOIN Course_table 
ON Course_table.ROLL_NO = Student_table.ROLL_NO;

Output: