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

Table 1 for SQL JOIN
Table 2 for SQL JOIN

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.

SQL - Inner Join

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:

SQL Inner join 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.

SQL Left Outer 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

SQL Left Outer join 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.

SQL Right Outer 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

SQL Right Outer join 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.

SQL Full join

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

SQL full join Output





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.