SQL joins are used to join database tables based on a common field

There are different types of SQL joins

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

In this lesson we are working with the student and the course table

These are the key point to be conceded about the two tables

The student table has records which contain NULL values for the courseID and that is the student who are not following any courses

The course table has a course called Database System which is not followed by any student in the student table

What is common in both tables is courseID

Now let’s join the tables to get the student information and course details inside one table

Please go through each type of SQL JOIN examples and the results is given below

INNER JOIN

INNTER JOIN selects all the records which include matching values in the common filed

Here is the SQL query with the INNER JOIN

SELECT student.studentID, student.FirstName, student.LastName, course.CourseTitle, course.Duration
FROM student INNER JOIN course ON student.courseID = course.courseID 

The query selects only records which have matching values in the common field. It excludes the records which has NULL values in the common field of the student (Left) table and the course Database Systems from the course (Right) table under courseID = 5 which is not a matching value in the common filed

The result includes all the students form the student (Left) table excluding the students who are not following any course and all the courses from the course(Right) table excluding the course Database System which is not followed by any student

LEFT OUTER JOIN

Left JOIN selects all the records from the left table and records which contain matching values in the common filed from the right table and returns NULL in the right side of the table where values are not matched.  

Here is the SQL query with LEFT OUTER JOIN

SELECT student.studentID, student.FirstName, student.LastName, course.CourseTitle, course.Duration
FROM student LEFT OUTER JOIN course ON student.courseID = course.courseID 

LEFT OUTER JOIN selects all the students and the information about the courses their following without losing the student who are not following any courses which will return as NULL values

Database Systems is under courseID = 5 in the course table which is not a matching value in the common filed has been excluded

The result contains` all the students form the student (Left) table including the students who are not following any course and all the courses from the course (Right) table without the course Database System which is not followed by any student

RIGHT OUTER JOIN

RIGHT OUTER JOIN selects all the records from the right table and only records which contain matching values in the common filed from the left table and returns NULL in the left side of the table where values are not matched

RIGHT OUTER JOIN is the opposite of the LEFT OUTER JOIN

Here is the SQL query with the RIGHT OUTER JOIN

SELECT student.studentID, student.FirstName, student.LastName, course.CourseTitle, course.Duration
FROM student RIGHT OUTER JOIN course ON student.courseID = course.courseID 

The RIGHT OUTER JOIN will exclude records which has NULL values in the common filed of the student (Left) table but it will returns the course Database System under courseID = 5 in the common filed of the course(Right) table which is not a matching value in the common filed

The result contains all the courses from the course table including the course Database System which is not followed by any students and the students form the student table excluding the students who are not following any courses

FULL OUTER JOIN

FULL OUTER JOIN selects all the records from both left and right tables and it will have NULL values in the both side of the records which have no matching values in the common field

Here is the SQL query with the FULL OUTER JOIN

SELECT student.studentID, student.FirstName, student.LastName, course.CourseTitle, course.Duration
FROM student FULL OUTER JOIN course ON student.courseID = course.courseID 

The FULL OUTER JOIN selects all the records form the student (Left) and the course (Right) table and returns NULL values on either side of the joined table records in which values are not matched. 

The result includes all the students form the student (Left) table including the students who are not following any course and all the courses from the course (Right) table including the course Database Systems which is not followed by any student. We can also find NULL values on either side of the joined table records which have no matching values in the common field