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:
- INNER JOIN
-
OUTER JOIN
- LEFT JOIN
- RIGHT JOIN
- 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