22Sep, 2023
Understanding SQL Joins: Inner, Left, Right, and Full Joins
Understanding SQL Joins: Inner, Left, Right, and Full Joins

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

SQL Joins are used to combine records from two or more tables in a database based on related columns. These joins allow you to retrieve data from multiple tables efficiently, depending on the type of relationship and the desired results. The most common SQL joins are Inner Join, Left Join, Right Join, and Full Join. Below, we’ll explore each of them and how they work with examples to help you understand their usage better.

1. Inner Join

An Inner Join returns only the rows where there is a match in both tables. If no match is found, the row is excluded from the result set.

Example:

SELECT Orders.order_id, Users.username
FROM Orders
INNER JOIN Users ON Orders.user_id = Users.user_id;

In this example, the query returns only the orders where the user exists in the Users table.

2. Left Join

A Left Join returns all rows from the left table (first table) and the matching rows from the right table (second table). If there is no match, NULL values will appear for columns from the right table.

Example:

SELECT Users.username, Orders.order_id
FROM Users
LEFT JOIN Orders ON Users.user_id = Orders.user_id;

In this case, the query returns all users, including those without any orders, showing NULL for the order_id of users without orders.

3. Right Join

A Right Join is the opposite of the Left Join. It returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values will appear for columns from the left table.

Example:

SELECT Orders.order_id, Users.username
FROM Orders
RIGHT JOIN Users ON Orders.user_id = Users.user_id;

This query returns all users, and their corresponding orders, but even users with no orders are shown with NULL in the order_id column.

4. Full Join

A Full Join returns all rows when there is a match in one of the tables. It returns NULL values for columns where there is no match in either the left or right table.

Example:

SELECT Users.username, Orders.order_id
FROM Users
FULL JOIN Orders ON Users.user_id = Orders.user_id;

This query returns all users and all orders. If there is no matching record for either the user or the order, NULL will appear in the respective columns.

SQL Joins are an essential part of relational databases, helping to combine data from multiple tables. By understanding these different types of joins, you can better design and query your databases to obtain the necessary data in a more efficient and meaningful way.

Two table for joins

table for joins in sql 
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

The INNER JOIN keyword is used to combine rows from two or more tables based on a related column between them. As long as the condition is met, INNER JOIN selects all rows from both tables that have matching values in the common field. This is one of the most commonly used SQL joins for combining data from multiple tables.

How INNER JOIN Works

The INNER JOIN combines rows from two tables where the joined fields match in both tables. If there is no match, the row is excluded from the result set. This makes it an efficient way to retrieve data when you want only the records that have corresponding matches in both tables.

Example of INNER JOIN

Let’s say you have two tables: Customers and Orders. You want to retrieve a list of customers who have placed orders. The common column between the two tables is customer_id.

SELECT Customers.customer_name, Orders.order_id
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

In this example, the query returns all customers who have placed orders, with their corresponding order IDs. If a customer has not placed any orders, they will not appear in the result set.

The INNER JOIN is powerful for ensuring that you only get rows where there is a valid, matching relationship between tables. It is one of the most commonly used joins in SQL.

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)

INNER JOIN Example

SQL Query:

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 demonstrates the use of an INNER JOIN to combine the data from two tables: Student_Info and Student_Course.

How the Query Works:

In this query, we are selecting the following columns from the Student_Info table:

  • Roll_No
  • Name
  • Phone
  • Address
  • Age

Additionally, we are selecting the Course_ID from the Student_Course table. The query uses the INNER JOIN clause to link the two tables using the Roll_No column, which is common to both tables. Only records that have matching Roll_No values in both tables will be included in the result set.

Result Explanation:

The result of this query will include:

  • Roll_No: The student’s unique roll number from the Student_Info table.
  • Name: The name of the student from the Student_Info table.
  • Phone: The student’s phone number from the Student_Info table.
  • Address: The student’s address from the Student_Info table.
  • Age: The student’s age from the Student_Info table.
  • Course_ID: The ID of the course the student is enrolled in, from the Student_Course table.

This query provides a comprehensive view of the student’s personal information alongside the courses they are enrolled in. It ensures that only students who have enrolled in a course will appear in the result set.

