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
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.
A SQL Join is a clause used to combine rows from two or more tables based on a related column between them.
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.
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
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.
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
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.
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
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.
SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;
