SQL JOINS: Inner, Left, Right, and Full Join

Table of Contents:

• What is JOIN in SQL?
• Why Use JOINS in SQL?
• Types of JOINS in SQL
  • INNER JOIN in SQL
  • LEFT JOIN in SQL
  • RIGHT JOIN in SQL
  • FULL OUTER JOIN in SQL
• Using Multiple JOINS in a Single Query
• Additional JOIN Types: SELF JOIN, CROSS JOIN, and USING
• SQL JOINS vs Subqueries: Which Performs Better?
• Common SQL JOIN Errors & How to Fix Them
• Performance Comparison of JOINS in SQL
• Real-World SQL JOIN Examples
• Best Practices for Writing Efficient SQL JOINS
• Conclusion

Before getting started with the INNER JOIN in SQL, let us create some tables for better understanding.

Let’s create a Student table.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    email VARCHAR(100)
);
INSERT INTO Students (student_id, student_name, email) VALUES
(1, 'John', '[email protected]'),
(2, 'Smith', '[email protected]'),
(3, 'Johnson', '[email protected]'),
(4, 'Wilson', '[email protected]');
Select * from Students;

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100)
);

INSERT INTO Courses (course_id, course_name, instructor) VALUES
(101, 'SQL for Beginners', 'Ramesh'),
(102, 'Advanced MySQL', 'Johnson'),
(103, 'Data Science', 'David'),
(104, 'Big Data', 'Antony');
Select * from Courses;

This is how the Courses table looks once it is created and inserted with the values.

Finally, let’s create an enrollment table

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
INSERT INTO Enrollments (enrollment_id, student_id, course_id, enrollment_date) VALUES
(1, 1, 101, '2024-01-15'),
(2, 1, 102, '2024-02-10'),
(3, 2, 103, '2024-02-20'),
(4, 3, 101, '2024-03-05'),
(5, 3, 104, '2024-03-10'),
(6, 4, 102, '2024-03-15');
Select * from Enrollments;

This is how the Enrollment table looks once it is created and inserted with the values.

What is JOIN in SQL?

A JOIN in SQL is used to combine two or more tables based on conditions or matching records between them. 

SQL JOIN syntax:

SELECT column_names  
FROM table1  
JOIN table2  
ON table1.common_column = table2.common_column;

Why Use JOINS in SQL?

• To maintain a consistent and accurate data retrieval: The use of primary and foreign keys provides a relationship between one table and another, while JOINS allow us to accurately and consistently retrieve our data between tables.
• Optimizing performance of queries: With a well-optimized JOIN (as well as indexes), it is faster to execute a JOIN than it would be as a programmatic process, fetching data and constructing a dataset.
• Retrieve Related Data: Data is stored in separate tables in relational databases to make the storage efficient and organized. 

Types of JOINS in SQL

The SQL JOIN types are: INNER, LEFT, RIGHT, and FULL OUTER JOIN.

INNER JOIN in SQL

INNER JOIN is a type of SQL join that retrieves records from two or more tables that have a matching value in the specified columns. It returns only those records where the joined records meet the specified join condition.

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

 SQL JOIN example:

-- Using an INNER JOIN between the Student and Enrollment Tables
SELECT s.student_id, s.student_name, e.course_id
FROM Students s
INNER JOIN Enrollments e ON s.student_id = e.student_id;

Output:

Explanation: Here, the INNER JOIN in SQL matches student_id from the student’s table with student_id from the enrollment table. If a student is not enrolled in any of the courses, then they will not appear in the results.

LEFT JOIN in SQL

The LEFT JOIN in SQL retrieves all records from the left table and the matching records from the right table. If no match is found, NULL values are returned from the right table.

Syntax:

SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example:

SELECT 
    Students.student_id,
    Students.student_name,
    Courses.course_name
FROM Students
LEFT JOIN Enrollments ON Students.student_id = Enrollments.student_id
LEFT JOIN Courses ON Enrollments.course_id = Courses.course_id;

Output:

Explanation: Here, the LEFT JOIN in SQL returns all students, even those who are not enrolled in any courses, are returned by this query. Since every student is enrolled in this instance, there are no NULLs displayed. The course_name would display NULL if there were no enrolled students.

RIGHT JOIN in SQL

The RIGHT JOIN in SQL retrieves all the data from the right table and the matching data from the left table. If no match is found, NULL values are returned from the left table.

Syntax:

SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column

Example:

SELECT 
    Courses.course_id,
    Courses.course_name,
    Students.student_name
FROM Enrollments
RIGHT JOIN Courses ON Enrollments.course_id = Courses.course_id
LEFT JOIN Students ON Enrollments.student_id = Students.student_id;

Output:

Explanation: Here, the RIGHT JOIN in SQL, even if none of the students are enrolled, will show all courses. Courses with no enrollment will return NULL.