This example shows how INNER JOIN helps in combining information from multiple tables, ensuring that only relevant data, where there is a relationship between the tables, is included in the results.

B. LEFT JOIN

LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN, also known as LEFT OUTER JOIN, returns all rows from the table on the left side of the join, and matches rows from the table on the right side. If no matching row is found in the right table, NULL values will appear for columns from the right table in the result set.

Syntax:

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

Explanation:

In this syntax:

  • table1: The first table (left table) to join.
  • table2: The second table (right table) to join.
  • matching_column: The column that is common to both tables and used to match the data.

The LEFT JOIN ensures that all rows from the left table are included in the result set. If there are no corresponding rows in the right table, NULL values will be displayed in the columns of the right table. This is particularly useful when you want to show all records from the left table, even if some related data might be missing in the right table.

Example

Consider the following SQL query:

SELECT Employee.Name, Department.Department_Name
FROM Employee
LEFT JOIN Department ON Employee.Department_ID = Department.Department_ID;

In this example, the query selects employee names along with their department names. If an employee does not belong to a department (i.e., no matching Department_ID), the result will include the employee’s name with a NULL value for the department name.

Example of Queries with( LEFT JOIN) 

SELECT Student.Name, StudentCourse.Course_ID 
FROM Student
LEFT JOIN StudentCourse ON StudentCourse.Roll_No = Student.Roll_No;
left join output in sql

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;
output of right join in sql

D. FULL JOIN

FULL JOIN (FULL OUTER JOIN)

The FULL JOIN, also known as FULL OUTER JOIN, combines the results of both LEFT JOIN and RIGHT JOIN. It returns all rows from both tables. If there is no match between the tables, NULL values are included for the columns from the table that does not have a matching row.

Syntax:

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

Explanation:

In this syntax:

  • table1: The first table (left table) to join.
  • table2: The second table (right table) to join.
  • matching_column: The column common to both tables used to match the data.

The FULL JOIN ensures that all records from both the left and right tables are included in the result set. If there is no match, the result will include NULL values for the missing data. This is useful when you want to ensure that no data is lost from either table, even if there are no matching rows between them.

Example

Consider the following SQL query:

SELECT Employee.Name, Department.Department_Name
FROM Employee
FULL JOIN Department ON Employee.Department_ID = Department.Department_ID;

In this example, the query retrieves all employee names along with their department names. If an employee is not assigned to a department, NULL will appear for the department name. Similarly, if a department has no employees, NULL will be shown for the employee name.

FULL JOIN IN SQL 
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.
output of full join in sql

E. Natural join

Understanding Natural Join and Cross Join in SQL

In SQL, different types of joins allow us to combine data from multiple tables. Two important join operations are the Natural Join and Cross Join, each serving a distinct purpose. Let’s explore these two types of joins in detail.

1. What is a Natural Join?

A Natural Join in SQL is an operation that combines two tables based on shared column names and data types. The result will only include rows where the values in the matching columns are equal in both tables.

This type of join automatically identifies columns with the same name and data type in both tables and combines the matching rows based on these columns.

Example: Suppose we have two tables, Employees and Departments, with a common column Department_ID. A natural join will combine the rows where the Department_ID matches in both tables.

2. What is a Cross Join?

A Cross Join in SQL is a type of join that returns the Cartesian product of two tables. This means every row in the first table is combined with every row in the second table, resulting in a larger result set.

Unlike other types of joins, a Cross Join does not require any matching columns. It simply multiplies the number of rows in both tables.

Example: If the first table contains 3 rows and the second contains 2 rows, a Cross Join will return 6 rows (3 × 2 = 6).

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;
Categories of SQL command to know for Data Analysis

Call to Action: Take Your SQL Skills to the Next Level

Explore Additional SQL Tutorials and Real-World Practice

Now that you’ve mastered the LIKE operator and wildcards, it’s time to expand your knowledge and practice with more advanced SQL topics. The best way to improve your skills is through hands-on experience. Explore the following tutorials and practice on real-world datasets to take your SQL expertise to the next level:

These resources will guide you through complex SQL concepts and offer practical tips for real-world applications. Keep learning and refining your skills, and don’t forget to practice on real datasets to solidify your knowledge.