Understanding SQL Joins: Inner, Left, Right, and Full Joins

22Sep, 2023

Understanding SQL Joins: Inner, Left, Right, and Full Joins

The SQL Join statement is used to join data or rows from two or more tables based on a shared field. The following are the various sorts of Joins:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
NATURAL JOIN

Table of Contents

Two table for joins

CREATE TABLE Student_Info (
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(255),
    Phone VARCHAR(15),
    Address VARCHAR(255),
    Age INT
);

CREATE TABLE Student_Course (
    Course_ID INT,
    Roll_No INT,
    FOREIGN KEY (Roll_No) REFERENCES Student_Info(Roll_No)
);

-- Insert data into Student_Info table
INSERT INTO Student_Info (Roll_No, Name, Phone, Address, Age)
VALUES
    (1, 'John Doe', '123-456-7890', 'Mumbai', 20),
    (2, 'Jane Smith', '987-654-3210', 'Delhi', 22),
    (3, 'Amit Patel', '555-123-4567', 'Bangalore', 21),
    (4, 'Priya Singh', '333-888-9999', 'Chennai', 23);

-- Insert data into Student_Course table
INSERT INTO Student_Course (Course_ID, Roll_No)
VALUES
    (101, 1),
    (102, 2),
    (101, 3),
    (103, 4);

A. INNER JOIN

As long as the condition is met, the INNER JOIN keyword selects all rows from both tables. This keyword will generate the result-set by combining all rows from both tables that satisfy the requirement, i.e. the value of the common field is the same.

syntex :

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

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

Example of Queries with(INNER JOIN)

SELECT SI.Roll_No, SI.Name, SI.Phone, SI.Address, SI.Age, SC.Course_ID
FROM Student_Info SI
INNER JOIN Student_Course SC ON SI.Roll_No = SC.Roll_No;

This SQL query joins the “Student_Info” table with the “Student_Course” table using the “Roll_No” column as the common key. The output will include columns from both tables for students who are enrolled in courses, and it will match the students from “Student_Info” with the courses from “Student_Course.”
Columns such as Roll_No, Name, Phone, Address, Age from the “Student_Info” database and Course_ID from the “Student_Course” table will be included in the output, displaying information about students and the courses they are enrolled in.

B. LEFT JOIN

This join returns all rows from the table to the left of the join and matches rows from the table to the right of the join. The result-set will contain null for rows for which there is no matching row on the right side. LEFT OUTER JOIN is another name for LEFT JOIN.

Syntax:

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

table1: First table.
table2: Second table
matching_column: Column common to both the tables

Example of Queries with( LEFT JOIN)

SELECT Student.Name, StudentCourse.Course_ID 
FROM Student
LEFT JOIN StudentCourse ON StudentCourse.Roll_No = Student.Roll_No;

C. RIGHT JOIN

RIGHT JOIN is identical to LEFT JOIN. This join retrieves all rows from the table on the right side of the join as well as matching rows from the table on the left. The result-set will contain null for rows for which there is no matching row on the left side. RIGHT OUTER JOIN is another name for RIGHT JOIN.

Syntax:

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

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

SELECT Student.Name, StudentCourse.Course_ID 
FROM Student
RIGHT JOIN StudentCourse ON StudentCourse.Roll_No = Student.Roll_No;

D. FULL JOIN

The result-set is created by integrating the results of both LEFT JOIN and RIGHT JOIN. The result set will include all rows from both tables. The result-set will contain NULL values for rows where there is no match.

Syntax:

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

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

SELECT Student.Name, StudentCourse.Course_ID 
FROM Student
FULL JOIN StudentCourse ON StudentCourse.Roll_No = Student.Roll_No;

This query will return the names of students along with their corresponding course IDs. It performs a FULL JOIN, which means it will include all records from both the “Student” and “StudentCourse” tables, matching records where the “Roll_No” values are the same and including NULL values where there are no matches.

E. Natural join

A natural join in a database operation combines two tables based on shared column names and data types. It returns all rows where the values in these common columns match. For this type of join to work, both tables must have at least one column with the same name and data type.

When performing a natural join, the database management system (DBMS) looks for columns with identical names and data types in both tables. It then combines the rows from both tables where these common column values are equal.

In contrast, a cross join combines every row from one table with every row from another table, resulting in a larger Cartesian product of the two tables. It doesn’t rely on matching columns but instead pairs every row with every other row from the two tables.

In summary, a natural join is a join operation that focuses on matching values in common columns with the same name and data type, while a cross join pairs every row from one table with every row from another, regardless of column names or values.

What is a SQL Join?

A SQL Join is a clause used to combine rows from two or more tables based on a related column between them.

What is an Inner Join?

An Inner Join retrieves records that have matching values in both tables. Rows from the tables that do not meet the condition are excluded from the result set.

How do I write a basic Inner Join query?

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

What is a Left Join?

A Left Join returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.

How do I use a Left Join?

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

What is a Right Join?

A Right Join is similar to a Left Join, but it returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.

How do I use a Right Join?

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

What is a Full Join?

A Full Join returns all records when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the table without a match.

How do I use a Full Join?

SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;