FULL OUTER JOIN in SQL

The FULL OUTER JOIN in SQL retrieves all the data from both tables, including the null values.

Syntax:

SELECT column_names
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Example:

-- LEFT JOIN: All students, even if not enrolled
SELECT 
    Students.student_name,
    Courses.course_name
FROM Students
LEFT JOIN Enrollments ON Students.student_id = Enrollments.student_id
LEFT JOIN Courses ON Enrollments.course_id = Courses.course_id

UNION

-- RIGHT JOIN: All courses, even if no student enrolled
SELECT 
    Students.student_name,
    Courses.course_name
FROM Courses
LEFT JOIN Enrollments ON Courses.course_id = Enrollments.course_id
LEFT JOIN Students ON Enrollments.student_id = Students.student_id;

Output:

Explanation: Even if some students are not enrolled, the first portion of the query retrieves all students and their enrolled courses. All courses, including those with no enrolled students, are retrieved in the second section of the query. Both results are combined using UNION to cover unmatched records from both sides, which is similar to a FULL OUTER JOIN in SQL.

NOTE: Since the FULL OUTER JOIN in SQL is not supported in MySQL, the same output can be obtained from UNION.

Using Multiple JOINS in a Single Query

Multiple Joins in SQL in a single query allow us to combine data from multiple related tables.

Syntax:

SELECT columns  
FROM table1  
JOIN table2 ON condition  
JOIN table3 ON condition;

Example:

SELECT 
    Students.student_name,
    Courses.course_name,
    Enrollments.enrollment_date
FROM Students
JOIN Enrollments ON Students.student_id = Enrollments.student_id
JOIN Courses ON Enrollments.course_id = Courses.course_id;

Output:

Explanation: Here, by using student_id, this query joins students to enrollments. Then, it uses course_id to join enrollments to courses. All three tables’ details are returned in a single result set.

Additional JOIN Types: SELF JOIN, CROSS JOIN, and USING

Self Join in SQL

A self Join is a regular SQL JOIN that joins a table to itself. It’s useful when you want to compare rows within the same table.

Syntax:

SELECT A.column_name, B.column_name
FROM table_name A
JOIN table_name B ON A.common_column = B.common_column;

This is one of the SQL JOIN types, and is important in understanding multiple joins in SQL and avoiding common mistakes in SQL joins, like confusing aliases or misapplying conditions.

Cross Join in SQL

A CROSS JOIN returns the Cartesian product of two tables, each row of the first table joins with every row of the second table

Syntax: 

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

USING Clause in SQL

The USING clause simplifies JOIN syntax when both tables have a column with the same name. It’s an alternative to ON.

Syntax: 

SELECT column_name(s)
FROM table1
JOIN table2 USING (common_column);

USING makes SQL JOIN syntax simpler, especially when working with INNER JOIN in SQL, LEFT JOIN in SQL, or RIGHT JOIN in SQL.

SQL JOINS vs Subqueries: Which Performs Better?

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT
);

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);
INSERT INTO Employees VALUES (1, 'Alice', 101), (2, 'Bob', 102), (3, 'Charlie', 101);
INSERT INTO Departments VALUES (101, 'HR'), (102, 'IT'), (103, 'Finance');
SELECT e.employee_name, d.department_name  
FROM Employees e  
INNER JOIN Departments d ON e.department_id = d.department_id;

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
INSERT INTO Customers VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Sam Wilson');
INSERT INTO Orders VALUES (101, 1, '2024-03-01', 250.50),  (102, 2, '2024-03-05', 100.00),  
(103, 1, '2024-03-10', 300.00);
SELECT c.customer_name, o.order_id, o.order_date, o.amount  
FROM Customers c  
INNER JOIN Orders o ON c.customer_id = o.customer_id;

Output:

Explanation: Here, only customers who have placed an order are retrieved by the INNER JOIN. Since Sam Wilson hasn’t placed any orders, he isn’t included in the result set.

Best Practices for Writing Efficient SQL JOINS

• Use Indexing properly: To improve the performance, create an index on the columns.
• Choose the JOIN correctly: Based on the needs, choose the appropriate JOIN. For example, if you need to match the records, then use an INNER JOIN in SQL.
• Improve Performance: To improve the performance, avoid using SELECT * as it increases the data load and may impact performance.
• Filter Data: Before performing a JOIN, use the WHERE Clause, which reduces unnecessary processing.
• Ensure Readability: For better readability, prefer using an alias name like t1,t2, etc., to make the complex queries readable.

Conclusion

A JOIN in SQL is used to combine two or more tables based on conditions or matching records between them. As part of best practices, it is essential to avoid common mistakes in SQL joins, such as inefficient use of JOINS, and optimization for performance using filtering and indexing can reduce JOIN query time. In this blog, you have gained knowledge on various JOINs in detail.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL joins interview questions, prepared by industry experts.