SQL Join Example

SQL JOIN

The user can implement an SQL Join to merge data from two or more tables. In a relational database management system, all the data is related to one another. The data may be stored in different tables but somehow related. The different fields in the tables are composed of entities with relations. The data can even be separated into several tables within a database.

If there is a scenario where the user requires to collect data from several tables simultaneously, then the user can implement a simple Join clause to perform data retrieval. The join clause will merge the fields from different tables that will be associated based on the common data values in each table.

A join clause can be implemented with respect to join prediction. The predicate is mentioned in a WHERE clause. The predicate can be regarded as a condition the tables must meet to merge the data values.

For Example, when performing the equality test between the values in similar columns of different tables. These values may be present in fields with different names. It will also be regarded as a join predicate. The user can implement different operators in a join-predicate. It includes BETWEEN, LIKE, NOT, and comparison operators such as <, >, <>, <=, >=, !=.

SQL provides support for different types of join. The types of Join in SQL can be divided based on how data across the tables are merged. The types of joins are as follows:

Inner Join: An Inner Join returns the intersection of data values in the field of two or more tables. It begins with comparing the data values in the fields of the first table to the data values present in the second table. It then pairs the row and checks if the two rows meet the criteria specified in the join-predicate. It merges the records. It is also known as default join.

Outer Join: It is the opposite of the inner join. It returns all the records even if it has records in the table with no counterpart present in the other table. The outer join can further be classified into three subcategories. These categories are as follows

Full Join
Left Join
Right Join

Syntax of Join In SQL

The syntax to implement the join clause in two tables is as follows:

SELECT c_name
FROM tbl1
JOIN tbl2;

For Example,

Consider a scenario where the user has two tables in a local database. The database contains records of students and courses provided in the table. Different students are assigned different courses. The first table is the Students table, which contains the personal details of all the students in the college. It has fields for S_ID, S_Name, S_Age, S_City.

S_ID	S_Name	S_Age	S_City
01	Aman	18	Delhi
02	Harsh	22	Ghaziabad
03	Raghav	21	Noida
04	Abhay	22	Jaipur
05	Manav	19	Gurugram
06	Rita	18	Delhi

Implement the below SQL query to create the above table in the database.

CREATE TABLE Students (
    S_ID INT,
    S_Name VARCHAR(50),
    S_Age INT,
    S_City VARCHAR(50)
);
INSERT INTO Students (S_ID, S_Name, S_Age, S_City)
VALUES
    (01, 'Aman', 18, 'Delhi'),
    (02, 'Harsh', 22, 'Ghaziabad'),
    (03, 'Raghav', 21, 'Noida'),
    (04, 'Abhay', 22, 'Jaipur'),
    (05, 'Manav', 19, 'Gurugram'),
    (06, 'Rita', 18, 'Delhi');

To ensure that the above table is successfully inserted into the database, view the table by implementing the below query.

The second table contains related to the course. The fields in the Course table are C_ID, C_Name, SE_ID, and CT_Name.

C_ID	C_Name	SE_ID	CT_Name
110	Mathematics	02	Raman
112	Computer	01	Utkarsh
115	English	03	Vaibhav
121	Psychology	05	Rita

Implement the below SQL commands to create the Courses table.

CREATE TABLE Courses (
    C_ID INT,
    C_Name VARCHAR(50),
    SE_ID INT,
    CT_Name VARCHAR(50)
);
INSERT INTO Courses (C_ID, C_Name, SE_ID, CT_Name)
VALUES
    (110, 'Mathematics', 02, 'Raman'),
    (112, 'Computer', 01, 'Utkarsh'),
    (115, 'English', 03, 'Vaibhav'),
(121, 'Psychology', 05, 'Rita');

Implement the below SQL query to view the Courses table.

To merge the Student and Course table data, we will implement the essential Join operation on the two tables using the Join keyword and SELECT clause. The query to join these tables is as follows:

SELECT *
FROM Students
JOIN Courses ON Students.S_ID = Courses.SE_ID;

You will get the below result after implementing the above SQL query.

Output:

The above results are similar to those the user can retrieve by retrieving all the fields from the Students and Courses table. The JOIN keyword has merged the records from both tables into a singular tabular representation.

The user can use another method to implement the same operation by implementing the SELECT query to retrieve the same information.

SELECT Students.*, Courses.*
FROM Students, Courses
WHERE Students.S_ID = Courses.SE_ID;

Output:

Types Of Joins and its Example

The user can implement two significant types of join. The two types are:

Inner Join
Outer Join

Example of Inner Join

Inner Join

A common join type enables users to merge two tables based on a standard column. It means that all the columns in the first table are checked with all the columns in the second. It merges all the columns that meet the conditions.

Let's consider a Customers table that contains the CustomerID, CustomerName, and City.

CustomerID	CustomerName	City
1	Aman Singh	Noida
2	Harsh Gupta	Ghaziabad
3	Vaibhav Nagpal	Jaipur
4	Harshit Sharma	Delhi
5	Rita Verma	Noida

Now create the second table, the Orders table, that will store the OrderID, CustomerID, Product, and the ordered product quantity. The table is as follows:

OrderID	CustomerID	Product	Quantity
1	1	Phone	2
2	2	Laptop	1
3	1	Tablet	3
4	4	Headphones	1
5	3	SmartWatch	2

Implement the below query to create the above tables and implement the Inner Join.

-- Create the "Customers" table
CREATE TABLE Customers (
    CustomerID INT,
    CustomerName VARCHAR(50),
    City VARCHAR(50)
);

-- Insert data into the "Customers" table
INSERT INTO Customers (CustomerID, CustomerName, City)
VALUES
    (1, 'Aman Singh', 'Noida'),
    (2, 'Harsh Gupta', 'Ghaziabad'),
    (3, 'Vaibhav Nagpal', 'Jaipur'),
    (4, 'Harshit Sharma', 'Delhi'),
    (5, 'Rita Verma', 'Noida');

-- Create the "Orders" table
CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    Product VARCHAR(50),
    Quantity INT
);

-- Insert data into the "Orders" table
INSERT INTO Orders (OrderID, CustomerID, Product, Quantity)
VALUES
    (1, 1, 'Phone', 2),
    (2, 2, 'Laptop', 1),
    (3, 1, 'Tablet', 3),
    (4, 4, 'Headphones', 1),
    (5, 3, 'Smartwatch', 2);

-- Perform the inner join on the "Customers" and "Orders" tables
SELECT Customers.CustomerName, Orders.OrderID, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

Example of Outer Join

Outer Join

Outer Joins enable the user to have all the records from either one or both tables in the result. There are three types of Outer Join in SQL. The three types include Left, Right, and Full Outer Join.

We will use the above table to implement the Outer Join. The query to implement the outer join is as follows:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

It will give the following result:

Example of Self Join

Self Join

As the name suggests, it enables the user to merge the table. Consider a situation where you have the employee record and their manager id. Since the manager is an employee, their records are also in the same table.

EmployeeID	FirstName	LastName	ManagerID
1	Aman	Verma	NULL
2	Harsh	Singh	1
3	Saksham	Yadav	1
4	Varun	Singh	2
5	Guarav	Sharma	3
6	Nandini	Verma	3

-- Create the "Employees" table
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    ManagerID INT
);

-- Insert data into the "Employees" table
INSERT INTO Employees (EmployeeID, FirstName, LastName, ManagerID)
VALUES
    (1, 'Aman', 'Verma', NULL),
    (2, 'Harsh', 'Singh', 1),
    (3, 'Saksham', 'Yadav', 1),
    (4, 'Varun', 'Singh', 2),
    (5, 'Guarav', 'Sharma', 3),
    (6, 'Nandini', 'Verma', 3);

-- Perform the self-join on the "Employees" table
SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Output: