Home » DBMS

SQL Joins Tutorial with Queries and Examples

SQL Join Tutorial - In this tutorial, we are going to learn about the SQL joins with Queries and Examples. There are two joins that we are going to discuss here - 1) Inner Join and 2) Outer Join and there sub parts.
Submitted by Shamikh Faraz, on April 20, 2018

A SQL Join statement is used to join rows as well as data from two or more than two tables. This combination is based on a common field between them.

Different types of Joins are as follows:

1. INNER JOIN
2. OUTER JOIN
   1. LEFT JOIN
   2. RIGHT JOIN
   3. FULL JOIN

Tables, which we are going to use in this tutorial

1) SQL INNER JOIN

INNER JOIN joins both the tables. This selects all rows from both the tables. This keyword will combine columns values of both the tables based on join predicate. Join predicate normally we can call the same column data in both tables like above both tables are having ‘Roll No.’ column in same. This will be join predicate. Join will continue as long as the join predicate satisfies.

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 types.

2 - A) Left Outer Join

LEFT OUTER JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records. This join gives a result set of all the rows of the left table and matching rows from the right table. If there are no matching rows on right side, this gives null. This is also 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 performs a join starting with the second/right table and then any matching second from first/left table. This join gives a result set of all the rows of the second/right table and matching rows from the first/left table. If there are no matching rows on left first/left side, this gives null. This is also 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 result will contain all the records from both the tables. If there is no matching 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